使用示例在 Oracle 中解析 SOAP XML [英] Parse SOAP XML in Oracle with example
问题描述
以下是表external"中的典型 SOAP 请求.
Below is a typical SOAP request in table 'external'.
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soapenv:Body>
<settleResponse xmlns="urn:ABC">
<settleReturn xmlns="">
<message>Missing first name</message>
<errorCode>INVALID_ACC</errorCode>
<customData>offendingTransactionID=12345678</customData>
<divisionRequestID xsi:nil="true"/>
<status>Failed</status>
</settleReturn>
</settleResponse>
</soapenv:Body>
</soapenv:Envelope>
我需要检索参数 errorCode、status... 并将它们保存到数据库表中.我该怎么做?
I need to retrieve parameters errorCode, status... and save them in to a DB table. How can I do that?
推荐答案
您可以使用 XMLQUERY
提取节点内容:
You could extract the node contents with XMLQUERY
:
select xmlquery('declare namespace soapenv = "http://schemas.xmlsoap.org/soap/envelope/";
declare namespace urn = "urn:ABC";
/soapenv:Envelope/soapenv:Body/urn:settleResponse/settleReturn/message/text()'
passing XMLType(message)
returning content) as message,
xmlquery('declare namespace soapenv = "http://schemas.xmlsoap.org/soap/envelope/";
declare namespace urn = "urn:ABC";
/soapenv:Envelope/soapenv:Body/urn:settleResponse/settleReturn/errorCode/text()'
passing XMLType(message)
returning content) as errorCode,
xmlquery('declare namespace soapenv = "http://schemas.xmlsoap.org/soap/envelope/";
declare namespace urn = "urn:ABC";
/soapenv:Envelope/soapenv:Body/urn:settleResponse/settleReturn/status/text()'
passing XMLType(message)
returning content) as status
from external;
MESSAGE ERRORCODE STATUS
-------------------- -------------------- ----------
Missing first name INVALID_ACC Failed
或者更简单,特别是当您有多个消息要处理时,使用 XMLTABLE
:
Or perhaps more simply, particularly if you have multiple messages to handle, with XMLTABLE
:
select x.*
from external ext
cross join xmltable(
xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as "soapenv",
'urn:ABC' as "urn"),
'/soapenv:Envelope/soapenv:Body/urn:settleResponse/settleReturn'
passing XMLType(ext.message)
columns message varchar2(20) path 'message',
errorCode varchar2(20) path 'errorCode',
status varchar2(10) path 'status'
) x;
MESSAGE ERRORCODE STATUS
-------------------- -------------------- ----------
Missing first name INVALID_ACC Failed
这两种情况都需要指定命名空间,而且语法不同.阅读有关使用这些函数的更多信息.
In both cases you need to specify the namespaces, and the syntax is different. Read more about using these functions.
您可以使用 insert into some_table (x, y, z) select ...
直接将这些插入到另一个表中.
You can insert those directly into another table with insert into some_table (x, y, z) select ...
.
这篇关于使用示例在 Oracle 中解析 SOAP XML的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!