SQL SERVER: concatenate node values of XML column

Question

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

Id	Label	Details
1	Test1	`<terms><destination><email>[email protected]</email><email>[email protected]</email></destination><content>blabla</content></terms>`
2	Test2	`<terms><destination><email>[email protected]</email><email>[email protected]</email></destination><content>blabla</content></terms>`

I would like a query that produces the following output:

Id	Label	Destination
1	Test1	`[email protected], [email protected]`
2	Test2	`[email protected], [email protected]`

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

Charlieface · Accepted Answer · 2021-02-16 13:09:28Z

2

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

answered Feb 16, 2021 at 13:09

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yitzhak Khabinsky · Accepted Answer · 2021-02-16 12:50:37Z

Please try the following solution.

Because DDL and sample data population were not provided, assumption is that the Details column is of XML data type.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Label VARCHAR(20), Details XML);
INSERT INTO @tbl (Label, Details) VALUES
('Test1',N'<terms><destination><email>[email protected]</email><email>[email protected]</email></destination><content>blabla</content></terms>'),
('Test2',N'<terms><destination><email>[email protected]</email><email>[email protected]</email></destination><content>blabla</content></terms>');
-- DDL and sample data population, end

SELECT ID, Label 
    , REPLACE(Details.query('data(/terms/destination/email/text())').value('.','VARCHAR(MAX)'), SPACE(1), ', ') AS Destination
FROM @tbl;

Output

+----+-------+----------------------------------+
| ID | Label |           Destination            |
+----+-------+----------------------------------+
|  1 | Test1 | [email protected], [email protected] |
|  2 | Test2 | [email protected], [email protected] |
+----+-------+----------------------------------+

Collectives™ on Stack Overflow

SQL SERVER: concatenate node values of XML column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related