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

Question

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?

Solution 1

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

Solution 2

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.

Solution 3

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

Solution 4

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

Solution 5

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]

View more solutions

Comments

[stefano m]

due to your response, i found solution for my problem.. thanks a lot!

[Yaroslav]

Care to comment your code? What does it adds to the currently accepted answer with 12 upvotes and simple code?

[Black Light]

Actually, there is a subtle difference. You'll note that Vadym is returning a local variable of type "Xml". In the accepted answer the result is (I think) a string. I know it doesn't look like it ought to be, but it's certainly not of type "Xml".

[Black Light]

There is a second subtlety as well. When you select "for Xml", you get a row when some Xml is generated (ie. some source records match your criteria), or you get zero rows. When you (effectively) make the Xml generation a sub-query, you always get a row. You then have to determine the absence of data by means other than the row count.

[Evil Pigeon]

Yup. TYPE is required if you want to retain the xml datatype for the resulting column.

[adiga]

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value.

[Admin]

If don't want any xml node Please Refer below code

[adiga]

Edit the answer and add some additional comments as to why this would work.

[yivi]

Welcome to Stack Overflow! While this code snippet may be the solution, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion.

[snowblindzz]

This format retains the result to be of type XML. If Select(Query) as ColumnName fomat, the result comes as a sting and not XML.

[gkenny]

The type comment is exactly what I was searching for, thanks!