如何通过SQL Server从XML节点中获取值

Question

我在网上找到了一些关于这个问题的信息，但是我无论如何都无法使其工作。

这是我的XML：

我需要提取每个 节点的ID和Name值，有很多节点。

我尝试过以下方法，但它返回NULL：

select [xml].value('(/Alter/Object/ObjectDefinition/MeasureGroup/Partitions/Partition/ID)[1]', 'varchar(max)')
from test_xml

我理解上述代码将仅返回一条记录。我的问题是，如何返回所有记录？

以下是XML文本（简化版）：

<Alter xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" AllowCreate="true" ObjectExpansion="ExpandFull">
  <ObjectDefinition>
    <MeasureGroup xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <ID>ts_homevideo_sum_20140430_76091ba1-3a51-45bf-a767-f9f3de7eeabe</ID>
      <Name>table_1</Name>
      <StorageMode valuens="ddl200_200">InMemory</StorageMode>
      <ProcessingMode>Regular</ProcessingMode>
      <Partitions>
        <Partition>
          <ID>123</ID>
          <Name>2012</Name>
        </Partition>
        <Partition>
          <ID>456</ID>
          <Name>2013</Name>
        </Partition>
      </Partitions>
    </MeasureGroup>
  </ObjectDefinition>
</Alter>

- JJ.

---

这个命名空间的东西我还在努力学习和理解，也许其他人可以给出一个可行的答案并解释一下。如果我找到了解决方法，我会发布一个新的答案。 - Ryan Wilson

没问题。谢谢您的尝试！ - JJ.

不用谢。很抱歉我不能提供更多帮助。 - Ryan Wilson

Answer 1

你需要这样的东西：

DECLARE @MyTable TABLE (ID INT NOT NULL, XmlData XML)

INSERT INTO @MyTable (ID, XmlData)
VALUES (1, '<Alter xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" AllowCreate="true" ObjectExpansion="ExpandFull">
  <ObjectDefinition>
    <MeasureGroup xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <ID>ts_homevideo_sum_20140430_76091ba1-3a51-45bf-a767-f9f3de7eeabe</ID>
      <Name>table_1</Name>
      <StorageMode valuens="ddl200_200">InMemory</StorageMode>
      <ProcessingMode>Regular</ProcessingMode>
      <Partitions>
        <Partition>
          <ID>123</ID>
          <Name>2012</Name>
        </Partition>
        <Partition>
          <ID>456</ID>
          <Name>2013</Name>
        </Partition>
      </Partitions>
    </MeasureGroup>
  </ObjectDefinition>
</Alter>')

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/analysisservices/2003/engine')
SELECT 
    tbl.ID,
    MeasureGroupID = xc.value('(ID)[1]', 'varchar(200)'),
    MeasureGroupName = xc.value('(Name)[1]', 'varchar(200)'),
    PartitionID = xp.value('(ID)[1]', 'varchar(200)'),
    PartitionName = xp.value('(Name)[1]', 'varchar(200)')
FROM
    @MyTable tbl
CROSS APPLY
    tbl.XmlData.nodes('/Alter/ObjectDefinition/MeasureGroup') AS XT(XC)
CROSS APPLY
    XC.nodes('Partitions/Partition') AS XT2(XP)
WHERE   
    ID = 1

首先，您必须尊重并包含在XML文档根部定义的默认XML命名空间。

接下来，您需要进行嵌套调用以获取所有和所有包含的节点，以便您可以进入这些XML片段并从中提取ID和Name。

然后，输出应该类似于以下内容：

- marc_s

---

我自己也在努力理解这个问题，为什么你不需要将 xmlns:xsd="http://www.w3.org/2001/XMLSchema" 添加到 XML 命名空间中呢？是因为 xsd 已经被声明了吗？ - Ryan Wilson

谢谢，Marc_s！这解决了我的问题。虽然我不得不删除tbl.ID / ID = 1这部分。 - JJ.

@marc_s 节点分区和分区是否被认为是嵌套的，因为 MeasureGroup 是它自己的 xml？这就是为什么需要使用 CROSS APPLY 的原因吗？ - Ryan Wilson

@RyanWilson：不需要添加xsd命名空间，因为它既不是（a）默认命名空间（没有前缀的XML命名空间），也没有在任何地方使用。 - marc_s

@marc_s 谢谢。你帮了我很多。 - Ryan Wilson