我有一个返回以下XML的存储过程:
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP-ENV:Header />
<SOAP-ENV:Body>
<ns2:ReportResponse>
<ns2:responseTitle />
<ns2:responseBody>
<ns2:resultRow>
<ns2:result Name="country" Value="United Kingdom" />
<ns2:result Name="code" Value="7360" />
</ns2:resultRow>
<ns2:resultRow>
<ns2:result Name="country" Value="France" />
<ns2:result Name="code" Value="7340" />
</ns2:resultRow>
</ns2:responseBody>
</ns2:ReportResponse>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
我希望能够将 2 条记录保存在表中,如何获取循环以获取数据?
记录 1: 国家=英国 代码=7360
记录 2: 国家=法国 代码=7340
我尝试使用此选择,但它没有返回任何内容。
SELECT
Record.value('@Name','VARCHAR')
FROM @XmlResponse.nodes('/Envelope/Body/ReportResponse/responseBody/resultRow')AS TEMPTABLE(Record)
谢谢。
像这样:
declare @doc xml = '
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP-ENV:Header />
<SOAP-ENV:Body>
<ns2:ReportResponse xmlns:ns2="http://whatever">
<ns2:responseTitle />
<ns2:responseBody>
<ns2:resultRow>
<ns2:result Name="country" Value="United Kingdom" />
<ns2:result Name="code" Value="7360" />
</ns2:resultRow>
<ns2:resultRow>
<ns2:result Name="country" Value="France" />
<ns2:result Name="code" Value="7340" />
</ns2:resultRow>
</ns2:responseBody>
</ns2:ReportResponse>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
';
WITH XMLNAMESPACES ('http://schemas.xmlsoap.org/soap/envelope/' as soap ,
'http://whatever' as ns2)
SELECT
Record.value('(ns2:result[@Name="country"])[1]/@Value','VARCHAR(20)') Country,
Record.value('(ns2:result[@Name="code"])[1]/@Value','int') Code
FROM @doc.nodes('/soap:Envelope/soap:Body/ns2:ReportResponse/ns2:responseBody/ns2:resultRow')AS TEMPTABLE(Record)
输出
Country Code
-------------------- -----------
United Kingdom 7360
France 7340
(2 rows affected)