Extract xml content of CDATA Using Stylesheets in Oracle - useful in Apex Applications

This code will extract XML elements in CDATA section of XML file using stylesheet.

DECLARE
  P_XML XMLTYPE;
  P_STYLESHEET XMLTYPE;
  v_Return XMLTYPE;
  v_test CLOB;
BEGIN
  P_XML := xmltype(
  '<log>
<details> 
<![CDATA[
<inputVariable><part name="request" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance%22%3E%3Cns1:CreateInvoiceRequest xmlns:ns1="urn:au:gov:vic:sbs:services:ar:create:1"><ns1:ConsumerInformation><ns1:OrgId>DPI</ns1:OrgId><ns1:ContentSource>RRAM</ns1:ContentSource></ns1:ConsumerInformation><ns1:Invoice><ns2:ExternalCustomerReference xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">321831</ns2:ExternalCustomerReference><ns2:OracleCustomerReference xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">4192364</ns2:OracleCustomerReference><ns2:TransactionType xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">FV - Application</ns2:TransactionType><ns2:ExternalTransactionReference xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">90000134</ns2:ExternalTransactionReference><ns2:TransactionDate xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">2012-03-23</ns2:TransactionDate><ns2:SalesPerson xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">Commercial Fishing Licences</ns2:SalesPerson><ns2:InvoiceCurrenyCode xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">AUD</ns2:InvoiceCurrenyCode><ns2:PaymentTerms xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">7 DAYS</ns2:PaymentTerms><ns2:PaymentMethod xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">BP</ns2:PaymentMethod><ns2:SendToMailhouse xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">N</ns2:SendToMailhouse><ns2:LineItems xmlns:ns2="urn:au:gov:vic:sbs:transaction:1"><ns2:LineItemId>10</ns2:LineItemId><ns2:LineType>LINE</ns2:LineType><ns2:MemoLine>Commercial Fishing</ns2:MemoLine><ns2:Description>Aqua (CL - Eels) Licence</ns2:Description><ns2:Quantity>1.0</ns2:Quantity><ns2:Amount>1835.53</ns2:Amount><ns2:Taxed>0</ns2:Taxed><ns2:TaxRate>0</ns2:TaxRate><ns2:TaxAmount>0.0</ns2:TaxAmount></ns2:LineItems></ns1:Invoice></ns1:CreateInvoiceRequest></part></inputVariable>]]>
</details> 
<details> 
<![CDATA[
<inputVariable><part name="request" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance%22%3E%3Cns1:CreateInvoiceRequest xmlns:ns1="urn:au:gov:vic:sbs:services:ar:create:1"><ns1:ConsumerInformation><ns1:OrgId>DPI</ns1:OrgId><ns1:ContentSource>RRAM</ns1:ContentSource></ns1:ConsumerInformation><ns1:Invoice><ns2:ExternalCustomerReference xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">321831</ns2:ExternalCustomerReference><ns2:OracleCustomerReference xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">4192364</ns2:OracleCustomerReference><ns2:TransactionType xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">FV - Application</ns2:TransactionType><ns2:ExternalTransactionReference xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">90000134</ns2:ExternalTransactionReference><ns2:TransactionDate xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">2012-03-23</ns2:TransactionDate><ns2:SalesPerson xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">Commercial Fishing Licences</ns2:SalesPerson><ns2:InvoiceCurrenyCode xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">AUD</ns2:InvoiceCurrenyCode><ns2:PaymentTerms xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">7 DAYS</ns2:PaymentTerms><ns2:PaymentMethod xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">BP</ns2:PaymentMethod><ns2:SendToMailhouse xmlns:ns2="urn:au:gov:vic:sbs:transaction:1">N</ns2:SendToMailhouse><ns2:LineItems xmlns:ns2="urn:au:gov:vic:sbs:transaction:1"><ns2:LineItemId>10</ns2:LineItemId><ns2:LineType>LINE</ns2:LineType><ns2:MemoLine>Commercial Fishing</ns2:MemoLine><ns2:Description>Aqua (CL - Eels) Licence</ns2:Description><ns2:Quantity>1.0</ns2:Quantity><ns2:Amount>1835.53</ns2:Amount><ns2:Taxed>0</ns2:Taxed><ns2:TaxRate>0</ns2:TaxRate><ns2:TaxAmount>0.0</ns2:TaxAmount></ns2:LineItems></ns1:Invoice></ns1:CreateInvoiceRequest></part></inputVariable>]]>
</details>
</log>'
  );
  P_STYLESHEET := xmltype('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<log> 
<xsl:for-each select="//details/text()"> 
<sections>  
<xsl:value-of select="."/> 
</sections> 
</xsl:for-each>
</log>  
</xsl:template>
</xsl:stylesheet>
');
  v_Return     := p_xml.transform (p_stylesheet);
  dbms_output.put_line( REPLACE(REPLACE(REPLACE (v_Return.getClobVal(),'&lt;','<'),'&gt;','>'),'&quot;','"'));
END;