2

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.

Improve this question

1 Answer 1

Reset to default
1

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

Improve this answer
2
  • Thanks. I remain shocked that there is no simple gui tool that would allow mapping of XML elements to to rows/columns and then bulk import. Excel does some of this just looking for a tool that covered that last 25% .
    – stimpy
    Commented Oct 24, 2011 at 2:42
  • The problem is that there are too many ways to map XML content due to the fact that it is too "flexible" and every format can/will be different... That said, the SQLLoader tool could have been a bit more like excel...
    – Marco Gralike
    Commented Oct 25, 2011 at 20:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.