Search code examples
sql-serverxmlconcatenationstuff

SQL SERVER: concatenate node values of XML column

I have the following table that holds a column which is in XML:

Id Label Details
1 Test1 <terms><destination><email>email11@foo.com</email><email>email12@foo.com</email></destination><content>blabla</content></terms>
2 Test2 <terms><destination><email>email21@foo.com</email><email>email22@foo.com</email></destination><content>blabla</content></terms>

I would like a query that produces the following output:

Id Label Destination
1 Test1 email11@foo.com, email12@foo.com
2 Test2 email21@foo.com, email22@foo.com

Any clue on how I can concat the XML email node values as a column along the related columns (Id and Label)? Thanks ahead

Solution 
  • select ID, Label,
    stuff(
        details.query('for $step in /terms/destination/email/text() return concat(", ", string($step))')
        .value('.', 'nvarchar(max)'),
    1, 2, '')
from @tbl;