title: Using XMLTABLE to parse CDATA section of SOAP
body: <p>I'm following the example in this <a href="https://community.oracle.com/thread/3788694?start=0&amp;tstart=0" rel="nofollow">thread</a> to learn how to parse the CDATA section of a SOAP response using XMLTABLE. Database is Oracle Database 11g Enterprise Edition 11.2.0.4.0.</p>
  
  <p>I modified the SOAP response that I need to parse just so I could have a query that works, similar to the example I was looking at.</p>
  
  <p>So this is what is working ok, albeit with a slightly simplified response envelope:</p>
  
  <pre><code>CREATE TABLE xml_tab (xml_data xmltype);
  
  DECLARE l
  l_xmltype xmltype;
  
  BEGIN
  
  SELECT xmltype('&lt;soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:sawsoap="urn://oracle.bi.webservices/v6"&gt;  
  &lt;soap:Body&gt;    
  &lt;sawsoap:executeSQLQueryResult&gt;      
  &lt;sawsoap:return xsi:type="sawsoap:QueryResults"&gt;        
  &lt;sawsoap:rowset&gt;&lt;![CDATA[&lt;Data&gt;&lt;Row&gt;&lt;Column0&gt;1200&lt;/Column0&gt;&lt;Column1&gt;East Region&lt;/Column1&gt;&lt;/Row&gt;&lt;Row&gt;&lt;Column0&gt;3000&lt;/Column0&gt;&lt;Column1&gt;West Region&lt;/Column1&gt;&lt;/Row&gt;&lt;/Data&gt;]]&gt;&lt;/sawsoap:rowset&gt;        
  &lt;sawsoap:queryID/&gt;        
  &lt;sawsoap:finished&gt;true&lt;/sawsoap:finished&gt;      
  &lt;/sawsoap:return&gt;    
  &lt;/sawsoap:executeSQLQueryResult&gt;  
  &lt;/soap:Body&gt;
  &lt;/soap:Envelope&gt;') INTO l_xmltype FROM dual ;
  
  INSERT INTO xml_tab VALUES(l_xmltype);
  
  END;
  </code></pre>
  
  <p>And this query returns the results I want:</p>
  
  <pre><code>SELECT B2.*  
     FROM 
     xml_tab x,
     XMLTable(  
           XMLNamespaces(  
             'http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP-ENV"  
              ,'urn://oracle.bi.webservices/v6' AS  "sawsoap"                
            )  
          , 'SOAP-ENV:Envelope/SOAP-ENV:Body/sawsoap:executeSQLQueryResult/sawsoap:return/sawsoap:rowset'  
            passing    x.XML_DATA 
            columns Row1 clob path '.'  
         ) A1,
          XMLTable(  
           '/Data/Row'  
           passing xmlparse(document A1.Row1)  
           columns    
           Amount number PATH 'Column0',
           Region varchar2(60) PATH 'Column1'
         ) B2;
  
  
  
  AMOUNT REGION                                                     
    ---------- ------------------------------------------------------------
    1200 East Region                                                  
    3000 West Region 
  </code></pre>
  
  <p>Unfortunately the actual SOAP response that I need to parse looks like this:</p>
  
  <pre><code>truncate table xml_tab;
  
   DECLARE
    l_xmltype xmltype;
  BEGIN
    SELECT xmltype('&lt;soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:sawsoap="urn://oracle.bi.webservices/v6"&gt;
    &lt;soap:Body&gt;
      &lt;sawsoap:executeSQLQueryResult&gt;
        &lt;sawsoap:return xsi:type="sawsoap:QueryResults"&gt;
          &lt;sawsoap:rowset&gt;&lt;![CDATA[&lt;rowset xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" &gt;&lt;Row&gt;&lt;Column0&gt;01200&lt;/Column0&gt;&lt;Column1&gt;East Region&lt;/Column1&gt;&lt;/Row&gt;&lt;Row&gt;&lt;Column0&gt;3000&lt;/Column0&gt;&lt;Column1&gt;West Region&lt;/Column1&gt;&lt;/Row&gt;&lt;/rowset&gt;]]&gt;&lt;/sawsoap:rowset&gt;
          &lt;sawsoap:queryID/&gt;
          &lt;sawsoap:finished&gt;true&lt;/sawsoap:finished&gt;
        &lt;/sawsoap:return&gt;
      &lt;/sawsoap:executeSQLQueryResult&gt;
    &lt;/soap:Body&gt;
  &lt;/soap:Envelope&gt;')
    INTO l_xmltype
    FROM dual ;
    INSERT INTO xml_tab VALUES
      (l_xmltype
      );
  END;
  </code></pre>
  
  <p>Because of this bit, rowset xmlns="urn:schemas-microsoft-com:xml-analysis:rowset", my code no longer works and I don't know how to get past this. If anyone can suggest modifications to my query that will successfully parse this SOAP response I would greatly appreciate the help.</p>