Database Software

I am trying various ways of importing data from a fairly large XML file into database tables and I'm countinuously hitting the wall of 2^16 node occurences, which generates an error.
Has anyone ran into the same problem and found a workaround?

The content is an ISO20022 formatted file with a bank-to-customer statement (camt53) with 70k+ transactions. One upper lever being the statement and 70k+ entries for that statement.

My first approach was:
1. load the XML file in a table with a CLOB column
2. "load" the CLOB content into a xmltype variable

declare
l_xml_file xmltype;
l_xml_package xmltype;
l_xmlns varchar2(200); --namespace, logic not shown in example - irrelevant
begin
select t.clob_content
        into l_clob_temp
        from clob_table t
        where id = p_id
        for update
        ;
        l_xml_file := xmltype.createxml(l_clob_temp);
l_xml_package := l_xml_file.extract('/Document/BkToCstmrStmt/*', l_xmlns);
end;

3. outer loop though the statements (1 occurence for now)

loop
exit when l_xml_package.existsnode('/Stmt['||l_stmt_counter||']', l_xmlns) = 0;
...
/*fetch data using:*/
l_xml_package.extract('/Stmt['||l_stmt_counter||']/LglSeqNb/text()', l_xmlns).getstringval();
...
/*<inner loop through the >70k entries>*/
end loop;

4. inner loop through the entries, located on the end of the outer loop as shown above

loop
exit when l_xml_package.existsnode('/Stmt['||l_stmt_counter||']/Ntry['||l_ntry_counter||']', l_xmlns) = 0;
...
/*fetch data using:*/
l_xml_package.extract('/Stmt['||l_stmt_counter||']/Ntry['||l_ntry_counter||']/AcctSvcrRef/text()', l_xmlns).getstringval();
...
/*insert data into table*/
end loop;

This approach was working fine while the number of entries was moderate - up to 10k, but when the number of entries grew, the processing time grew exponentially, ie. 5.000 rows in 5 minutes, 70.000 rows in an estimated 8h, I didn't wait for it to finish.

To avoid this bottleneck I tried approach 2:
1. created a table of xmltype
2. converted my clob into xmltype:

insert into xmltype_table(xml_content)
select xmltype(clob_content)
from clob_table

3. created an index on xmltype_table.xml_content
4. same loops and fetch as in the first approach, but from the xmltype table, hoping that the indexes and lack of conversion clob->xml would speed thing up

The result was exactly the same as in approach 1 :-( , but with a twist!:
If I fetched the n-th occurence, Oracle returned the values of the n-th occurence concatenated with the value of the (n+2^16)th occurence :-O

Approach 3:
1. created a table of xmltype
2. converted my clob into xmltype:

insert into xmltype_table(xml_content)
select xmltype(clob_content)
from clob_table

3. created an index on xmltype_table.xml_content
4. cursors using xmltable:

cursor c_stmt (b_t_id number) is
select x.LglSeqNb
,        x.Id
from xmltable(xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02')
             ,'/Document/BkToCstmrStmt/Stmt'
             passing (select t.xml_content from xmltype_table t where t.id = b_t_id)
             columns LglSeqNb path 'LglSeqNb'
             ,           Id path 'Id'
             ) x
;
cursor c_ntry(b_t_id number, b_stmt_id varchar2) is
select x.Amt
from xmltable(xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02')
             ,'/Ntry'
             passing (select extract(t.xml_content, '/Document/BkToCstmrStmt/Stmt/Ntry', 'xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02"')
                      from xmltype_table t
                      where t.id = b_t_id
                      and   extractvalue(t.xml_content
                                        ,'/Document/BkToCstmrStmt/Stmt/Id'
                                        ,'xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02"'
                                        ) = b_stmt_id
                     )
             columns Amt path 'Amt'
             ) x
;

5. loop through the cursors:

for r_stmt in c_stmt loop
    --insert statement data into statement table
    for r_ntry in c_ntry loop
        --insert entry data into entry table
    end loop;
end loop;

As soon as the inner loop reaches 2^16th row it crashes:
ORA-04030: out of process memory when trying to allocate 55600 bytes (kxs-heap-w,qmemNextBuf:Large Alloc)

Approach 4:
Same as approach 3, but using bulk collect limited to 1000 records at a time, same result.

Approach 5:
Instead of using cursors I used sql:

insert into target_table (columns)
( select as in the cursors above )

Same error.

I'm running out of ideas, any suggestions?

Database version:
11.2.0.3.0, 64bit

Edited by: 973704 on 28.11.2012 5:12

Edited by: JurijC on Nov 28, 2012 2:20 PM - discovered /code/ formatting tags :-)