sqlsql-serverxmlsql-server-2005

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

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

  • 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