使用PL/SQL解析SOAP响应
问题描述:
我想使用下面的代码解析SOAP响应,但我得到一个空响应。该响应包含多个名称空间,我认为这是我遇到问题的地方。有人能指出我做错了什么吗?使用PL/SQL解析SOAP响应
declare
l_xml xmltype;
begin
l_xml := xmltype.createXML('<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://www.cachelan.com/WebAPI/solarvuDataRetrieve.php" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns2="http://xml.apache.org/xml-soap" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<ns1:getDataResponse>
<return xsi:type="ns2:Map">
<item>
<key xsi:type="xsd:string">status</key>
<value xsi:type="xsd:int">0</value>
</item>
<item>
<key xsi:type="xsd:string">content</key>
<value xsi:type="xsd:string">"SolarVu OM Data Request"
"From Mar 1, 2017 To Mar 4, 2017"
"Timestamp","Date","Daily Energy(kWh)","Insolation(Wh/m^2)"
"1488344400","Mar 1/17","131.064","1,105.750"
"1488430800","Mar 2/17","370.576","3,202.750"
"1488517200","Mar 3/17","517.566","4,662.500"
"1488603600","Mar 4/17","382.626","6,001.750"
</value>
</item>
</return>
</ns1:getDataResponse>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>');
for i in (SELECT t."key", t."value"
FROM dual d,
XMLTABLE(xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as "xsi",
'http://schemas.xmlsoap.org/soap/envelope/' as "SOAP-ENV",
'http://www.cachelan.com/WebAPI/solarvuDataRetrieve.php' as "ns1",
'http://www.w3.org/2001/XMLSchema' as "xsd",
'http://xml.apache.org/xml-soap' as "ns2",
'http://schemas.xmlsoap.org/soap/encoding/' as "SOAP-ENC"),
'/getDataResponse/return/item[*]'
PASSING l_xml
COLUMNS
"key" varchar2(4000) PATH 'key',
"value" varchar2(4000) PATH 'value') t)
loop
dbms_output.put_line('Key is: '||i."key");
end loop;
end;
/
答
你不与它在XPath命名空间前缀getDataResponse
,你也没有包括信封和身体标签;所以更改XPath来:
'/SOAP-ENV:Envelope/SOAP-ENV:Body/ns1:getDataResponse/return/item'
随着只是改变你的代码生成:
Key is: status
Key is: content
PL/SQL procedure successfully completed.
答
我有同样的问题。我发现问题出在<ns1:getDataResponse>
之类的前缀名称空间以及Oracle XMLType如何解释它们。我解决了它提取没有前缀命名空间的内部节点,基于你的例子这样。
declare
l_xml xmltype;
begin
l_xml := xmltype.createXML('<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://www.cachelan.com/WebAPI/solarvuDataRetrieve.php" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ns2="http://xml.apache.org/xml-soap" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<ns1:getDataResponse>
<return xsi:type="ns2:Map">
<item>
<key xsi:type="xsd:string">status</key>
<value xsi:type="xsd:int">0</value>
</item>
<item>
<key xsi:type="xsd:string">content</key>
<value xsi:type="xsd:string">"SolarVu OM Data Request"
"From Mar 1, 2017 To Mar 4, 2017"
"Timestamp","Date","Daily Energy(kWh)","Insolation(Wh/m^2)"
"1488344400","Mar 1/17","131.064","1,105.750"
"1488430800","Mar 2/17","370.576","3,202.750"
"1488517200","Mar 3/17","517.566","4,662.500"
"1488603600","Mar 4/17","382.626","6,001.750"
</value>
</item>
</return>
</ns1:getDataResponse>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>');
--Pay attention here and changes in path parameter at XMLTABLE
l_xml := l_xml.EXtract('//*/*//return');
for i in (select t.key, t.value
from dual d,
XMLTABLE('/return/item' PASSING l_xml COLUMNS key
varchar2(4000) PATH 'key',
value varchar2(4000) PATH 'value') t) loop
dbms_output.put_line('Key is: ' || i.key);
end loop;
end;
感谢Alex,我根据你的建议得到了这个工作。 – Steve