Menu
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?
904
To get a column name of a table we use sp_help with the name of the object or table name. sp_help will return all the column names of the object.
Step-2: Choose the column name you want to change and then double-click. Step-3: Give a name to your selected column. Step-4: Refresh the table or database to complete the renaming steps. Step-5: Implement the SELECT statement to confirm the changed column name.
You should use the query() Method if you want to get a part of your XML. If you want the value from a specific node you should use value() Method. Update: If you want to shred your XML to multiple rows you use nodes() Method.
We can verify the data in the table using the SELECT query as below. We will be using sys. columns to get the column names in a table. It is a system table and used for maintaining column information.
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
55
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
