sql - 从 SQL XML 字段中提取值

Question

使用 MS SQL Server，我在 XML 字段(称为 XML)中获得了一些数据，其结构如下:

<Transaction01>
  <TransactionSetPurpose>Insert</TransactionSetPurpose>
  <POHeader>
    <PO_NBR>LG40016181</PO_NBR>
  </POHeader>
</Transaction01>

我正在尝试创建一个 SQL 查询来获取另一个名为 SubmittedDate 的列，以及来自该 XML 字段的 PO_NBR。作为 XPath 的新手，我阅读了许多示例并尝试了 query 和 value，但我还没有成功。例如:

SELECT SubmittedDate, 
       XML.query('data(/POHeader/PO_NBR)') as PO_NBR
  FROM SubmitXML

这只会给我一个空列。从 Quassnoi 获得工作测试后，我从他的 XML 工作到我的，发现问题是根节点中的 xmlns 和 xmlns:i 属性:

<Transaction01 xmlns="http://services.iesltd.com/" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

那么我该如何解决呢？

Answer 1

SELECT  SubmittedDate, 
        XML.query('data(/Transaction01/POHeader/PO_NBR)') as PO_NBR
FROM    SubmitXML

你原来的XPath，/POHeader/PO_NBR，假设POHeader是根节点(其实不是)。

要检查的示例查询:

DECLARE @myxml XML
SET @myxml = '
<Transaction01>
  <TransactionSetPurpose>Insert</TransactionSetPurpose>
  <POHeader>
    <PO_NBR>LG40016181</PO_NBR>
  </POHeader>
</Transaction01>'

SELECT  @myxml.query('data(/Transaction01/POHeader/PO_NBR)')

如果 Transaction01 并不总是根节点(这不是一件好事)，使用这个:

SELECT  SubmittedDate, 
        XML.query('data(/*/POHeader/PO_NBR)') as PO_NBR
FROM    SubmitXML

通常，XML 模式假定标签名称是固定的，可变部分转到节点和属性的数据而不是它们的名称，如下所示:

<Transaction id='01'>
  <TransactionSetPurpose>Insert</TransactionSetPurpose>
  <POHeader>
    <PO_NBR>LG40016181</PO_NBR>
  </POHeader>
</Transaction>

更新:

您应该使用 WITH XMLNAMESPACES 声明命名空间:

DECLARE @myxml XML
SET @myxml = '
<Transaction01 xmlns="http://services.iesltd.com/" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <TransactionSetPurpose>Insert</TransactionSetPurpose>
  <POHeader>
    <PO_NBR>LG40016181</PO_NBR>
  </POHeader>
</Transaction01>'

;
WITH    XMLNAMESPACES
        (
        'http://services.iesltd.com/' AS m
        )
SELECT  @myxml.query
        (
        'data(/*/m:POHeader/m:PO_NBR)'
        )

更新 2:

排序:

;
WITH    XMLNAMESPACES
        (
        'http://services.iesltd.com/' AS m
        )
SELECT  SubmittedDate, 
        XML.value('(/*/m:POHeader/m:PO_NBR)[1]', 'NVARCHAR(200)') AS po_nbr
FROM    SubmitXML
ORDER BY
        po_nbr