PL\SQL XMLTable performance

Question

I have a function that parses a XMLTYPE variable, and for each message, append a CLOB with a specific structure for each tag in the XMLTYPE variable. Like this:

FUNCTION myFunc (px_Header      IN     VARCHAR2,
                                px_Block       IN     XMLTYPE,
                                pn_numLines      OUT PLS_INTEGER)
      RETURN CLOB
   IS
      lcl_return   CLOB := EMPTY_CLOB;
   BEGIN
      pn_numLines := 0;

      FOR item
         IN (          SELECT RPAD (NVL (RECEIPTNUMBER, ' '), 20) AS RECEIPTNUMBER,
                              RPAD (NVL (COMPANYCODE, ' '), 3) AS COMPANYCODE,
                              RPAD (NVL (BRAND, ' '), 3) AS BRAND,
                              RPAD (NVL (POLICYNUMBER, ' '), 20) AS POLICYNUMBER,
                              RPAD (NVL (CLAIMNUMBER, ' '), 20) AS CLAIMNUMBER,
                              RECEIPTAMOUNT
                                 AS receiptAmount
                         FROM XMLTABLE (
                                 'INT_DATA/Item'
                                 PASSING px_Block
                                 COLUMNS RECEIPTNUMBER   VARCHAR2 (20)
                                                            PATH 'RECEIPTNUMBER',
                                         COMPANYCODE     VARCHAR2 (3)
                                                            PATH 'COMPANYCODE',
                                         BRAND           VARCHAR2 (3) PATH 'BRAND',
                                         POLICYNUMBER    VARCHAR2 (20)
                                                            PATH 'POLICYNUMBER',
                                         CLAIMNUMBER     VARCHAR2 (20)
                                                            PATH 'CLAIMNUMBER',
                                         RECEIPTAMOUNT   VARCHAR2 (15)
                                                            PATH 'RECEIPTAMOUNT'))
      LOOP
         lcl_return:=
               lcl_return
            || px_Header
            || 'B2'
            || item.RECEIPTNUMBER
            || item.COMPANYCODE
            || item.BRAND
            || item.POLICYNUMBER
            || item.CLAIMNUMBER
            || item.RECEIPTAMOUNT
            || CHR (13)
            || CHR (10);
         pn_numLines := pn_numLines + 1;
      END LOOP;

      RETURN lcl_return;
   END myFunc ;

This works well if I have a small px_Block. But I have a case that I can have a big XMLTYPE and this function takes a long time. I'm new using XMLType and XMLTable. Is there something that I can do to improve performance. Maybe using a BULK COLLECT statement?

Thanks in advance, Filipe

EDIT1: Here's an example of the XML only for two instances.

<INT_DATA>
 <Item>
  <RECEIPTNUMBER>1</RECEIPTNUMBER>
  <COMPANYCODE>148</COMPANYCODE>
  <BRAND>006</BRAND>
  <POLICYNUMBER>72972</POLICYNUMBER>
  <CLAIMNUMBER>2015101504</CLAIMNUMBER>
  <RECEIPTAMOUNT>-10.00</RECEIPTAMOUNT>
 </Item>
 <Item>
  <RECEIPTNUMBER>1</RECEIPTNUMBER>
  <COMPANYCODE>148</COMPANYCODE>
  <BRAND>006</BRAND>
  <POLICYNUMBER>73785</POLICYNUMBER>
  <CLAIMNUMBER>2015101505</CLAIMNUMBER>
  <RECEIPTAMOUNT>-22.50</RECEIPTAMOUNT>
 </Item>
</INT_DATA>

EDIT2: I've made some changes in my function with a 20% better improvements. I've changed the way I append the CLOB using a temporary CLOB. But a better solutions will be helpful.

