SQL 如何在 SELECT FOR XML 查询中选择返回的列名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/304940/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to choose returned column name in a SELECT FOR XML query?
提问by Eero
MS SQL has a convenient workaround for concatenating a column value from multiple rows into one value:
MS SQL 有一种方便的解决方法，可以将多行中的列值连接成一个值：
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...
列名似乎包含随机元素（或 GUID），因此我不愿意在我的应用程序中使用它（不同的实例或不同的服务器可能有另一个 GUID）。不幸的是，我无法使用 * 来选择值，并且由于现有应用程序中的限制，我无法遍历返回的列，或者...
Is there a way to force the column name in the returned recordset to something more sensible?
有没有办法强制返回的记录集中的列名更合理？
回答by kristof
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
不漂亮，但应该给出你需要的结果
回答by Black Light
Try this...
尝试这个...
select
(
select '@greeting' = 'hello', '@where' = 'there', '@who' = 'world'
for xml path ('salutation'), type
) as 'MyName'
Note: If you omit the "type" after the "for xml", you get (I think)a string.
注意：如果在“for xml”之后省略“type”，则会得到（我认为）一个字符串。
回答by Vadym Sichkar
stored procedure
存储过程
declare @requestResultXML xml
set @requestResultXML =
(
SELECT 'NPOIT-1.0' AS '@Interface',
(
select 'Query' as '@Type',
'GetBill' as '@Query',
'True' as '@CompressResult'
FOR XML PATH('Head'), TYPE
),
(
select @pin as '@PIN',
@period as '@Period',
@number as '@Number',
@barcode as '@Barcode'
FOR XML PATH('QueryParams'), TYPE
) as Data
FOR XML PATH('DataExchangeModule')
)
select @requestResultXML as GetBillRequest
回答by Mahadev
DECLARE @XmlData XML;
SET @XmlData =(SELECT * FROM [dbo].[Users] ORDER by UserName FOR XML path(''))
SELECT @XmlData AS Result
回答by JJ Roman
For EXPLICIT xml generation - with unions you need to wrap results one more time (As a bonus result as XML):
对于 EXPLICIT xml 生成 - 使用联合您需要再包装一次结果（作为 XML 的额外结果）：
SELECT
CAST(
(
SELECT
*
FROM (
SELECT
1 AS Tag
,NULL AS Parent
...
UNION ALL
SELECT ...
FOR XML EXPLICIT
)
) as XML) as [MyName]
回答by Mahadev
DECLARE @XmlData XML;
SET @XmlData = (
SELECT *
FROM [dbo].[TABLE1]
FOR XML PATH('ChildNodeDetailsResponse')
,ROOT('ParentNode')
)
SELECT @XmlData AS Result
