Search code examples
sql-serverxquery

Get XML nodes from SQL Server column as comma-separated list

I have a data stored in a xml column and need a comma-separated list of child nodes. Using script below, I can only get "A B C". Please help me get "A,B,C" using xquery (Simple replace of space with comma does not help because we have data with spaces inside).

create table Temp12345 (col1 xml)
go

insert into Temp12345 (col1)
values('<fd><field i="22"><v>A</v><v>B</v><v>C</v></field></fd>')
go

select col1.value('(/fd/field[@i=22])[1] ', 'NVarchar(Max)') 
from Temp12345
go

drop table Temp12345
go
Solution

  • Try this:

    SELECT
    STUFF((SELECT 
              ',' + fd.v.value('(.)[1]', 'varchar(10)')
           FROM 
              Temp12345
           CROSS APPLY
              col1.nodes('/fd/field/v') AS fd(v)
           FOR XML PATH('')
          ), 1, 1, '')

    This gives me A,B,C - does it work for you, too?