SQL Server FOR XML Path make repeating nodes -

i'd generate following output using sql server 2012:

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

from 3 different columns in same table (we'll call them col1, col2, , col3).

i'm trying use query:

select    t.col1 'item'  ,t.col2 'item'  ,t.col3 'item'  tbl t  xml path('parent'), type 

but this:

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

what doing wrong here?

add column null value generate separate item node each column.

select    t.col1 'item'  ,null  ,t.col2 'item'  ,null  ,t.col3 'item'  dbo.tbl t  xml path('parent'), type; 

result:

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

sql fiddle

why work?

columns without name inserted text nodes. in case null value inserted text node between item nodes.

if add actual values instead of null see happening.

select    t.col1 'item'  ,'1'  ,t.col2 'item'  ,'2'  ,t.col3 'item'  dbo.tbl t  xml path('parent'), type; 

result:

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

another way specify column without name use wildcard character * column alias.

columns name specified wildcard character

it not necessary use wildcard in case because columns null values don't have column name useful when want values actual columns don't want column name node name.