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?

Answers


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


Need Your Help

Does every belongs_to has_many association need nested routes?

ruby-on-rails ruby-on-rails-3 routes associations

This is a fairly basic question but I haven't been able to find concrete answers online. Every has_many belongs_to does not need nested routes correct? Should you only use nested routes when you're

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.