How to select returned column name in SELECT FOR XML query?

Question

MS SQL has a convenient workaround to combine a column value from several rows into a single value:

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

and returns a good set of records:

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

only the column name in the returned recordset is pretty nasty!

It looks like the column name contains random elements (or GUIDs) and therefore I am reluctant to use it in my application (different instances or different servers may have a different GUID). Unfortunately, I cannot use * to select a value, and due to limitations in an existing application, I cannot iterate over returned columns, ...

Is there a way to force the column name in the returned recordset to something more reasonable?

Answer 1

This should do:

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

Not really, but should give the result you need

Answer 2

Try it...

 select ( select '@greeting' = 'hello', '@where' = 'there', '@who' = 'world' for xml path ('salutation'), type ) as 'MyName' 

Note. If you omit the "type" after "for xml", you will get (I think) a string.

Answer 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 

Answer 4

For EXPLICIT xml generation - with the help of unions you need to wrap the results again (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] 

Answer 5

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

Answer 6

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