convert mutilple nodes within xml to comma seperated list in Tsql

Question

I have a table with a column containing the following xml which I want to present on the gui as a comma separated list ie. 1,2,5,6,7,8

    <RuleSetting>
  <CurrentQueue>1559</CurrentQueue>
  <CurrentQueue>1560</CurrentQueue>
  <CurrentQueue>1561</CurrentQueue>
  <CurrentQueue>1564</CurrentQueue>
</RuleSetting>

How might I do this or where can I find information on how to do it?

Answer 1

You can use XQuery for this:

DECLARE @xml XML=
N'<RuleSetting>
  <CurrentQueue>1559</CurrentQueue>
  <CurrentQueue>1560</CurrentQueue>
  <CurrentQueue>1561</CurrentQueue>
  <CurrentQueue>1564</CurrentQueue>
</RuleSetting>';

SELECT STUFF(@xml.query(N'
                   for $nd in /RuleSetting/CurrentQueue/text()
                   return
                   <x>{concat(",",$nd)}</x>
                   ').value('.','nvarchar(max)'),1,1,'');

If you can be sure, that your data will never include any blanks, you might use this simple approach:

SELECT REPLACE(@xml.query(N'data(/RuleSetting/CurrentQueue)')
                   .value(N'.',N'nvarchar(max)'),' ',',');

Hint: XQuery function data() will return all values separated by a blank, which you can replace on string level.