3 Replies Latest reply on Aug 30, 2013 4:27 AM by DAS_NH

    Need assistance creating an XMLTABLE query

      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"
          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"
                      <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">
                              <return enc:itemType="ns2:Map" enc:arraySize="2" xsi:type="enc:Array">
                                  <item xsi:type="ns2:Map">
                                          <key xsi:type="xsd:string">id</key>
                                          <value xsi:type="xsd:int">14756</value>
                                          <key xsi:type="xsd:string">shortname</key>
                                          <value xsi:type="xsd:string">testname5</value>
                                  <item xsi:type="ns2:Map">
                                          <key xsi:type="xsd:string">id</key>
                                          <value xsi:type="xsd:int">14757</value>
                                          <key xsi:type="xsd:string">shortname</key>
                                          <value xsi:type="xsd:string">testname6</value>
        • 1. Re: Need assistance creating an XMLTABLE query

          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

            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

              Thank you very much.


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