Best methods for ingesting XML into Oracle 11gR2

oracleoracle-11g-r2xml

I have a large Oracle 11g Instance. I now have a requirement to convert large amounts of XML into records in several related tables.

The xml will arrive frequently at irregular intervals. The documents will vary in size (100megs avg)

The XML is well defined and static in structure.
The documents contains primary elements which should become the records.
Each element has two child elements for related entries.

So for a element that looks like this 

 <element>
    <pet>
        <row0>1</row0>
        <row1>Dog</row1>
        <row2>Rex</row2>
        <row3>ownerRow3</row3>
    </pet>   
    <owner>
        <row1>Bob</row1>
        <row2>banker</row2>
        <row3>1</row3>
    </owner>
</element>

would be come one "row" in two tables.

What is the most reasonable ( time/money vs "best" ) solution for this relatively simple (and common) task ?

I am not opposed to writing my own "digester" but I do not want to overlook simple solutions ( commercial or free)

My familiarity with available Oracle tools is low and Web searches are not providing a reasonable answer.

Best Answer

The fastest way to get things in is via SQLLoader, also for XML. The following will show you a method based on bfilename, which should be able to load XML files into the database in around a minute or 2 per file.

https://forums.oracle.com/forums/thread.jspa?threadID=2284390&tstart=30

You got some repeating elements in that file, so if you want to be able to query it efficient you should created specific, based on (x)path setting xmlindexes.

Some extra info can be found here

forums.oracle.com/forums/thread.jspa?threadID=2234618&tstart=120

If you need a short introduction into, or howto examples, search the needed info via blog.gralike.com

HTH

Marco

Related Question