时间戳

首页 > 解决方案 > 在 SQL Sever 中提取包含多个命名空间和不同属性的 soap xml 标记值

sql-server - 在 SQL Sever 中提取包含多个命名空间和不同属性的 soap xml 标记值

问题描述

下面是用于提取值的示例 SOAP xml 和查询 -

DECLARE @xml XML='<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <soapenv:Body>
  <xsi:MaintenanceOrder xmlns:xsi="http://schema.xyz.com/abc/2" xmlns:ush="http://www.xyz.nl/abc" xmlns:xsj="http://www.w3.org/2001/XMLSchema-instance" languageCode="en-US" releaseID="9.2" systemEnvironmentCode="Production" versionID="2.8.0">
   <xsi:DataArea>
    <xsi:MaintenanceOrder>
     <xsi:MaintenanceOrderHeader>
      <xsi:UserArea>
       <xsi:Property>
        <xsi:NameValue accountingEntity="*" listID="*" name="OrderDate" type="DATE">2020-03-30T00:00:00</xsi:NameValue>
       </xsi:Property>
       <xsi:Property>
        <xsi:NameValue accountingEntity="*" listID="*" name="ReportDate" type="DATE">2020-04-30T00:00:00</xsi:NameValue>
       </xsi:Property>
      </xsi:UserArea>
     </xsi:MaintenanceOrderHeader>
    </xsi:MaintenanceOrder>
   </xsi:DataArea>
  </xsi:MaintenanceOrder>
 </soapenv:Body>
</soapenv:Envelope>'
select A.r.value('(xsi:NameValue[@name="OrderDate"])[1]','date') as "OrderDate"
,A.r.value('(xsi:NameValue[@name="ReportDate"])[1]','date') as "ReportDate"
FROM @xml.nodes('/*:Envelope/*:Body/*:MaintenanceOrder/*:DataArea/*:MaintenanceOrder/*:MaintenanceOrderHeader/*:UserArea/*:Property') AS A(r)

问题 1 - 通过删除 MaintenanceOrder 中的命名空间，然后查询返回值，否则返回 null。

问题 2 - 所需的输出是具有多个标签值的单行，但查询给出了多行。

任何帮助将不胜感激。

标签： sql-serverxml

解决方案

尝试使用with xmlnamespaces声明和显式路径，以便您的 XPath 查询仅匹配您期望的元素 - 并且执行与预期一样好。

例如：

declare @xml xml =
N'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <soapenv:Body>
  <xsi:MaintenanceOrder xmlns:xsi="http://schema.xyz.com/abc/2" xmlns:ush="http://www.xyz.nl/abc" xmlns:xsj="http://www.w3.org/2001/XMLSchema-instance" languageCode="en-US" releaseID="9.2" systemEnvironmentCode="Production" versionID="2.8.0">
   <xsi:DataArea>
    <xsi:MaintenanceOrder>
     <xsi:MaintenanceOrderHeader>
      <xsi:UserArea>
       <xsi:Property>
        <xsi:NameValue accountingEntity="*" listID="*" name="OrderDate" type="DATE">2020-03-30T00:00:00</xsi:NameValue>
       </xsi:Property>
       <xsi:Property>
        <xsi:NameValue accountingEntity="*" listID="*" name="ReportDate" type="DATE">2020-04-30T00:00:00</xsi:NameValue>
       </xsi:Property>
      </xsi:UserArea>
     </xsi:MaintenanceOrderHeader>
    </xsi:MaintenanceOrder>
   </xsi:DataArea>
  </xsi:MaintenanceOrder>
 </soapenv:Body>
</soapenv:Envelope>';

with xmlnamespaces (
  'http://schemas.xmlsoap.org/soap/envelope/' as s11,
  'http://schema.xyz.com/abc/2' as abc
)
select
  A.r.value('(abc:Property/abc:NameValue[@name="OrderDate"])[1]','date') as "OrderDate",
  A.r.value('(abc:Property/abc:NameValue[@name="ReportDate"])[1]','date') as "ReportDate"
from @xml.nodes('/s11:Envelope/s11:Body/abc:MaintenanceOrder/abc:DataArea/abc:MaintenanceOrder/abc:MaintenanceOrderHeader/abc:UserArea') as A(r);

在单行中返回预期值：

OrderDate   ReportDate
2020-03-30  2020-04-30

推荐阅读