SQL Server How to choose returned column name in a SELECT FOR XML query?

gijlo24d 于 2023-11-16 发布在其他

关注(0)|答案(6)|浏览(95)

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?

sql-server

来源：https://stackoverflow.com/questions/304940/how-to-choose-returned-column-name-in-a-select-for-xml-query

6条答案

按热度按时间

f45qwnt81#

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

赞(0）回复(0）举报 2023-11-16

vnjpjtjt2#

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.

赞(0）回复(0）举报 2023-11-16

azpvetkf3#

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

赞(0）回复(0）举报 2023-11-16

w8biq8rn4#

For EXPLICIT xml generation - with unions you need to wrap results one more time (As a bonus result as XML):

SELECT 
    CAST(  
        (
            SELECT 
                * 
            FROM (
                SELECT 
                    1 AS Tag
                    ,NULL AS Parent
                    ...
                UNION ALL
                SELECT ...
                FOR XML EXPLICIT
            )
        ) as XML) as [MyName]

赞(0）回复(0）举报 2023-11-16

ubbxdtey5#

DECLARE @XmlData XML;
SET @XmlData =(SELECT * FROM [dbo].[Users] ORDER by UserName FOR XML path(''))
SELECT @XmlData AS Result

赞(0）回复(0）举报 2023-11-16

cunj1qz16#

DECLARE @XmlData XML;

SET @XmlData = (
        SELECT *
        FROM [dbo].[TABLE1]
        FOR XML PATH('ChildNodeDetailsResponse')
            ,ROOT('ParentNode')
        )

SELECT @XmlData AS Result

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server How to choose returned column name in a SELECT FOR XML query?

6条答案

相关问题

热门标签

最新问答