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

Answer 1

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>