SQL Server 查询从两个不同节点从 SOAP 1.1 中提取数据

2021-09-24 00:00:00 xml web-services soap sql-server

我能够使用 SQL Server 从以下 XML 中提取数据:

I'm able to extract data from the following XML using SQL Server:

<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(列数据类型:XML)

Stored the above XML in a TestXML table, column 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 的原因.

Using the above query, I was able to get Name, Height, Age & Salary. I wasn't able to fetch the data for EmpUnit and EmpOrg, that's why I used NULL in the above query.

我需要获取 EmpUnit & 的值EmpOrg 也是如此.像，EmpUnit &EmpOrg 将只有第一行和最后一行的值 (21,XE & 17, XE)，对于其他行，EmpUnit &EmpOrg 将为空.

I need to get the values for EmpUnit & EmpOrg as well. Like, EmpUnit & EmpOrg is going to have values for the 1st and last row only (21,XE & 17, XE), and for the other rows, EmpUnit & EmpOrg is going to be null.

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

Need some help. Thanks in advance.

推荐答案

如果我们假设你的 XML 是有效的，我已经在下面更正了，你可以这样做.

If we assume your XML is valid, which I've corrected in the below, you can do this.

首先，您的不工作的原因是因为您要在 nodes 调用中转到 Pos/PosType 节点，但是 EmpUnit 位于 LocoType 节点中.

Firstly, the reason yours isn't working is because you're going to the Pos/PosType node in your nodes call, but EmpUnit is in the LocoType node.

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

Instead, use 2 nodes calls. Also, I define your namespaces explicitly in both the XMLNAMESPACES and XML nodes/value calls:

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);

相关文章