sql-server - 从 XML 列中选择所有 XML 节点

Question

我有一个包含 XML 列的表。每个条目的 XML 结构是完全扁平的，甚至没有父标记 - 这是一个条目的示例:

<tag1>1.22</tag1>
<tag3>5</tag3>
<tag12>-1.22</tag>

到目前为止，我已经能够做这样的事情:

SELECT CAST(xml_column AS NVARCHAR(MAX)) as XML_text

并在我这边解析 XML。或者我显然可以编写 xpath 查询来将标签选择到列中，这就是我想要的，除了有大约 1000 个可能的标签名称，我不想把它们全部写出来(并且可能错过一个)。

简而言之，我该怎么做:

| ID | XML type column                |
| 1  | <tag1>1</tag1><tag2>2</tag2>   |
| 2  | <tag2>8</tag2><tag34>1</tag34> |

为此:

| ID | tag1 | tag2 | tag34 |
| 1  | 1    | 2    | NULL  |
| 2  | NULL | 8    | 1     |

对于我能在我的数据集中找到的任何标签，事先不知道它们？我也会满足于此:

| ID | tag   | value | 
| 1  | tag1  | 1     |
| 1  | tag2  | 2     |
| 2  | tag2  | 8     |
| 2  | tag34 | 1     |

Answer 1

以下内容会将您的没有根节点的平面 XML 转换为经典的 EAV 列表:

DECLARE @tbl TABLE(ID INT IDENTITY, YourXml XML);
INSERT INTO @tbl VALUES
 ('<tag1>1</tag1><tag2>2</tag2>')
,('<tag3>3</tag3><tag4>4</tag4><tag5>5</tag5>');

--查询

SELECT t.ID
      ,AllNodes.value('local-name(.)','nvarchar(max)') AS TagName
      ,AllNodes.value('text()[1]','nvarchar(max)') AS TagValue
FROM @tbl t
CROSS APPLY t.YourXml.nodes('/*') A(AllNodes);

XQuery 函数local-name() 允许查询元数据，text() 节点表示元素的内容。