For XML Path - SQL Server [duplicate]

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
– Mittal Patel, Commented Mar 25, 2015 at 12:15

Giorgos Betsos · Accepted Answer · 2015-03-25 11:17:54Z

1

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.

answered Mar 25, 2015 at 11:17

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mittal Patel Over a year ago

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?

Mikael Eriksson · Accepted Answer · 2015-03-25 12:30:22Z

0

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>

edited Mar 25, 2015 at 12:30

answered Mar 25, 2015 at 12:10

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Collectives™ on Stack Overflow

For XML Path - SQL Server [duplicate]

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related