Converting SQL query result to XML [closed]

Input data

id  year    Name    provid  prov
1   1995    MAC 1995-11_CL236   reg 236
1   1995    MAC 1995-11_CL230   reg 230 (1)
1   1995    MAC 1995-11_CL229J  reg 229J
1   1995    MAC 1995-11_CL260   reg 260

My query looks like this

select  
    id, year, Name, prov, provid 
from 
    Table 
for xml path ('entry'), root('legref'), elements

The above Query generating different entry for each row. But I need group by id year, name and provide single entry with different prov and provid.

<legref>
  <entry>
    <id>1</id>
    <year>1995</year>
    <Name>MAC</Name>
    <prov>reg 229J</prov>
    <provid>NSW_REG_1995-11_CL229J</provid>
  </entry>
  <entry>
    <id>1</id>
    <year>1995</year>
    <Name>MAC</Name>
    <prov>reg 230 (1)</prov>
    <provid>NSW_REG_1995-11_CL230</provid>
  </entry>
  <entry>
    <id>1</id>
    <year>1995</year>
    <Name>MAC</Name>
    <prov>reg 236</prov>
    <provid>NSW_REG_1995-11_CL236</provid>
  </entry>
  <entry>
    <id>1</id>
    <year>1995</year>
    <Name>MAC</Name>
    <prov>reg 260</prov>
    <provid>NSW_REG_1995-11_CL260</provid>
  </entry>
</legref>

Output Data:

How do I convert SQL query result to XML?

Expected result set:

<legref>
     <entry>
            <id>1<id>
            <year>1995</year>
            <Name>MAC</Name>
            <prov provID="1995-11_CL230">reg 230 (1)</prov>
            <prov provID="1995-11_CL236">reg 236</prov>
            <prov provID="1995-11_CL260">reg 260</prov>
            <prov provID="1995-11_CL229J">reg 229J</prov>
        </entry>
</legref>

Answer

Try This

FIDDLE DEMO

SELECT ID, Year, Name, 
       (
            SELECT ProvID AS 'Prov/@ProvID',Prov
            FROM tbl t
            WHERE ID = t.ID AND Name = t.Name
            FOR XML PATH(''),TYPE
        )
FROM tbl 
GROUP BY ID, Year, Name
FOR XML PATH ('Entry'),ROOT('legref')

Output

<legref>
  <Entry>
    <ID>1</ID>
    <Year>1995</Year>
    <Name>MAC</Name>
    <Prov ProvID="1995-11_CL236">Reg 236</Prov>
    <Prov ProvID="1995-11_CL230">Reg 230</Prov>
    <Prov ProvID="1995-11_CL229J">Reg 229J</Prov>
    <Prov ProvID="1995-11_CL260">Reg 260</Prov>
  </Entry>
</legref>

Advertisement

Answer