SQL Server How to use TSQL FOR XML to generate one element per row

e4eetjau 于 2023-06-28 发布在其他

关注(0)|答案(1)|浏览(91)

Given the following query:

select name from student

I want to generate the following XML:

<students>
    <name>Alice</name>
    <name>Bob</name>
    <name>Charles</name>
</students>

I do not want to generate the following XML. The code consuming the generated XML won't accept anything but the format shown above.

<students>
    <row><name>Alice</name></row>
    <row><name>Bob</name></row>
    <row><name>Charles</name></row>
</students>

Can this be done with TSL's FOR XML clause?

Text replacement on the resulting XML doesn't count. I can do that already. I already know string_agg() , too.

1条答案

There are two methods to do this, depending on how you would want other columns to show up.

Either use no PATH , which would keep other columns using their own node names, but no parent node.

SELECT
  s.name
FROM student s
FOR XML PATH(''), ROOT('students'), TYPE;

Or an unnamed column, where other unnamed colums would be squashed together into a single text value inside <name>

SELECT
  s.name + ''
FROM student s
FOR XML PATH('name'), ROOT('students'), TYPE;