DevOps, CI/CD and Automation

I have a large (819MB) XML file I'm trying to import into a table in the format:
<ROW_SET>
<ROW>
<column_name>value</column_name>
</ROW>
...
<ROW>
<column_name>value</column_name>
</ROW>
</ROW_SET>

I've tried importing it with xmlsequence(...).extract(...) and ran into the number of nodes exceed maximum error.

I've tried importing it with XMLTable(... passing XMLTYPE(bfilename('DIR_OBJ','large_819mb_file.xml'), nls_charset_id('UTF8'))) and I gave up after it ran for 15+ hours ( COLLECTION ITERATOR PICKLER FETCH issue ).

I've tried importing it with:

insCtx := DBMS_XMLStore.newContext('schemaname.tablename');
DBMS_XMLStore.clearUpdateColumnList(insCtx);
DBMS_XMLStore.setUpdateColumn(insCtx,'column1name');
...
DBMS_XMLStore.setUpdateColumn(insCtx,'columnNname');
ROWS := DBMS_XMLStore.insertXML(insCtx, XMLTYPE(bfilename('DIR_OBJ','large_819mb_file.xml'), nls_charset_id('UTF8')));

and ran into ORA-04030: out of process memory when trying to allocate 1032 bytes (qmxlu subheap,qmemNextBuf:alloc).


All I need to do is read the XML file and move the data into a matching table in a reasonable time. Once I have the data in the database, I no longer need the XML file.

What would be the best way to import large XML files?


Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE 11.2.0.1.0 Production"
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production