查询XML列的多行，将子节点提取到多行中

Question

在SQL server中，我有一个带有XML列的表，并且从每个XML中，我在父节点下有节点，并且我希望将节点中的每个值选择到每个XML的单独行中。

我如何做到这一点?

下面是表格的样例:

Name    | Message
John    | <User><Data><Valuelist><Value>123</Value><Value>456</Value><Value>789</Value><Value>654</Value></ValueList></Data></User>
Jack    | <User><Data><ValueList><Value>555</Value><Value>455</Value></ValueList></Data></User>
Jane    | <User><Data><Valuelist><Value>576</Value><Value>854</Value><Value>933</Value></ValueList></Data></User>

下面是我试图查询的Message列的示例XML，为了更清晰:

<User>
<Data>
<ValueList>
<Value>123</Value>
<Value>456</Value>
<Value>789</Value>
<Value>654</Value>
</ValueList>
</Data>
</User>

下面是我希望看到的结果:

Name    | Values
John    | 123
John    | 456
John    | 789
John    | 654
Jack    | 555
Jack    | 455
Jane    | 576
Jane    | 854
Jane    | 933

我一直在尝试使用下面的查询:

select t.Name, x.y.value('(Value)', 'nvarchar(10)') [Values]
from TABLE t
cross apply t.Message.nodes('//ValueList') as x(y)

但是它给了我下面的错误:

'value()'需要一个单例(或空序列)，找到类型为'xdt:untypedAtomic *'的操作数

我只能选择一个值使用:

select t.Name, x.y.value('(Value[1])', 'nvarchar(10)') [Values]
from TABLE t
cross apply t.Message.nodes('//ValueList') as x(y)

我一直在用谷歌搜索，但找不到我想做的事情。

我该怎么做?

我的SQL server版本是2016 SP2.

提前感谢!

Answer 1

请尝试以下解决方案。

/p>

-- DDL and sample data population, start
DECLARE @tbl table (ID INT IDENTITY PRIMARY KEY, Name VARCHAR(20), Message XML);
INSERT INTO @tbl (Name, Message) VALUES
('John', N'<User><Data><ValueList><Value>123</Value><Value>456</Value><Value>789</Value><Value>654</Value></ValueList></Data></User>'),
('Jack', N'<User><Data><ValueList><Value>555</Value><Value>455</Value></ValueList></Data></User>'),
('Jane', N'<User><Data><ValueList><Value>576</Value><Value>854</Value><Value>933</Value></ValueList></Data></User>');
-- DDL and sample data population, end
SELECT t.ID, t.Name
, c.value('(./text())[1]', 'nvarchar(10)') AS [Values]
FROM @tbl AS t
CROSS APPLY Message.nodes('/User/Data/ValueList/Value') as t1(c);

+----+------+--------+
| ID | Name | Values |
+----+------+--------+
|  1 | John |    123 |
|  1 | John |    456 |
|  1 | John |    789 |
|  1 | John |    654 |
|  2 | Jack |    555 |
|  2 | Jack |    455 |
|  3 | Jane |    576 |
|  3 | Jane |    854 |
|  3 | Jane |    933 |
+----+------+--------+