英文:
How to use TSQL FOR XML to generate one element per row
问题
我想生成以下XML：
<students>
<name>Alice</name>
<name>Bob</name>
<name>Charles</name>
</students>
不要生成以下XML：
<students>
<row><name>Alice</name></row>
<row><name>Bob</name></row>
<row><name>Charles</name></row>
</students>
是否可以使用TSL的FOR XML子句来实现这个目标？
请注意，对生成的XML进行文本替换不算在内。我已经知道string_agg()了。
英文:
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
得分: 2
有两种方法可以实现这个，取决于你希望其他列如何显示。
要么不使用 PATH，这将保留其他列使用它们自己的节点名称，但没有父节点。
SELECT
s.name
FROM student s
FOR XML PATH(''), ROOT('students'), TYPE;
要么使用一个无名称的列，在其中其他无名称的列将被合并成一个文本值，位于 <name> 内。
SELECT
s.name + ''
FROM student s
FOR XML PATH('name'), ROOT('students'), TYPE;
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论