問(wèn)題描述
我能夠使用 SQL Server 從以下 XML 中提取數(shù)據(jù):
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 存儲(chǔ)在 TestXML 表中,列 XMLPayload(列數(shù)據(jù)類(lèi)型:XML)
Stored the above XML in a TestXML table, column XMLPayload (Column DataType: XML)
我正在使用以下查詢(xún):
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)
使用上述查詢(xún),我??能夠獲得姓名、身高、年齡和年齡.薪水.我無(wú)法獲取 EmpUnit 和 EmpOrg 的數(shù)據(jù),這就是我在上述查詢(xún)中使用 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),對(duì)于其他行,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.
推薦答案
如果我們假設(shè)你的 XML 是有效的,我已經(jīng)在下面更正了,你可以這樣做.
If we assume your XML is valid, which I've corrected in the below, you can do this.
首先,您的不工作的原因是因?yàn)槟?nodes
調(diào)用中轉(zhuǎn)到 Pos/PosType
節(jié)點(diǎn),但是 EmpUnit
位于 LocoType
節(jié)點(diǎn)中.
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 個(gè) nodes
調(diào)用.此外,我在 XMLNAMESPACES
和 XML nodes
/value
調(diào)用中明確定義了您的命名空間:
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);
這篇關(guān)于SQL Server 查詢(xún)從兩個(gè)不同節(jié)點(diǎn)從 SOAP 1.1 中提取數(shù)據(jù)的文章就介紹到這了,希望我們推薦的答案對(duì)大家有所幫助,也希望大家多多支持html5模板網(wǎng)!