Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need assistance creating an XMLTABLE query

829470Dec 15 2011 — edited Aug 30 2013
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>
This post has been answered by odie_63 on Dec 15 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 27 2013
Added on Dec 15 2011
3 comments
3,115 views