Using Oracle PL SQL to bulk load XML

In performance testing it is often necessary to do bulk loads of data into databases via XML payloads, to prepare for a test. For my example I had to load 250,000 elements via XML to create accounts. The XML structure had one root or top level element with many child elements, also many batches of the root elements. I decided I would perform a commit after constructing each root element, with the corresponding amounts of child elements. I made both the batch number of root elements and the child elements both configurable. This resulted in the following PL SQL code. I have deleted out certain parts of the XML so as to protect client.

create or replace
PROCEDURE XML_LOADER AS

l_payload clob;

chProgramID CONSTANT varchar(7) NOT NULL   := 'MMMMM';

iCount                    number(6) NOT NULL := 0;
iCountBatch           number(6) NOT NULL := 0;
iNumbersBatch       number(6) NOT NULL := 5;                          
iNumberOfBatches  number(6) NOT NULL := 5;                          
chXmlRoot               clob;
chXmlRootEnd       varchar2(32767);
chXml                    varchar2(32767);
l_offset                number default 1;
iID               	    number(6) NOT NULL := '000000';

BEGIN
DBMS_OUTPUT.ENABLE(1000000);

WHILE iCountBatch < iNumberOfBatches LOOP
  chXmlRoot := '(<?xml version="1.0" encoding="UTF-8"?>
  <iee:ConfigurationBatch..The rest of your root element xml here';
  chXmlRootEnd :='</iee:ConfigurationTransfers>
  </iee:ConfigurationBatch>)';

  WHILE iCount < iNumbersBatch LOOP
        iID := iID + 1;
        chXml := '<com:ID>your child element xml'||TO_CHAR(iID)||'</com:ID>'
      chXmlRoot := chXmlRoot||chXml;
      iCount := iCount + 1;
  END LOOP;

  chXmlRoot := chXmlRoot||chXmlRootEnd;
  l_payload := chXmlRoot;

  /* Print clob contents */
  LOOP
  exit when l_offset > dbms_lob.getlength(l_payload);
    dbms_output.put_line( dbms_lob.substr( l_payload, 255, l_offset ) );
    l_offset := l_offset + 255;
  END LOOP;

  /* We have completed generating  batch. Lets commit and cleanup for next loop */
  /* Commit code here */
  dbms_output.put_line('Commit');
  /*pkg_mts_xml_queue.pro_EnQueue(p_queue_name => 'SQ_INTERFACE_IN', p_xml_payload => l_payload, p_corrid => 'nsingh.20091417.1');
  commit; */

  l_offset := 1;
  iCount :=0;
  iCountBatch := iCountBatch + 1;

END LOOP; 

END XML_LOADER;

One thought on “Using Oracle PL SQL to bulk load XML

Comments are closed.