动态获取 SQL Server 中不同列中的 XML 数据类型值

Question

我有一个表，其中有一列具有xml数据类型，并包含xml格式的值。

例如

<row><A>1</A><B>xyz</B></row>
<row><A>2</A><B>jkl</B></row>

并希望在表中输出：

-------------
|  A  |  B  |
-------------
|  1  | xyz |
|  2  | jkl |
-------------

我想动态地展示这样的 ouptup。是否有任何函数将 xml 数据返回到表中？

Answer 1

你想要什么似乎没有什么动态的，你只需要使用 XQUERY：

SELECT V.YourXML.value('(/row/A/text())[1]','int') AS A,
V.YourXML.value('(/row/B/text())[1]','varchar(3)') AS B
FROM (VALUES(CONVERT(xml,'<row><A>1</A><B>xyz</B></row>')),
(CONVERT(xml,'<row><A>2</A><B>jkl</B></row>')))V(YourXML);

Answer 2

您可以检查并尝试此查询。

declare @X xml = '<row><A>1</A><B>xyz</B></row><row><A>2</A><B>jkl</B></row>';
select  x.r.value('(A)[1]', 'varchar(100)') as [A],
x.r.value('(B)[1]', 'varchar(500)') as [B]
from    @X.nodes('/row') as x(r);

这是输出：

A   B
-------
1   xyz
2   jkl

您可以在此处找到演示。 若要动态访问节点名称及其值，可以尝试以下查询。

DECLARE @input XML =  '<row><A>1</A><B>xyz</B></row><row><A>2</A><B>jkl</B></row>'
SELECT
NodeName = C.value('local-name(.)', 'varchar(50)'),
NodeValue = C.value('(.)[1]', 'varchar(50)') 
FROM @input.nodes('/row/*') AS T(C)

它将给出如下所示的输出。要了解更多信息，您可以参考此SO答案。

NodeName    NodeValue
--------------------
A           1
B           xyz
A           2
B           jkl