問題描述

我能夠使用 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);

這篇關于SQL Server 查詢從兩個不同節點從 SOAP 1.1 中提取數據的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！