SQL Server FOR XML Path make repeating nodes

Question

I'd like to generate the following output using SQL Server 2012:

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

From three different columns in the same table (we'll call them col1, col2, and col3).

I'm trying to use this query:

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

But what I get is this:

<parent>
  <item>123</item>
</parent>

What am I doing wrong here?

Answer 1

A couple notes here: If you use FOR XML EXPLICIT, you can't use WITH XMLNAMESPACES (a requirement I didn't mention, so I'm still leaving the accepted answer). While Iheria's answer was also very helpful, there's another simpler possibility I've since realized:

SELECT CONVERT(XML, '<item>' + t.col1 
           + '</item><item>' + t.col2 
           + '</item><item>' + t.col3 + '</item>')
FROM tbl t
FOR XML PATH('parent'), TYPE

I think this is probably the easiest and most performant way (I haven't benchmarked it, but I can't imagine using UNPIVOT would be faster and, if anything, the multiple SELECT option likely is refactored to this by the engine anyway).

Answer 2

There are actually a few ways to solve this with the XML Path syntax.

The first is to UNPIVOT your results first, for example:

SELECT item as [text()]
FROM 
   (select col1, col2, col3 from tbl) p
UNPIVOT
   (item FOR colHeading IN (col1, col2, col3)) AS unpvt
FOR XML PATH ('item'), ROOT ('parent')

The 2nd doesn't require the unpivot, but repeats more of your query:

select (select col1 as [text()] from tbl for xml path('item'), type)
    ,  (select col2 as [text()] from tbl for xml path('item'), type)
    ,  (select col3 as [text()] from tbl for xml path('item'), type)
for xml path ('parent')

Both of these will combine multiple rows of data all under the same parent node. For example, if you have 2 rows, the first with 1,2,3 and the second 4,5,6, you'd get:

<parent>
    <item>1</item>
    <item>2</item>
    <item>3</item>
    <item>4</item>
    <item>5</item>
    <item>6</item>
</parent>

If, instead, you want each row you unpivot to have a unique parent element per row, then, assuming you have some row identifier on each row (I'll call it parentId), you can group these by that row by tweaking these approaches:

SELECT
  (
      SELECT item as [text()]
      FROM 
        (select parentId, col1, col2, col3 from tbl tt where tt.parentid =   t.parentid) p
      UNPIVOT
        (item FOR colHeading IN (col1, col2, col3)) AS unpvt
      FOR XML PATH ('item'), TYPE
  )
FROM tbl t
FOR XML PATH ('parent')

or

select (select col1 as [text()] from tbl tt where t.parentid = tt.parentid for xml path('item'), type)
    ,  (select col2 as [text()] from tbl tt where t.parentid = tt.parentid for xml path('item'), type)
    ,  (select col3 as [text()] from tbl tt where t.parentid = tt.parentid for xml path('item'), type)
from tbl t
for xml path ('parent')

Which would result in:

<parent>
    <item>1</item>
    <item>2</item>
    <item>3</item>
</parent>
<parent>
    <item>4</item>
    <item>5</item>
    <item>6</item>
</parent>

Sql Fiddle with demo

Answer 3

I think if you change the alias of the columns like this it should work. This is because the aliases are same and may be the data type of data is same as well. In case if you have different data in col1, col2 and col3, it shouldn't be showing this behaviour.

SELECT 
t.col1 as 'item'
,t.col2 as 'item1'
,t.col3 as 'item2' 
FROM tbl t 
FOR XML PATH('parent'), TYPE

Answer 4

Ok, you can't use path for that. Use explicit, instead,

SELECT 1 AS tag,NULL AS parent, t.col1 AS [Parent!1!Item!element],
               t.col2 AS [Parent!1!Item!element],
               t.col3 AS [Parent!1!Item!element]
FROM tbl t
FOR XML EXPLICIT

Answer 5

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.