如何在 SELECT FOR XML 查询中选择返回的列名? [英] How to choose returned column name in a SELECT FOR XML query?

问题描述

MS SQL 有一种方便的解决方法，可以将多行中的列值连接成一个值:

MS SQL has a convenient workaround for concatenating a column value from multiple rows into one value:

SELECT col1
 FROM table1
 WHERE col2 = 'x'
 ORDER by col3
 FOR XML path('')

然后返回一个不错的记录集:

and that returns a nice recordset:

XML_F52E2B61-18A1-11d1-B105-00805F49916B                                     
---------------------------------------- 
<col1>Foo</col1><col1>Bar</col1>

只有返回的记录集中的列名比较讨厌！

only the column name in the returned recordset is rather nasty!

列名似乎包含随机元素(或 GUID)，因此我不愿意在我的应用程序中使用它(不同的实例或不同的服务器可能有另一个 GUID).不幸的是，我无法使用 * 来选择值，并且由于现有应用程序中的限制，我无法遍历返回的列，或者...

The column name seems to include random elements (or a GUID), and hence I am reluctant to use it in my application (different instances or different servers might have another GUID). Unfortunately I cannot use * to select the value, and due to the restrictions in the existing application I cannot iterate through returned columns, either...

有没有办法强制返回的记录集中的列名更合理?

Is there a way to force the column name in the returned recordset to something more sensible?

推荐答案

应该这样做:

select(
SELECT col1
 FROM table1
 WHERE col2 = 'x'
 ORDER by col3
 FOR XML path('')
) as myName

不漂亮，但应该给出你需要的结果

Not pretty but should give the result that you need

这篇关于如何在 SELECT FOR XML 查询中选择返回的列名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！