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

Question

我能够使用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(列数据类型: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 &也是。比如EmpUnit &EmpOrg将只有第一行和最后一行的值(21,XE &17, XE)，对于其他行，EmpUnit &EmpOrg将为空。

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

Answer 1

如果我们假设您的XML是有效的(我在下面更正了这一点)，您可以这样做。

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

改为使用2个nodes调用。此外，我还在XMLNAMESPACES和XMLnodes/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);