从 SQL Server 中的 XML 文件中读取主详细信息数据

Question

>我有一个这样的XML文件：

<invoice xmlns:xsi="http://......" xmlns="http://..." xmlns:omismsg="http://..." omismsg:action="update">
    <status>1</status>
    <transNo>17AUAU0000118N</transNo>
    <transType>5</transType>
    <externalRefId/>
    <billExternalRef/>
    <invoiceDetails omismsg:action="update">
        <transNo>17AUAU0000118N</transNo>
        <transType>5</transType>
        <seqNo>001</seqNo>
    </invoiceDetails>
    <invoiceDetails omismsg:action="update">
        <transNo>17AUAU0000118N</transNo>
        <transType>5</transType>
        <seqNo>002</seqNo>
    </invoiceDetails>
</invoice>

我写了这个命令来提取所有数据

select
 c3.value('status[1]','int'),
 c3.value('transNo[1]','VARCHAR(255)'),
 c3.value('transType[1]','int'),
 c3.value('externalRefId[1]','VARCHAR(255)'),
 c3.value('billExternalRef[1]','VARCHAR(255)')
 ,c4.value('(transNo)[1]','VARCHAR(10)')
 ,c4.value('(transType)[1]','int')
 ,c4.value('(seqNo)[1]','VARCHAR(10)')
from
(
  select 
    cast(c1 as xml)
  from 
    OPENROWSET (BULK '\SQL-CSLDataWarehousedevelopmentETL-ToIMOStestinvoice.xml', SINGLE_BLOB) as T1(c1)
)as T2(c2)
cross apply c2.nodes('/invoice') T3(c3)
cross apply c2.nodes('/invoice/invoiceDetails') T4(c4)

但结果是空的。

我检查了XML文件，发现如果我替换

<invoice xmlns:xsi="http://......" xmlns="http://..." xmlns:omismsg="http://..." omismsg:action="update">

跟

<invoice>

和

<invoiceDetails omismsg:action="update">

跟

<invoiceDetails>

它会起作用。但实际上我可以更改 xml 文件。我应该如何更改我的命令才能工作？

Answer 1

您有一个命名空间问题。有两种方法可以采用

• 命名空间的通配符
• (推荐(声明并使用命名空间

试试这个

DECLARE @xml XML=
N'<invoice xmlns:xsi="http://......" xmlns="http://..." xmlns:omismsg="http://..." omismsg:action="update">
  <status>1</status>
  <transNo>17AUAU0000118N</transNo>
  <transType>5</transType>
  <externalRefId />
  <billExternalRef />
  <invoiceDetails omismsg:action="update">
    <transNo>17AUAU0000118N</transNo>
    <transType>5</transType>
    <seqNo>001</seqNo>
  </invoiceDetails>
  <invoiceDetails omismsg:action="update">
    <transNo>17AUAU0000118N</transNo>
    <transType>5</transType>
    <seqNo>002</seqNo>
  </invoiceDetails>
</invoice>';

-

-此查询将使用*:忽略命名空间

select
 c3.value('(*:status/text())[1]','int'),
 c3.value('(*:transNo/text())[1]','VARCHAR(255)'),
 c3.value('(*:transType/text())[1]','int'),
 c3.value('(*:externalRefId/text())[1]','VARCHAR(255)'),
 c3.value('(*:billExternalRef/text())[1]','VARCHAR(255)'),
 c3.value('(@*:action)[1]','varchar(255)')
 ,c4.value('(*:transNo/text())[1]','VARCHAR(10)')
 ,c4.value('(*:transType/text())[1]','int')
 ,c4.value('(*:seqNo/text())[1]','VARCHAR(10)')
 ,c4.value('(@*:action)[1]','VARCHAR(10)')
from @xml.nodes('/*:invoice') T3(c3)
cross apply c3.nodes('*:invoiceDetails') T4(c4);

-

-此查询声明命名空间并相应地使用它们

WITH XMLNAMESPACES(DEFAULT 'http://...'
                          ,'http://...' AS omismsg)
select
 c3.value('(status/text())[1]','int'),
 c3.value('(transNo/text())[1]','VARCHAR(255)'),
 c3.value('(transType/text())[1]','int'),
 c3.value('(externalRefId/text())[1]','VARCHAR(255)'),
 c3.value('(billExternalRef/text())[1]','VARCHAR(255)'),
 c3.value('(@omismsg:action)[1]','varchar(255)')
 ,c4.value('(transNo/text())[1]','VARCHAR(10)')
 ,c4.value('(transType/text())[1]','int')
 ,c4.value('(seqNo/text())[1]','VARCHAR(10)')
 ,c4.value('(@omismsg:action)[1]','VARCHAR(10)')
from @xml.nodes('/invoice') T3(c3)
cross apply c3.nodes('invoiceDetails') T4(c4);

两者都返回相同的