时间戳

首页 > 解决方案 > 用于从两个不同节点的 SOAP 1.1 中提取数据的 SQL Server 查询

sql-server - 用于从两个不同节点的 SOAP 1.1 中提取数据的 SQL Server 查询

问题描述

我可以使用 SQL Server 从以下 XML 中提取数据：

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <soap:Body>
  <GetBatchResponse xmlns="https://webservices.aba.com/">
   <web:GetBatchResult xmlns:web="https://webservices.aba.com/">
    <web:Loco>
     <web:LocoType>
      <web:Errors />
      <web:Pos>
       <web:PosType>
        <web:Name>Sam</web:Name>
        <web:Height>5.10</web:Height>
        <web:Age>26</web:Age>
        <web:Salary />
       </web:PosType>
      </web:Pos>
      <web:Address />
      <web:EmpUnit>21</web:EmpUnit>
      <web:EmpOrg>XE</web:EmpOrg>
     </web:LocoType>
     <web:LocoType>
      <web:Errors />
      <web:Pos>
       <web:PosType>
        <web:Name>Emma</web:Name>
        <web:Height>5.7</web:Height>
        <web:Age>21</web:Age>
        <web:Salary />
       </web:PosType>
       <web:PosType>
        <web:Name>Prince</web:Name>
        <web:Height>5.11</web:Height>
        <web:Age>25</web:Age>
        <web:Salary />
       </web:PosType>
       <web:PosType>
        <web:Name>Smith</web:Name>
        <web:Height>5.6</web:Height>
        <web:Age>24</web:Age>
        <web:Salary />
       </web:PosType>
      </web:Pos>
      <web:Address />
      <web:EmpUnit>17</web:EmpUnit>
      <web:EmpOrg>XE</web:EmpOrg>
     </web:LocoType>
    </web:Loco>
    <web:EndTimeUTC xsi:nil="true" />
   </web:GetBatchResult>
  </GetBatchResponse>
 </soap:Body>
</soap:Envelope>

将上述 XML 存储在一个TestXML表中，列XMLPayload (Column DataType: XML)

我正在使用以下查询：

CREATE TABLE testResult (Name VARCHAR(10), Height FLOAT, Age INT, SALARY BIGINT, EmpUnit INT, EmpOrg VARCHAR(10))

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.xmlsoap.org/soap/envelope/')
INSERT INTO testResult
SELECT
 reponse.data.value('*.Name/text())[1]','VARCHAR(10)') AS Name,
 reponse.data.value('*.Height/text())[1]','FLOAT') AS Height,
 reponse.data.value('*.Age/text())[1]','INT') AS Age,
 reponse.data.value('*.Salary/text())[1]','BIGINT)') AS Salary,
 NULL AS EmpUnit,
 NULL AS EmpOrg
FROM testXML t
CROSS APPLY XMLPayload.nodes('/*:Envelope/*:Body/*:GetBatchResponse/*:GetBatchResult/*:Loco/*:LocoType/*:Pos/*:PosType') AS response(data)

使用上面的查询，我可以得到姓名、身高、年龄和薪水。我无法获取 EmpUnit 和 EmpOrg 的数据，这就是我在上述查询中使用 NULL 的原因。

我还需要获取 EmpUnit 和 EmpOrg 的值。例如，EmpUnit 和 EmpOrg 将仅具有第一行和最后一行的值（21，XE 和 17，XE），而对于其他行，EmpUnit 和 EmpOrg 将为空。

需要一些帮助。提前致谢。

标签： sql-serverxmlweb-servicessoap

解决方案

如果我们假设您的 XML 是有效的（我已在下面进行了更正），您可以这样做。

首先，你的不工作的原因是因为你要去Pos/PosType你的nodes通话中的节点，但EmpUnit在LocoType节点中。

相反，使用 2 个nodes调用。此外，我在XMLNAMESPACES和 XML nodes/value调用中明确定义了您的命名空间：

DECLARE @XML xml = '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <soap:Body>
  <GetBatchResponse xmlns="https://webservices.aba.com/">
   <web:GetBatchReult xmlns:web="https://webservices.aba.com/">
    <web:Loco>
     <web:LocoType>
      <web:Errors />
      <web:Pos>
       <web:PosType>
        <web:Name>Sam</web:Name>
        <web:Height>5.10</web:Height>
        <web:Age>26</web:Age>
        <web:Salary />
       </web:PosType>
      </web:Pos>
      <web:Address />
      <web:EmpUnit>21</web:EmpUnit>
      <web:EmpOrg>XE</web:EmpOrg>
     </web:LocoType>
     <web:LocoType>
      <web:Errors />
      <web:Pos>
       <web:PosType>
        <web:Name>Emma</web:Name>
        <web:Height>5.7</web:Height>
        <web:Age>21</web:Age>
        <web:Salary />
       </web:PosType>
       <web:PosType>
        <web:Name>Prince</web:Name>
        <web:Height>5.11</web:Height>
        <web:Age>25</web:Age>
        <web:Salary />
       </web:PosType>
       <web:PosType>
        <web:Name>Smith</web:Name>
        <web:Height>5.6</web:Height>
        <web:Age>24</web:Age>
        <web:Salary />
       </web:PosType>
      </web:Pos>
      <web:Address />
      <web:EmpUnit>17</web:EmpUnit>
      <web:EmpOrg>XE</web:EmpOrg>
     </web:LocoType>
    </web:Loco>
    <web:EndTimeUTC xsi:nil="true" />
   </web:GetBatchReult>
  </GetBatchResponse>
 </soap:Body>
</soap:Envelope>';
--Seems odd that the default namespace and the "web" namespace have the same value.
WITH XMLNAMESPACES (DEFAULT 'https://webservices.aba.com/', 'http://schemas.xmlsoap.org/soap/envelope/' AS soap, 'https://webservices.aba.com/' AS web)
SELECT L.LT.value('(./web:EmpUnit/text())[1]','int') AS EmpUnit,
       L.LT.value('(./web:EmpOrg/text())[1]','char(2)') AS EmpOrg,
       P.PT.value('(./web:Name/text())[1]','varchar(10)') AS Name,
       P.PT.value('(./web:Height/text())[1]','decimal(5,2)') AS Height, --Float name no sense for a precise value
       P.PT.value('(./web:Age/text())[1]','int') AS Age,
       P.PT.value('(./web:Salary/text())[1]','bigint') AS Salary --Can someone really be paid over 2billion?
FROM @XML.nodes('soap:Envelope/soap:Body/GetBatchResponse/web:GetBatchReult/web:Loco/web:LocoType') L(LT)
     CROSS APPLY L.LT.nodes('web:Pos/web:PosType')P(PT);

推荐阅读