Parsing Large XML (>4k) in PL/SQL

Question

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:

DECLARE
  PARSER XMLPARSER.PARSER;
  XMLDOC XMLDOM.DOMDOCUMENT;
  NODELIST XMLDOM.DOMNODELIST;
  REFDATA VARCHAR2(32767);
  REFDATAPART VARCHAR2(32767);
  NODELENGTH NUMBER;

BEGIN

  REFDATA := '<ReferenceFields><ReferenceField><FieldKey>Name1</FieldKey>                  <FieldValue>ABCD</FieldValue></ReferenceField><ReferenceField><FieldKey>Name1</FieldKey><FieldValue>ABCD</FieldValue></ReferenceField></ReferenceFields>';
  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);

END;

This query works fine and finally would be part of sproc.

Here I have two questions:

1- Is this the right way to process large xml of maximum size 32k.

2- Will this be efficient enough to handle 700 calls in a second?

Thanks, Attiq

Answer 1

I think that you definitely want to gather the inserts at least. I don't know how big is NODELENGTH but you may use something similar to:

In your declare part:

TYPE T_STRING IS TABLE OF VARCHAR2(1024); -- adjust size
arr T_STRING    := T_STRING();

in the pl body:

REFDATA         := '<ReferenceFields><ReferenceField><FieldKey>Name1</FieldKey>                  <FieldValue>ABCD</FieldValue></ReferenceField><ReferenceField><FieldKey>Name1</FieldKey><FieldValue>ABCD</FieldValue></ReferenceField></ReferenceFields>';
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);
    arr.EXTEND();
    arr(arr.COUNT) := XMLSequence(XMLTYPE(REFDATAPART));
END LOOP;

FORALL i IN arr.FIRST..arr.LAST
    INSERT INTO HTS_TRANSACTION_XREF(TRANS_ID,XREF_FIELD, XREF_VALUE)       
        SELECT 
            '1',
            EXTRACTVALUE(arr(i), '/ReferenceField/FieldKey') "FIELDKEY",
            EXTRACTVALUE(arr(i), '/ReferenceField/FieldValue') "FIELDVALUE"
        FROM 
            dual
        WHERE 
            NOT EXISTS (
                SELECT 1 FROM TRANSACTION_CROSSREFERENCE 
                WHERE 
                    TRANS_ID   = '1' 
                AND XREF_FIELD = EXTRACTVALUE(arr(i), '/ReferenceField/FieldKey') 
                AND XREF_VALUE = EXTRACTVALUE(arr(i), '/ReferenceField/FieldValue')
            );


XMLPARSER.FREEPARSER(PARSER);

I also don't know what is the purpose of the not exists part, are you sure this is necessary?