I'm fairly new to parsing XML within Oracle and am struggling to correctly write an XMLTABLE query which can parse the response XML from a SOAP webservice. I've read through some documentation, but nothing seems to cover the sort of mess I'm working with below.
If there is anyone who has experience dealing with this kind of SOAP response, any assistance would be greatly appreciated. I suspect I am not declaring the namespaces properly.
Here is the command I have so far, which is nowhere near functioning:
select *
from XMLTABLE(xmlnamespaces(
'http://www.w3.org/2003/05/soap-envelope/' as "soapenv"
,'http://<server>/webservice/soap/server.php?wstoken=LONGENCRYPTEDTOKEN' as "ns1"
,'http://xml.apache.org/xml-soap' as "ns2"
,'http://www.w3.org/2003/05/soap-encoding' as "enc"
,'http://www.w3.org/2003/05/soap-rpc' as "rpc"
),
'/soapenv:Envelope/soapenv:Body/ns1:moodle_course_create_coursesResponse/rpc:result/ns2:item'
PASSING ('XML SHOWN BELOW')
COLUMNS
ID VARCHAR2(10) PATH '???????') a
Here is the sanitized XML:
<?xml version="1.0" encoding="UTF-8"?>
<env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope"
xmlns:ns1="http://<server>/webservice/soap/server.php?wstoken=LONGENCRYPTEDTOKEN"
xmlns:ns2="http://xml.apache.org/xml-soap"
xmlns:enc="http://www.w3.org/2003/05/soap-encoding"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<env:Body xmlns:rpc="http://www.w3.org/2003/05/soap-rpc">
<ns1:moodle_course_create_coursesResponse env:encodingStyle="http://www.w3.org/2003/05/soap-encoding">
<rpc:result>return</rpc:result>
<return enc:itemType="ns2:Map" enc:arraySize="2" xsi:type="enc:Array">
<item xsi:type="ns2:Map">
<item>
<key xsi:type="xsd:string">id</key>
<value xsi:type="xsd:int">14756</value>
</item>
<item>
<key xsi:type="xsd:string">shortname</key>
<value xsi:type="xsd:string">testname5</value>
</item>
</item>
<item xsi:type="ns2:Map">
<item>
<key xsi:type="xsd:string">id</key>
<value xsi:type="xsd:int">14757</value>
</item>
<item>
<key xsi:type="xsd:string">shortname</key>
<value xsi:type="xsd:string">testname6</value>
</item>
</item>
</return>
</ns1:moodle_course_create_coursesResponse>
</env:Body>
</env:Envelope>