SQL Table result in a xml output

Question

I have a table

Key     code 
1       100
1       200
1       300
1       400
2       100
2       200
2       300

I am looking for my result in one row with key and other row XML_data

Key      XML_Data(XML column)
1        <sub><key>1...
2        <sub><key>2...

XML_Data example :

<sub>
  <key> 1 </Key>
    <list>
      <code> 100 </code>
      <code> 200 </code>
      <code> 300 </code>
      <code> 400 </code>
   </list>
</sub>

Thanks

The query you've tried so far is? – Pred Mar 23 '17 at 18:33 — Pred, Mar 23 '17 at 18:33

score 1 · Answer 1 · edited May 23 '17 at 11:54

1

I think that this SQL Server: Two-level GROUP BY with XML output it's closer.

Posted here because i don't have reputation for comment.

edited May 23 '17 at 11:54

Community

1
1

answered Mar 23 '17 at 20:32

Nanyin

11
1
2
3

score 0 · Answer 2 · edited May 23 '17 at 12:00

You've not specified much, as to where your XML is etc. but in general following query should help in your case.

INSERT INTO dbo.YourTable
  (key, xml_data) 
VALUES 
  (KEY, CONVERT(XML, N'YOUR_XML', 2));

You can go even further with that, by declaring XML as variable, extracing your KEY from XML, and to 2nd column adding XML based on whole variable.

DECLARE @input XML = '<sub>
  <key> 1 </key>
    <list>
      <code> 100 </code>
      <code> 200 </code>
      <code> 300 </code>
      <code> 400 </code>
   </list>
</sub>'

INSERT INTO dbo.YourTable(key, xml_data)
   SELECT
      key = COALESCE(XCol.value('key[1]', 'int'),0),
      xml_data = CONVERT(XML, N''''+@input+'''', 2)
   FROM  @input.nodes('/sub') AS XTbl(XCol)

@ATC, thank you for your comment. If really what is needed is to get table into XML format please try following - FOR XML PATH is what you're looking for

SELECT 
   key,
   code as "list/code"
FROM dbo.YourTable
FOR XML PATH('sub')

I'm not able to test it right now unfortunately. Here you can see similar question: How to convert records in a table to xml format using T-SQL?

Your updated query will not lead to the structure the OP states in his question... — Shnugo, Mar 24 '17 at 07:42
@Shnugo thanks for comment, unfortunately I'm not able to do real testing due to my pc / environment limitation. It was more like a concept to take further. Seen your answer, seems like it will solve the problem well, thanks! — morb1d, Mar 24 '17 at 11:02

score 0 · Accepted Answer · answered Mar 24 '17 at 07:41

Your question is quite fuzzy, but my magic crystall ball tells me, that you are looking for this:

DECLARE @tbl TABLE([Key] INT, code INT);
INSERT INTO @tbl VALUES 
 (1,100)
,(1,200)
,(1,300)
,(1,400)
,(2,100)
,(2,200)
,(2,300);

--The query will first find a distinct list of keys and then use nested FOR XML-selects to gather your data into the structure wanted:

WITH DistinctKeys AS
(SELECT [Key] FROM @tbl GROUP BY [Key])
SELECT dk.[Key]
      ,(
        SELECT dk.[Key]
              ,(
                SELECT t.code
                FROM @tbl AS t
                WHERE t.[Key]=dk.[Key]
                FOR XML PATH(''),ROOT('list'),TYPE
               )
        FOR XML PATH('sub'),TYPE    
       ) AS XML_Data
FROm DistinctKeys AS dk

The result

Key XML_Data
1   <sub><Key>1</Key><list><code>100</code><code>200</code><code>300</code><code>400</code></list></sub>
2   <sub><Key>2</Key><list><code>100</code><code>200</code><code>300</code></list></sub>

SQL Table result in a xml output

3 Answers3