Create XML in T-SQL with one table

Question

I'm trying to do a xml file in T-SQL Here is my Table:

|Name| Rank|
|Rob | 1   |
|Matt| 1   |
|Leo | 2   |
|jin | 2   |
|jo  | 3   |

So far my code is:

select name, rank from Info order by rank for xml raw ('Data'), ROOT, ELEMENTS

The result I got is:

<root>
<data>
  <rank>1<rank>
  <name>Rob</name>
</data>
<data>
  <rank>1<rank>
  <name>Matt</name>
</data>
...

Put I would like to have this result:

<data>
  <rank id=1>
    <name>Rob</name>
    <name>Matt</name>
  </rank>
  <rank id=2>
    <name>Leo</name>
    <name>jin</name>
  </rank>
  <rank id = 3>
    <name>jo</name>
  </rank>
</data>

Do you know how I can have this? Thanks

Answer 1

Group by Rank and query the Name values for each Rank in a correlated subquery.

select I1.rank as 'rank/@id',
       (
       select I2.name
       from Info as I2
       where I1.rank = I2.rank
       for xml path(''), type
       ) as rank
from Info as I1
group by I1.rank
for xml path('data')