This discussion is archived
3 Replies Latest reply: Aug 29, 2013 9:27 PM by DAS_NH RSS

Need assistance creating an XMLTABLE query

829470 Newbie
Currently Being Moderated
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>
  • 1. Re: Need assistance creating an XMLTABLE query
    odie_63 Guru
    Currently Being Moderated
    Hi,

    SOAP-RPC is tricky, the name of the resultset is given in a separate element.

    Here, you'll need to do this :
    SQL> var xmlresp clob
    SQL> BEGIN
      2   :xmlresp :=
      3  '<?xml version="1.0" encoding="UTF-8"?>
      4  <env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope"
      5                xmlns:ns1="http://server/webservice/soap/server.php?wstoken=LONGENCRYPTEDTOKEN"
      6                xmlns:ns2="http://xml.apache.org/xml-soap"
      7                xmlns:enc="http://www.w3.org/2003/05/soap-encoding"
      8                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      9                xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     10      <env:Body xmlns:rpc="http://www.w3.org/2003/05/soap-rpc">
     11          <ns1:moodle_course_create_coursesResponse env:encodingStyle="http://www.w3.org/2003/05/soap-encoding">
     12              <rpc:result>return</rpc:result>
     13              <return enc:itemType="ns2:Map" enc:arraySize="2" xsi:type="enc:Array">
     14                  <item xsi:type="ns2:Map">
     15                      <item>
     16                          <key xsi:type="xsd:string">id</key>
     17                          <value xsi:type="xsd:int">14756</value>
     18                      </item>
     19                      <item>
     20                          <key xsi:type="xsd:string">shortname</key>
     21                          <value xsi:type="xsd:string">testname5</value>
     22                      </item>
     23                  </item>
     24                  <item xsi:type="ns2:Map">
     25                      <item>
     26                          <key xsi:type="xsd:string">id</key>
     27                          <value xsi:type="xsd:int">14757</value>
     28                      </item>
     29                      <item>
     30                          <key xsi:type="xsd:string">shortname</key>
     31                          <value xsi:type="xsd:string">testname6</value>
     32                      </item>
     33                  </item>
     34              </return>
     35          </ns1:moodle_course_create_coursesResponse>
     36      </env:Body>
     37  </env:Envelope>';
     38  END;
     39  /
    
    PL/SQL procedure successfully completed.
    
    SQL> SELECT x.*
      2  FROM XMLTable(
      3         XMLNamespaces(
      4           'http://www.w3.org/2003/05/soap-envelope' as "soapenv"
      5         , 'http://server/webservice/soap/server.php?wstoken=LONGENCRYPTEDTOKEN' as "ns1"
      6         , 'http://xml.apache.org/xml-soap' as "ns2"
      7         , 'http://www.w3.org/2003/05/soap-encoding' as "enc"
      8         , 'http://www.w3.org/2003/05/soap-rpc' as "rpc"
      9         )
     10       , 'for $i in /soapenv:Envelope/soapenv:Body/ns1:moodle_course_create_coursesResponse
     11          return $i/*[name()=$i/rpc:result]/item'
     12          PASSING xmltype(:xmlresp)
     13          COLUMNS id        VARCHAR2(10) PATH 'item[key="id"]/value'
     14                , shortname VARCHAR2(30) PATH 'item[key="shortname"]/value'
     15       ) x
     16  ;
    
    ID         SHORTNAME
    ---------- ------------------------------
    14756      testname5
    14757      testname6
    BTW, you don't have to declare every single namespace from the document, just the one(s) you intend to use in the statement.
  • 2. Re: Need assistance creating an XMLTABLE query
    829470 Newbie
    Currently Being Moderated
    Brilliant!

    It makes so much sense seeing a clear example. I wouldn't have ever thought to use the 'for' syntax.

    Thanks so much!
  • 3. Re: Need assistance creating an XMLTABLE query
    DAS_NH Newbie
    Currently Being Moderated

    Thank you very much.

     

    I was searching solution for similar kind of issue and this one helped me to find the fix.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points