Read and insert large xml file using Sql/Plsql

Question

I have a requirement to read and insert a very large XML file into Oracle tables. The file is of the format:

<PMT>
<SEQ>0001</SEQ>
<SINGADDINFO>
<INFOTYPE>PMTRELINFO1</INFOTYPE>
<INFOAMOUNT>100.00</INFOAMOUNT>
</SINGADDINFO>
<SINGADDINFO>
<INFOTYPE>PMTRELINFO2</INFOTYPE>
<INFOAMOUNT>200.00</INFOAMOUNT>
</SINGADDINFO> --AND THIS CAN GO UP TO <PMTRELINFO9999>
</PMT>
<PMT>
--ANOTHER TRANSACTION CAN BE INCLUDED HERE, THE PMT TAG CAN BE A COUNT OF 1 TO  
--1000
</PMT>

The following code works fine for smaller XML documents, but it gets stuck on the big file.

FOR r IN
  (SELECT Extract(Value(p),'pmt:PayInfo/pmt:Single', r_namespace) As Address,
  ExtractValue(Value(p),'pmt:PayInfo/pmt:Single/pmt:SingExtRef', r_namespace) AS extref,
  ExtractValue(Value(p),'pmt:PayInfo/pmt:Single/pmt:SingSequence', r_namespace) AS singseq

  FROM TABLE(XMLSequence(Extract(payXml,'/bbva:bbva/bbva:Pmt/pmt:Payments/pmt:PayInfo['||ctr2||']', r_namespace))) p
  )
  LOOP

    FOR row1 IN
    (SELECT ExtractValue(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpType/text()', r_namespace) AS singtype,
          --  ExtractValue(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpText[1]/text()', r_namespace) AS singtext1,
          --  ExtractValue(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpText[2]/text()', r_namespace) AS singtext2,
            xmltype.extract(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpText[1]/text()', r_namespace).getClobVal() AS singtext1,
            xmltype.extract(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpText[2]/text()', r_namespace).getClobVal() AS singtext2,
            xmltype.extract(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpText[3]/text()', r_namespace).getClobVal() AS singtext3,
            xmltype.extract(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpText[4]/text()', r_namespace).getClobVal() AS singtext4,
            xmltype.extract(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpText[5]/text()', r_namespace).getClobVal() AS singtext5,
            xmltype.extract(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpText[6]/text()', r_namespace).getClobVal() AS singtext6,
            xmltype.extract(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpText[7]/text()', r_namespace).getClobVal() AS singtext7,
            xmltype.extract(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpText[8]/text()', r_namespace).getClobVal() AS singtext8,
            xmltype.extract(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpText[9]/text()', r_namespace).getClobVal() AS singtext9,
            xmltype.extract(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpText[10]/text()', r_namespace).getClobVal() AS singtext10,
            xmltype.extract(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpText[11]/text()', r_namespace).getClobVal() AS singtext11,
            xmltype.extract(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpText[12]/text()', r_namespace).getClobVal() AS singtext12,
            xmltype.extract(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpText[13]/text()', r_namespace).getClobVal() AS singtext13

    FROM TABLE(XMLSequence(Extract(r.Address,'/pmt:Single/pmt:SingAddInfo', r_namespace))) l
    )
    LOOP
IF (row1.singtype LIKE 'PmtRel%')
 THEN
 r_addenda := row1.singtype;
 r_addenda1 := row1.singtext1;
 r_addenda2 := row1.singtext2;
 r_addenda3 := row1.singtext3;
 r_addenda4 := row1.singtext4;
 r_addenda5 := row1.singtext5;
 r_addenda6 := row1.singtext6;
 r_addenda7 := row1.singtext7;
 r_addenda8 := row1.singtext8;
 r_addenda9 := row1.singtext9;
 r_addenda10 := row1.singtext10;
 r_addenda11 := row1.singtext11;
 r_addenda12 := row1.singtext12;

 dbms_output.put_line('test1');

 if r_addenda6 = 'NA' then
 r_addenda6 := null;
 elsif r_addenda6 != 'NA' then
 r_addenda6 := to_date(r_addenda6, 'yyyymmdd');
 end if;

 IF r_addenda3 = 'NA' THEN
 r_addenda3 := null;
 ELSIF r_addenda3 != 'NA' THEN
 r_addenda3 := r_addenda3;
 END IF;

 IF r_addenda4 = 'NA' THEN
 r_addenda4 := null;
 ELSIF r_addenda4 != 'NA' THEN
 r_addenda4 := r_addenda4;
 END IF;

 If r_addenda5 = 'NA' THEN
 r_addenda5 := null;
 ELSIF r_addenda5 != 'NA' THEN
 r_addenda5 := r_addenda5;
 END IF;

INSERT INTO TEMP_ACH_ADDENDA( RMR_NUMBER, RMR_CODE, INVOICE_NUMBER, RMR_PAID_AMT, RMR_ORIG_AMT, RMR_DISC_AMT, RMR_DATE, REF_CODE,
REF_ID, REF_NAME, REF_INSTRUCTIONS, ADX_INSTRUCTIONS, ENT_NUMBER, EXT_REF)  values
(r_addenda, r_addenda1, r_addenda2, r_addenda3, r_addenda4, 
 r_addenda5, r_addenda6, r_addenda7, r_addenda8, r_addenda9, r_addenda10, r_addenda11, r_addenda12, r_ext_ref
);

How can I project this XML to insert the SQL into a table? Can I use a cursor because the <PMTRELINFO%> can be variable in the number of occurrences? And what about on the insert, what would be a better way of handling it? There are other values within the <SINGADDINFO> tag too, but the <PMTRELINFO> is what is causing the problems when I test with a file that has 9999 of them.

Any suggestions or pointing in the right direction would be highly appreciated.

have a look a this [post](http://stackoverflow.com/questions/30952656/xml-bulk-insert-in-oracle-table) hope it helps — Franck Ngako, Oct 31 '16 at 16:06
Frank Ngako, my existing code is already taking that approach. Mustaccio, when i encounter a large xml file as mentioned, the procedure never successfully executes. The last time i left it running, it was still going for 4 hours which is obviously not feasible. — QuickDrawMcgraw, Oct 31 '16 at 17:13
See if this gives you an idea: http://stackoverflow.com/questions/998055/oracle-loading-a-large-xml-file — mustaccio, Oct 31 '16 at 17:27
Not helping, can't figure out performance issue. Any other recommendations/help is really appreciated. — QuickDrawMcgraw, Oct 31 '16 at 19:28

Read and insert large xml file using Sql/Plsql

0 Answers0