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?

score 13 · Accepted Answer · edited Nov 22 '16 at 09:55

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.

This works - is it documented behavior though? Would I have to worry about it breaking in a future release? — Dan Field, Mar 24 '15 at 13:39
@DanField Added a bit of explanation to the answer. Let me know if I can clarify anything. — Mikael Eriksson, Mar 24 '15 at 13:53
Awesome, thanks. This answer really gives the most clarity on functionality and the original intent of the question. Marking it as the accepted answer. — Dan Field, Mar 24 '15 at 14:05

score 4 · Answer 2 · answered Mar 16 '15 at 23:59

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

answered Mar 16 '15 at 23:59

sanmis

515
1
7
22

Great, thanks. I wasn't sure if there was a way to do it using XML PATH, but this should work. – Dan Field Mar 17 '15 at 00:35

score 3 · Answer 3 · answered Mar 17 '15 at 02:13

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

I think for my purposes it'll be better to switch to Explicit, but thanks for this. I was wondering about some way to unpivot, it just seemed like there might be a more direct/easier way. — Dan Field, Mar 17 '15 at 12:50
I appreciate your input - I've realized a slightly simpler way to do this, thanks in part to your suggestions. See the answer I posted. — Dan Field, Mar 24 '15 at 13:14

score 1 · Answer 4 · answered Mar 24 '15 at 13:13

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).

answered Mar 24 '15 at 13:13

Dan Field

20,885
5
55
71

Thanks for posting back what you ended up doing. For future readers, the only thing to caution against when building your own Xml (in any language) is to watch out for Xml reserved characters (<, >, etc) otherwise you'll get cryptic parsing errors. (The original question makes it look like `col1`, `col2`, and `col3` may be integers so it may not be an issue here, though this syntax here makes it look like they may be stored as char/varchar data, and hence *could* contain reserved characters). – lheria Mar 25 '15 at 00:36
Yes. In the actual work, these values originally came from XML so it'll be fine. – Dan Field Mar 25 '15 at 11:59

score -2 · Answer 5 · answered Mar 16 '15 at 23:19

-2

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

answered Mar 16 '15 at 23:19

sanmis

515
1
7
22

Nope, that would give me different tag names. I want the item tag to repeat – Dan Field Mar 16 '15 at 23:31

SQL Server FOR XML Path make repeating nodes

5 Answers5

Linked

Related