SQL Server FOR XML Path make repeating nodes

by

Add a column with NULL as value to generate a separate item node for each column.

SELECT 
  t.col1 as 'item'
 ,NULL
 ,t.col2 as 'item'
 ,NULL
 ,t.col3 as 'item' 
FROM dbo.tbl as t 
FOR XML PATH('parent'), TYPE;

Result:

<parent>
  <item>1</item>
  <item>2</item>
  <item>3</item>
</parent>

SQL Fiddle

Why does this work?

Columns without a name are inserted as text nodes. In this case the NULL value is inserted as a text node between the item nodes.

If you add actual values instead of NULL you will see what is happening.

SELECT 
  t.col1 as 'item'
 ,'1'
 ,t.col2 as 'item'
 ,'2'
 ,t.col3 as 'item' 
FROM dbo.tbl as t 
FOR XML PATH('parent'), TYPE;

Result:

<parent>
  <item>1</item>1<item>2</item>2<item>3</item></parent>

Another way to specify a column without a name is to use the wildcard character * as a column alias.

Columns with a Name Specified as a Wildcard Character

It is not necessary to use the wildcard in this case because the columns with NULL values don’t have a column name but it is useful when you want values from actual columns but you don’t want the column name to be a node name.

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to get a particular preceding value of a column in SQL [closed]
  3. SQL where clause odd behavior [closed]
  4. How do I obtain a Query Execution Plan in SQL Server?
  5. How do I escape a single quote in SQL Server?
  6. Simple way to transpose columns and rows in SQL?
  7. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  8. Only inserting a row if it’s not already there
  9. Unpivot with column name
  10. Selecting data from two different servers in SQL Server
  11. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  12. SQL JOIN vs IN performance?
  13. Most efficient way in SQL Server to get date from date+time?
  14. Convert varchar into datetime in SQL Server
  15. TSQL Pivot without aggregate function
  16. How to generate a range of dates in SQL Server
  17. SQL RANK() versus ROW_NUMBER()
  18. Generate SQL Create Scripts for existing tables with Query
  19. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  20. Check if a row exists, otherwise insert
  21. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  22. How do I perform a GROUP BY on an aliased column in SQL Server?
  23. CTE error: “Types don’t match between the anchor and the recursive part”
  24. How do I remove extended ASCII characters from a string in T-SQL?
  25. Getting Dates between a range of dates
  26. The most elegant way to generate permutations in SQL server
  27. SQL – How do I get only the numbers after the decimal?
  28. How to retrieve records for last 30 minutes in MS SQL?
  29. Group consecutive rows of same value using time spans
  30. Why do SQL Server Scalar-valued functions get slower?

Leave a Comment