SQL Server For XML Query

Question

My table structure is as follows:

   date             Id        Value
   --------------------------------
   01/01/2005    50000        5              
   01/01/2006    50000        6   
   01/01/2007    50000        7   
   01/01/2005    50001        8   
   01/01/2006    50001        9   
   01/01/2007    50001        10

I would like to output the xml using for xml in SQL Server in the following format:

<Date date = "01/01/2005"> <Id dealId=50000" value="5"/> <Id dealId=50001" value="8"/> </Date> <Date date = "01/01/2006"> <Id dealId=50000" value="6"/> <Id dealId=50001" value="9"/> </Date>

It would be of great help if someone can help me out with the query in SQL Server. I have tried couple of them myself but I am not getting the exact output.

You should show an attempt you made, this evidences that you have tried something and a better answer can be given explaining where the code is wrong which helps in peoples learnings. — T I, Nov 24 '13 at 03:06

laylarenee · Answer 1 · 2013-11-24T03:55:13.803

-- using your provided values:
DECLARE @table TABLE ([date] DATE, id INT, value INT);
INSERT @Table VALUES ('01/01/2005',50000,5);
INSERT @Table VALUES ('01/01/2006',50000,6);
INSERT @Table VALUES ('01/01/2007',50000,7);
INSERT @Table VALUES ('01/01/2005',50001,8);
INSERT @Table VALUES ('01/01/2006',50001,9);
INSERT @Table VALUES ('01/01/2007',50001,10);

-- XML Query
-- the outer query groups by date, ensuring each date only shows up once
SELECT 
    [date] AS '@date',
    (
        -- The inner query selects all Ids for each date found in the outer query
        SELECT  id as '@dealId',
                value as '@value'
        FROM @Table B
        WHERE B.date=A.date -- join on date from outer query
        FOR XML PATH ('Id'), TYPE
    )
FROM @Table A
GROUP BY date
FOR XML PATH ('Date');

-- Produces:    
<Date date="2005-01-01">
    <Id dealId="50000" value="5"/>
    <Id dealId="50001" value="8"/>
</Date>
<Date date="2006-01-01">
    <Id dealId="50000" value="6"/>
    <Id dealId="50001" value="9"/>
</Date>
<Date date="2007-01-01">
    <Id dealId="50000" value="7"/>
    <Id dealId="50001" value="10"/>
</Date>

If you feel this answer solved your problem, please mark it as 'accepted' by clicking the green check mark. — laylarenee, Jan 04 '14 at 02:47

score 0 · Answer 2 · answered Nov 24 '13 at 07:53

Usually, if I need such a simple xml transformation, I'm using for xml raw:

select
    t1.[date],
    (
        select
            t2.[Id] as dealId, t2.[Value]
        from Table1 as t2
        where t2.[date] = t1.[date]
        for xml raw('Id'), type
    )
from Table1 as t1
group by t1.[date]
for xml raw('Date')

----------------------
<Date date="2005-01-01">
    <Id dealId="50000" Value="5"/>
    <Id dealId="50001" Value="8"/>
</Date>
<Date date="2006-01-01">
    <Id dealId="50000" Value="6"/>
    <Id dealId="50001" Value="9"/>
</Date>
<Date date="2007-01-01">
    <Id dealId="50000" Value="7"/>
    <Id dealId="50001" Value="10"/>
</Date>

Try it yourself in the sql fiddle demo

Some notes:

You don't have to use @ in column names, because for xml raw is attribute-centric by default.
Subquery here is necessary, because you want nested xml and for now it's the only way to get it.
You also have to use type modifier in the subquery to get inner data as xml.

SQL Server For XML Query

2 Answers2