Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
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 Oct 24 '11 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 Oct 25 '11 at 20:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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