SQL SERVER:连接 XML 列的节点值

Question

我有下表，其中包含XML中的列：

Detailsstyle="文本对齐：右;">1style="text-align： right;">2><terms><destination><email>[e-mail]</email><email>[e-mail]</email></destination><content>blabla</content></terms>

Id	Label
	Test1	<terms><destination><email>[e-mail]</email><email>[e-mail]</email></destination><content>blabla</content></terms>
	Test2

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

请尝试以下解决方案。

由于未提供 DDL 和样本数据填充， 假设">详细信息"列的数据类型为 XML。

.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>[e-mail]</email><email>[e-mail]</email></destination><content>blabla</content></terms>'),
('Test2',N'<terms><destination><email>[e-mail]</email><email>[e-mail]</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;

输出

+----+-------+----------------------------------+
| ID | Label |           Destination            |
+----+-------+----------------------------------+
|  1 | Test1 | [e-mail], [e-mail] |
|  2 | Test2 | [e-mail], [e-mail] |
+----+-------+----------------------------------+