Parsing SOAP XML in Oracle

Question

Hi

I am new to Oracle and I am working on something that needs to parse a SOAP request and save the address to DB Tables. I am using the XML parser in Oracle (XMLType) with XPath but am struggling since I can't figure out the way to parse the SOAP request because it has multiple namespaces.

Could anyone give me an example?

Thanks in advance!!!

edit

It would be a typical SOAP request similar to the one below.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"        
                  xmlns:soap="http://soap.service.****.com"&gt; 
  <soapenv:Header /> 
  <soapenv:Body> 
    <soap:UpdateElem> 
      <soap:request> 
        <soap:att1>123456789</soap:att1> 
        <soap:att2 xsi:nil="true" 
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />                       
        <soap:att3>L</soap:att3> 
        ..... 
      </soap:request> 
    </soap:UpdateElem>
  </soapenv:Body> 
</soapenv:Envelope>

I need to retrieve parameters att1, att2... and save them in to a DB table.

Answer 1

With Oracle's XML implementation there may several ways to shred a particular cabbage. Here is a solution which uses XMLTable() to isolate the attributes.

I have put your SOAP message into a table with an XMLType column:

SQL> set long 5000
SQL> select xt.xmlmsg
  2  from xt
  3  where id = 1
  4  /

XMLMSG
--------------------------------------------------------------------------------
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
                                  xmlns:soap="http://soap.service.****.com"&gt;
  <soapenv:Header />
  <soapenv:Body>
    <soap:UpdateElem>
      <soap:request>
        <soap:att1>123456789</soap:att1>
        <soap:att2 xsi:nil="true"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
        <soap:att3>L</soap:att3>
        .....
      </soap:request>
    </soap:UpdateElem>
  </soapenv:Body>
</soapenv:Envelope>
SQL>

This SELECT uses XPath to extract the attributes as columns. Note the use of the XMLTable() XMLNAMESPACES clause to define the, er, namespaces.

SQL> select
  2      atts.att1
  3      , atts.att2
  4      , atts.att3
  5  from xt
  6       , xmltable(
  7              xmlnamespaces(
  8                  'http://schemas.xmlsoap.org/soap/envelope/' as "soapenv"
  9                     , 'http://soap.service.****.com' as "soap"
 10                     , 'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
 11                          )
 12               , '/soapenv:Envelope/soapenv:Body/soap:UpdateElem/soap:request'
 13               passing xt.xmlmsg
 14               columns
 15                   att1 number path 'soap:att1'
 16                   , att2 varchar2(10) path 'soap:att2/@xsi:nil'
 17                   , att3 char(1) path 'soap:att3'
 18                   ) atts
 19  where xt.id = 1
 20  /

      ATT1 ATT2       A
---------- ---------- -
 123456789 true       L

SQL>