FUNCTION myFunc (px_Header      IN     VARCHAR2,
                                px_Block       IN     XMLTYPE,
                                pn_numLines      OUT PLS_INTEGER)
      RETURN CLOB
   IS
      lcl_return   CLOB := EMPTY_CLOB;
      v_tmp_clob    CLOB := EMPTY_CLOB;
   BEGIN
      pn_numLines := 0;

      FOR item
         IN (          SELECT RPAD (NVL (RECEIPTNUMBER, ' '), 20) AS RECEIPTNUMBER,
                              RPAD (NVL (COMPANYCODE, ' '), 3) AS COMPANYCODE,
                              RPAD (NVL (BRAND, ' '), 3) AS BRAND,
                              RPAD (NVL (POLICYNUMBER, ' '), 20) AS POLICYNUMBER,
                              RPAD (NVL (CLAIMNUMBER, ' '), 20) AS CLAIMNUMBER,
                              RECEIPTAMOUNT
                                 AS receiptAmount
                         FROM XMLTABLE (
                                 'INT_DATA/Item'
                                 PASSING px_Block
                                 COLUMNS RECEIPTNUMBER   VARCHAR2 (20)
                                                            PATH 'RECEIPTNUMBER',
                                         COMPANYCODE     VARCHAR2 (3)
                                                            PATH 'COMPANYCODE',
                                         BRAND           VARCHAR2 (3) PATH 'BRAND',
                                         POLICYNUMBER    VARCHAR2 (20)
                                                            PATH 'POLICYNUMBER',
                                         CLAIMNUMBER     VARCHAR2 (20)
                                                            PATH 'CLAIMNUMBER',
                                         RECEIPTAMOUNT   VARCHAR2 (15)
                                                            PATH 'RECEIPTAMOUNT'))
      LOOP
         v_tmp_clob :=
           TO_CLOB (px_Header)
        || TO_CLOB ('B2')
        || TO_CLOB (item.RECEIPTNUMBER)
        || TO_CLOB (item.COMPANYCODE)
        || TO_CLOB (item.BRAND)
        || TO_CLOB (item.POLICYNUMBER)
        || TO_CLOB (item.CLAIMNUMBER)
        || TO_CLOB (item.RECEIPTAMOUNT)
        || CHR (13)
        || CHR (10);

     lcl_return := lcl_return || v_tmp_clob;

         pn_numLines := pn_numLines + 1;
      END LOOP;

      RETURN lcl_return;
   END myFunc ;

Answer 1

The problem with XMLTABLE is that Oracle uses a DOM Parser to read the XML. It means the entire XML has to be loaded into memory with a lot of overhead. Have a look at DBMS_XMLSTORE. This package uses a SAX Parser which is event bases (or stream based).

Using a SAX Parser you can read XML files having a size of several Gigabytes with an ordinary PC.

However, until - let's say 50-100 MByte - a DOM Parser should work well.

Answer 2

Since your XML represent a simple table structure you could use dbms_xmlsave to store the XML data in a table.

First create a table that matches the XML data.

-- Create table
create table TEST
(
  receiptnumber NUMBER,
  companycode   NUMBER,
  brand         VARCHAR2(10),
  policynumber  NUMBER,
  claimnumber   NUMBER,
  receiptamount NUMBER
)

Then use dbms_xmlsave to store the data in a table.

declare
   l_clob   clob;
   l_rows  number;
   l_insctx dbms_xmlsave.ctxtype;
begin
   l_clob := '<INT_DATA>
 <Item>
  <RECEIPTNUMBER>1</RECEIPTNUMBER>
  <COMPANYCODE>148</COMPANYCODE>
  <BRAND>006</BRAND>
  <POLICYNUMBER>72972</POLICYNUMBER>
  <CLAIMNUMBER>2015101504</CLAIMNUMBER>
  <RECEIPTAMOUNT>-10.00</RECEIPTAMOUNT>
 </Item>
 <Item>
  <RECEIPTNUMBER>1</RECEIPTNUMBER>
  <COMPANYCODE>148</COMPANYCODE>
  <BRAND>006</BRAND>
  <POLICYNUMBER>73785</POLICYNUMBER>
  <CLAIMNUMBER>2015101505</CLAIMNUMBER>
  <RECEIPTAMOUNT>-22.50</RECEIPTAMOUNT>
 </Item>
</INT_DATA>';

   l_insctx := dbms_xmlsave.newcontext('TEST');
   dbms_xmlsave.setrowtag(l_insctx, 'Item');
   l_rows := dbms_xmlsave.insertxml(l_insctx, l_clob);
   dbms_xmlsave.closecontext(l_insctx);
end;

Maybe this performs better.