时间戳

首页 > 解决方案 > 从 XML 列中检索 3 级 XML 字段的所有实例

sql - 从 XML 列中检索 3 级 XML 字段的所有实例

问题描述

我的一个表中有一个 XML 数据字段，基本上如下所示：

<App xmlns='http://Namespace1'>
    <Package xmlns='http://Namespace2'>
        <Item>
            <ItemDetails xmlns='http://Namespace3'>
                <ItemName>ItemNameValue</ItemName>
            </ItemDetails>
            other_item_stuff
        </Item>
        <Item>
            <ItemDetails>
                    <ItemName>ItemNameValue</ItemName>
            </ItemDetails>
        </Item>
        ...
    </Package>
</App>

我需要从 XML 中获取所有 ItemNameValues。

我试图根据我的目的调整网上找到的许多示例，但都失败了。我似乎能做的最好的事情是为每个包裹获得一个 ItemName。我认为 CROSS APPLY 是我需要去的地方，但是检索所有 itemdetail.itemname 的语法让我望而却步。

这是我最近的失败（什么都不返回）：

WITH XMLNAMESPACES(
'http://Namespace1' AS xsd,
'http://www.w3.org/2001/XMLSchema-instance' AS xsi,
'http://Namespace2' AS ns1,
'http://Namespace3' AS ns2)            
Items.d.value('(ns2:ItemDetails/ItemName/text())[1]','varchar(200)') as 
ItemName
FROM MyTable
CROSS APPLY XMLDataColumn.nodes('/xsd:App/ns1:Package/ns1:Item') Items(d)

我希望从每个 XML 字段中获取几条记录，但只能获取第一个元素。

标签： sqlxmlsql-server-2012

解决方案

这个问题最大的问题是 XML 本身：

<App xmlns="http://Namespace1">
  <Package xmlns="http://Namespace2">
    <Item>
      <ItemDetails xmlns="http://Namespace3">
        <ItemName>ItemNameValue</ItemName>
      </ItemDetails>
            other_item_stuff
        </Item>
    <Item>
      <ItemDetails>
        <ItemName>ItemNameValue</ItemName>
      </ItemDetails>
    </Item>
        ...
    </Package>
</App>

两个主要问题：

  • 命名空间都被声明为默认命名空间（它们不包括前缀）。如果没有明确说明，节点内的所有节点共享相同的默认命名空间。
  • 第一个<ItemDetails>生活在 namespacehttp://Namespace3中，而第二个<ItemDetails>生活在 namespace 中http://Namespace2（继承自<Package>）

这意味着：如果您可以（任何机会）更改 XML 的结构，请先尝试这样做。

如果您必须处理这个问题，您可以尝试这种干净但笨拙的方法。

WITH XMLNAMESPACES(
'http://Namespace1' AS ns1,
'http://www.w3.org/2001/XMLSchema-instance' AS xsi,
'http://Namespace2' AS ns2,
'http://Namespace3' AS ns3)    
SELECT COALESCE(Items.d.value('(ns2:ItemDetails/ns2:ItemName/text())[1]','varchar(200)')
               ,Items.d.value('(ns3:ItemDetails/ns3:ItemName/text())[1]','varchar(200)')) AS ItemName
FROM @xml.nodes('/ns1:App/ns2:Package/ns2:Item') Items(d);

另一种方法是使用命名空间通配符，但要注意名称不明确......

SELECT Items.d.value('(*:ItemDetails/*:ItemName/text())[1]','varchar(200)') AS ItemName
FROM @xml.nodes('/*:App/*:Package/*:Item') Items(d)

推荐阅读