sql - T-SQL XQuery 用于为每个嵌套元素的每个匹配的 XML 值返回一个结果行

Question

给定表 y 中的 XML 数据类型列 x，其值如下:

<a>
<b>
<c>1</c>
<c>2</c>
<c>3</c>
</b>
</a>

如何使用 T-SQL XQuery 获取结果集，以便每个 <c> 返回一行元素值？假设表 y 也有列 z，其中包含 <c> 的数量。 x 列中的元素 - 只是为了让我们有一个额外的列要返回。

到目前为止我们能想到的最好的是:

select 
z, 
cvalues = x.query('data(a/b/c)')
from y

然而，以上将在 y 中每行返回一行，并且有一个空格分隔的 <c> 值列表。列 cvalues 中的元素。

我们怎样才能得到这样的结果集:

z cvalues
3 1
3 2
3 3

而不是我们现在得到的:

z cvalues
3 1 2 3 

谢谢!

Answer 1

您可以将CROSS APPLY 与nodes 结合使用:

SELECT 
  z, 
  cvalues = s.c.value('.', 'int')
FROM y
CROSS APPLY x.nodes('//c') AS s(c);

LiveDemo

输出:

╔═══╦═════════╗
║ z ║ cvalues ║
╠═══╬═════════╣
║ 3 ║       1 ║
║ 3 ║       2 ║
║ 3 ║       3 ║
╚═══╩═════════╝

请记住:

//c 匹配所有 c xml 元素，无论它在层次结构中的什么位置

/a/b/c 将匹配嵌套在 a 和 b 中的 c xml 元素/p>