For XML Path - SQL Server

Question

I have data in SQL Server table as shown below.

**Name      Date        AA   BB  CC**
Customer1   3-Mar-15    234  56  4567
Customer2   4-Mar-15    55   66   77

Now I want this data to be formatted as below in XML.

<ReturnsList>
    <Return Label='AA' Days = 1 value = '234'></Return>
    <Return Label='BB' Days = 2 value = '56'></Return>
    <Return Label='CC' Days = 3 value = '4567'></Return>
</ReturnsList>
<ReturnsList>
    <Return Label='AA' Days = 1 value = '55'></Return>
    <Return Label='BB' Days = 2 value = '66'></Return>
    <Return Label='CC' Days = 3 value = '77'></Return>
</ReturnsList>

I have tried different ways like :

SELECT 

 (Select AA  AS "AA", 

   BB AS "BB",

   CC AS "CC"

from table
for xml path('Return') ,type
)
FOR XML PATH ('ReturnsList');

I am not getting xml format as expected.

Can anyone please help ?

Thank you, Mittal.

I have updated above example, as it should work for all the rows. How can I achieve this with below CTE query ? — Mittal Patel, Mar 25 '15 at 12:15

score 1 · Accepted Answer · answered Mar 25 '15 at 11:17

FOR XML PATH operates on rowsets. Hence, you have to transpose a single row of your data into a set of rows. You can use UNPIVOT for this:

SELECT Name, Labels, LabelValue, 
       CASE 
          WHEN Labels = 'AA' THEN 1
          WHEN Labels = 'BB' THEN 2
          WHEN Labels = 'CC' THEN 3
       END AS [Days]
FROM 
   (SELECT Name, AA, BB, CC
    FROM #mytable) p
UNPIVOT (
   LabelValue FOR Labels IN ([AA], [BB], [CC])
)AS unpvt

Output:

    Name        Labels  LabelValue  Days
   --------------------------------------
    Customer1   AA      234         1
    Customer1   BB      56          2
    Customer1   CC      4567        3

You can now wrap the above query in a CTE and apply FOR XML PATH to get the desired result:

;WITH CTE AS (
    ... above query here ...
)
SELECT Labels AS "@Label", [Days] AS "@Days", LabelValue AS "@value" 
FROM CTE
FOR XML PATH('Return'), root('ReturnsList')

For more than one dates, customers you can adjust the above query with an appropriate use of GROUP BY clause.

Thank you for this detailed answer. Can I use this CTE query with select statement? As I want to insert this xml output to another table with other columns from #mytable. Is this possible? — Mittal Patel, Mar 25 '15 at 12:00

Mikael Eriksson · Answer 2 · 2015-03-25T12:30:22.997

select 'AA' as [Return/@Label],
       1    as [Return/@Days] ,
       T.AA as [Return/@Value],
       null,
       'BB' as [Return/@Label],
       2    as [Return/@Days] ,
       T.BB as [Return/@Value],
       null,
       'CC' as [Return/@Label],
       3    as [Return/@Days] ,
       T.CC as [Return/@Value],
       null
from table as T
for xml path('ReturnList');

Result:

<ReturnList>
  <Return Label="AA" Days="1" Value="234" />
  <Return Label="BB" Days="2" Value="56" />
  <Return Label="CC" Days="3" Value="4567" />
</ReturnList>
<ReturnList>
  <Return Label="AA" Days="1" Value="55" />
  <Return Label="BB" Days="2" Value="66" />
  <Return Label="CC" Days="3" Value="77" />
</ReturnList>

For XML Path - SQL Server

2 Answers2