Extracting XML

Hi,

I have ~1100 XML files which should be stored in the 10gR1EE.

The structure of a XML file is like this:

<a val="1167919973">

   <b val="12345">
     <c val1="1167922800" val2="12.2" val3="515.3"/>    </b>
   <b> ... </b>
   ...
</a>

(always 1 <a>, ~20 <b> and ~120 <c>)

I created a table:

   CREATE TABLE myimport OF XMLTYPE;
Then the files were imported with sqlldr. Until this point all works fine.

But now I want to extract the information and copy it into another table
(insert as select).

I want to select all "rows" in the form

   (b.val, a.val, c.val1, c.val2, c.val3) At the end, there schould be ~2.6mio rows.

I tried this SELECT statement:

select extractvalue(value(t1), '/b/@val') b_val,

        extractvalue(value(p), '/a/@val') a_val,
        extractvalue(value(t2), '/c/@val1') c_val1,
        extractvalue(value(t2), '/c/@val2') c_val2,
        extractvalue(value(t2), '/c/@val3') c_val3
   from myimport p,
        table(xmlsequence(extract(value(p), '/a/b'))) t1,
        table(xmlsequence(extract(value(t1), '/b/c'))) t2

This statement is veeeeeeery slow... and I don't know if it is correct...

Questions:
1. I think there is no need for a join between p, t1 and t2 but I am not sure. Do I need it?
2. Is there a better way to select all values? 3. Should I create the table as a relational table, not as XMLType? How to import this?

Thanks,

Markus Received on Thu Mar 15 2007 - 03:51:36 CDT