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?

Answer 1

The column names are in the XML.

The column name returned for the query is irrelevant, what do you need it for. Simply referring to the first column returned (for example rs.Fields(0) in ADO) should be enough.

Answer 2

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