您可以将 CLOB 转换为 XMLType(假设它有效),只需使用:
extractvalue(XMLType(RESPONSE_XML), ...
如果您在其中存储 XML,则不确定为什么您的列类型不是 XMLType,但这并不完全相关。
然后您可以将命名空间提供给extractvalue()
:
SELECT extractvalue(XMLType(RESPONSE_XML),
'//ax2130:id/text()',
'xmlns:ax2130="http://core.data.soap.CDRator.com/xsd"')
FROM SOAP_MONITORING
where WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder';
..但是你有多个ID,所以你得到:ORA-19025: EXTRACTVALUE returns value of only one node
.
And extractvalue
已弃用,如文档中所述
您可以使用XQuery相反,这里具体是一个 XMLTable。
假设你只想要ax2130:id
嵌套在里面的值ax2147:subscription
,您可以使用此 XQuery:
SELECT xt.id
FROM SOAP_MONITORING sm
CROSS JOIN XMLTable(XMLNAMESPACES (
'http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
'http://service.soap.CDRator.com' as "ns",
'http://core.data.soap.CDRator.com/xsd' as "ax2130",
'http://webshop.result.service.soap.CDRator.com/xsd' as "ax2147"
),
'for $i in /soapenv:Envelope/soapenv:Body/ns:placeShopOrderResponse/ns:return/ax2147:subscriptions
return $i/ax2130:id'
passing XMLType(sm.RESPONSE_XML)
columns "ID" number path '/') xt
where WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder';
ID
---------------------
201501070917439804
201501070917439804
2 rows selected
或者如果你想要任何ax:2130
任何地方的节点,包括空白节点,都可以使用:
SELECT xt.id
FROM SOAP_MONITORING sm
CROSS JOIN XMLTable(XMLNAMESPACES (
'http://core.data.soap.CDRator.com/xsd' as "ax2130"
),
'for $i in //ax2130:id return $i'
passing XMLType(sm.RESPONSE_XML)
columns "ID" number path '/') xt
where WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder';
ID
---------------------
201501070917439804
201501070917439804
3 rows selected
只需在 XMLNamespaces 子句中指定 XQuery 中引用的名称空间。
如果需要,您可以根据所选 ID 连接到另一个表:
SELECT xt.id
FROM SOAP_MONITORING sm
CROSS JOIN XMLTable(XMLNAMESPACES (
...) xt
JOIN someothertable sot on sot.id = xt.id
where sm.WEB_SERVICE_NAME='RatorWebShopService'
and sm.WEB_METHOD_NAME='placeShopOrder';