How To Choose Returned Column Name In A Select For Xml Query?

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('')

Solution 1:

That should do:

select(
SELECT col1
 FROM table1
 WHERE col2 = 'x'ORDERby 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  @pinas'@PIN',
                            @periodas'@Period',
                            @numberas'@Number',
                            @barcodeas'@Barcode'
                        FOR XML PATH('QueryParams'), TYPE
                )   as Data

                FOR XML PATH('DataExchangeModule')              
            )

select @requestResultXMLas GetBillRequest

Solution 4:

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

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

Solution 5:

DECLARE@XmlData XML;
SET@XmlData=(SELECT*FROM [dbo].[Users] ORDERby UserName FOR XML path(''))
SELECT@XmlDataASResult