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

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

MS SQL有一个方便的解决方法，用于将多个行的列值连接成一个值：

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

并返回一个不错的记录集：

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

只有在返回的记录集中的列名称相当讨厌！

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

有没有办法强制返回的记录集中的列名称更加明智？

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!

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

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

That should do:

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