Miguel Mas Miguel Mas - 18 days ago 6
SQL Question

SQL FOR XML PATH: atributes not staying in the node

I have:

(SELECT TOP 1 '01' AS '@language', 'a description' as 'data()' FROM @tmpData AS FOR XML PATH('description'),ROOT('course'),TYPE),
(SELECT TOP 1 '2016-CRS' AS '@code' FROM @tmpData as t FOR XML PATH('course'),TYPE),


This shows:

<course>
<description language="01">a description</description>
</course>
<course code="2016-CRS" />


But what I really need is:

<course code="2016-CRS">
<description language="01">a description</description>
</course>


I know it is simple but I cannot achieve it, please help

Thanks!

Answer

Your example is not all clear, as you are using a data source FROM @tmpData without using it at all, but this would return the result you seem to need:

SELECT '2016-CRS' AS [@code] 
      ,'01' AS [description/@language]
      ,'a description' as [description]
FOR XML PATH('course')

The result

<course code="2016-CRS">
  <description language="01">a description</description>
</course>
Comments