在 SQL Server 中读取指定 XML 节点的 XML "name"并获取其"value"

Question

如何在SQL Server数据库中读取以下XML：

<Row>
<Keys>
<Key>
<Name>NAME_2</Name>
<Data>22</Data>
</Key>
<Key>
<Name>NAME_3</Name>
<Data>33</Data>
</Key>
<Key>
<Name>NAME_1</Name>
<Data>98</Data>
</Key>
</Keys>
</Row>

我想从XML中进行选择，只得到一行带列：

NAME_1, NAME_2, NAME_3.

这就是为什么我需要一些东西，让我找到值为Name_1的Key/Key/Name，并返回其Key/Key/Data，等等。。。

预期结果集(1 ROW(：

NAME_1  NAME_2  NAME_3
-----------------------
98     22      33

还有一件更重要的事。这些值NAME_1、NAME_2、NAME_3。我在等他们。这就是为什么我需要查询它们并返回一行的值。

Answer 1

这是我使用预期名称的方法：

DECLARE @xml XML=
'<Row>
<Keys>
<Key>
<Name>NAME_2</Name>
<Data>22</Data>
</Key>
<Key>
<Name>NAME_3</Name>
<Data>33</Data>
</Key>
<Key>
<Name>NAME_1</Name>
<Data>98</Data>
</Key>
</Keys>
</Row>';

--最明确(推荐(的版本：

SELECT @xml.value('(/Row/Keys/Key[(Name/text())[1]="NAME_1"]/Data/text())[1]','int') AS NAME_1
,@xml.value('(/Row/Keys/Key[(Name/text())[1]="NAME_1"]/Data/text())[1]','int') AS NAME_1
,@xml.value('(/Row/Keys/Key[(Name/text())[1]="NAME_2"]/Data/text())[1]','int') AS NAME_2;

--相同，但不太明确(因此不推荐(

SELECT @xml.value('(//Key[Name="NAME_1"]/Data)[1]','int') AS NAME_1
,@xml.value('(//Key[Name="NAME_2"]/Data)[1]','int') AS NAME_1
,@xml.value('(//Key[Name="NAME_3"]/Data)[1]','int') AS NAME_2;

简而言之：

• 我们直接从XML中获取每个值
• 我们选择带有XQuery谓词的<Key>来请求元素，其中<Name>作为给定的字符串作为内容(=text()节点(
• 我们深入到<Key>下面的<Data>节点，获取其内容并将其作为int返回

Answer 2

假设您知道列名总是为NAME_1、NAME_2、NAME_3等，则可以执行以下操作，而不必使用动态SQL。

DECLARE @xml xml =
'<Row>
<Keys>
<Key>
<Name>NAME_2</Name>
<Data>22</Data>
</Key>
<Key>
<Name>NAME_3</Name>
<Data>33</Data>
</Key>
<Key>
<Name>NAME_1</Name>
<Data>98</Data>
</Key>
</Keys>
</Row>';
SELECT
*
FROM (
SELECT
x.f.value( 'Data[1]', 'int' ) AS [Data],
x.f.value( 'Name[1]', 'varchar(50)' ) AS [Name]
FROM @xml.nodes( '//Row/Keys/Key' ) x( f )
) AS d
PIVOT (
MAX( [Data] )
FOR [Name] IN ( [NAME_1], [NAME_2], [NAME_3] )
) AS x;

返回

+--------+--------+--------+
| NAME_1 | NAME_2 | NAME_3 |
+--------+--------+--------+
|     98 |     22 |     33 |
+--------+--------+--------+