Converting SQL query result to XML

Question

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>

Please don't supply sample data as an image; we can't use it. Take the time to provide it as tabular formatted `text` or DDL and DML statements. As we can't run your code, due to a lack of consumable sample data, why didn't what you have work? — Thom A, Nov 29 '19 at 10:02
Yes - and? You have the data and you have a query - what's the question really?? — marc_s, Nov 29 '19 at 10:04
Maybe you can write a SQLCLR function to convert query result to XML — Nitin Sawant, Nov 29 '19 at 10:06
My query have generate 4 entry. But I need single entry id , name, year are same but prov and provid is different. So I want this kind of format. — Antony, Nov 29 '19 at 10:08
@NitinSawant the OP is already using a query that returns XML — Panagiotis Kanavos, Nov 29 '19 at 10:09
@Antony update your question with the actual result and *text* instead of images. Don't force people to create tables, insert values just to see what the problem is — Panagiotis Kanavos, Nov 29 '19 at 10:12
@Antony after your update would this help https://stackoverflow.com/questions/18132039/sql-server-two-level-group-by-with-xml-output — Gnyasha, Nov 29 '19 at 10:34

Vignesh Kumar A · Accepted Answer · 2019-11-29T10:56:17.873

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>

Ajeet Verma · Answer 2 · 2019-11-29T10:13:33.093

0

Please try below query,

SELECT id
    ,year
    ,Name
    ,prov
    ,provid
FROM TABLE
FOR XML RAW ,ELEMENTS

I have tried it for different table for same type of result. Please check snap:

edited Nov 29 '19 at 10:13

answered Nov 29 '19 at 10:09

Ajeet Verma

1,021
1
7
25

1

This isn't goinjg to generate a value like `provID="1995-11_CL260"` – Thom A Nov 29 '19 at 10:11
The given query generated 4 entry. But I need single entry id , name, year are same but prov and provid is different. – Antony Nov 29 '19 at 10:11
The only thing those images show is that the result doesn't match what the OP asked for. The OP's query specifies an `entry` element inside a `legref` root, something impossible to generate with `XML RAW,ELEMENTS` – Panagiotis Kanavos Nov 29 '19 at 10:14

Converting SQL query result to XML

2 Answers2