Sql-server – how to output 3 column into 3 element in xml format with the same name

sql-server-2012xml

I have this code and I can't figure out how to get the expected result?

declare @table table (TAG int, PARENT int,col1 varchar(100)
                                         ,col2 varchar(100)
                                         ,col3 varchar(100))

insert into @table select 1, NULL, 'Value1'
                                 , 'Value2'
                                 , 'Value3'

select TAG,PARENT, col1 as [MyElement!1!!element],
                   col2 as [MyElement!1!!element],
                   col3 as [MyElement!1!!element] from @table for xml explicit

result :

<MyElement>Value1Value2Value3</MyElement>

expected :

<MyElement>Value1</MyElement>
<MyElement>Value2</MyElement>
<MyElement>Value3</MyElement>

Best Answer

It seems that when setting the directive (the 4th item in the !-delimited specification that is the field name alias) to "element", then the 3rd item in that specification is the element name that contains the value, whereas the 3rd item would otherwise be the attribute name that contains the value. In both cases the 1st item in the specification is the element that contains either the attribute or the sub-element. Hence, leaving it blank in [MyElement!1!!element] gave it no name for the child elements.

Instead, fill out the 3rd item so that the specification looks like:

[Parent!1!MyElement!element]

And so using the following query:

SELECT TAG,PARENT, col1 AS [Parent!1!MyElement!element],
                   col2 AS [Parent!1!MyElement!element],
                   col3 AS [Parent!1!MyElement!element]
FROM @table
FOR XML EXPLICIT

returns:

<Parent>
  <MyElement>Value1</MyElement>
  <MyElement>Value2</MyElement>
  <MyElement>Value3</MyElement>
</Parent>

Please note that you must fill out the 1st item in the specification, which means you cannot get this list without having a parent element.

Please also see the MSDN page for Example: Specifying the ELEMENT Directive.

Related Question