title: Parsing Large XML (>4k) in PL/SQL
body: <p>I am getting an xml of length around 30k. I have to extract key and value pairs from this xml and insert into a table. Also have to avoid from duplicated rows in table. Here is the query I came up with:</p>
  
  <pre><code>DECLARE
    PARSER XMLPARSER.PARSER;
    XMLDOC XMLDOM.DOMDOCUMENT;
    NODELIST XMLDOM.DOMNODELIST;
    REFDATA VARCHAR2(32767);
    REFDATAPART VARCHAR2(32767);
    NODELENGTH NUMBER;
  </code></pre>
  
  <p>BEGIN</p>
  
  <pre><code>  REFDATA := '&lt;ReferenceFields&gt;&lt;ReferenceField&gt;&lt;FieldKey&gt;Name1&lt;/FieldKey&gt;                  &lt;FieldValue&gt;ABCD&lt;/FieldValue&gt;&lt;/ReferenceField&gt;&lt;ReferenceField&gt;&lt;FieldKey&gt;Name1&lt;/FieldKey&gt;&lt;FieldValue&gt;ABCD&lt;/FieldValue&gt;&lt;/ReferenceField&gt;&lt;/ReferenceFields&gt;';
    PARSER := XMLPARSER.NEWPARSER;
    XMLPARSER.PARSEBUFFER(PARSER,REFDATA);
    XMLDOC := XMLPARSER.GETDOCUMENT(PARSER);
    NODELIST := XMLDOM.GETELEMENTSBYTAGNAME(XMLDOC, 'ReferenceField');
    NODELENGTH := XMLDOM.GETLENGTH(NODELIST);
    FOR i IN 0..NODELENGTH-1 LOOP
  
      XMLDOM.WRITETOBUFFER(XMLDOM.ITEM(NODELIST, i),REFDATAPART);
      INSERT INTO 
          HTS_TRANSACTION_XREF(TRANS_ID,XREF_FIELD, XREF_VALUE) 
      SELECT 
          '1',
          EXTRACTVALUE(COLUMN_VALUE, '/ReferenceField/FieldKey') "FIELDKEY",
          EXTRACTVALUE(COLUMN_VALUE, '/ReferenceField/FieldValue') "FIELDVALUE"
      FROM 
          TABLE(XMLSequence(XMLTYPE(REFDATAPART))) REFDATA
      WHERE 
          NOT EXISTS (SELECT 1 from TRANSACTION_CROSSREFERENCE WHERE TRANS_ID='1' AND XREF_FIELD=EXTRACTVALUE(column_value, '/ReferenceField/FieldKey') AND XREF_VALUE=EXTRACTVALUE(column_value, '/ReferenceField/FieldValue'));
  
    END LOOP;
  
  XMLPARSER.FREEPARSER(PARSER);
  </code></pre>
  
  <p>END;</p>
  
  <p>This query works fine and finally would be part of sproc. </p>
  
  <p>Here I have two questions:</p>
  
  <p>1- Is this the right way to process large xml of maximum size 32k.</p>
  
  <p>2- Will this be efficient enough to handle 700 calls in a second?</p>
  
  <p>Thanks,
  Attiq</p>