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

Question

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.

Answer 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;