Cable Forum - [Oracle] Help me!!!!

Hey, I've got a question. If you don't know Oracle, you might not want to read this message further. (Geekiness extreme)

So I'm a pretty good SQL jockey, but recently my boss is throwing XML stuff at me. I know that you can store an XML doc in a table as a field of type XMLType, and that you can extractValue() on that column to find the value of an element using the DOM. So lets take this from the top. My XML file looks something like this:

Code:

<employee>

        <id>550</id>

        <name>Smartypus McGnome</name>

        <position>CEO and President</position>

        <salary>150000</salary>

</employee>

<employee>

        <id>551</id>

        <name>Kazzap Kaboom</name>

        <position>COO</position>

        <salary>125000</salary>

</employee>

<employee>

        <id>552</id>

        <name>Whizbang Dustyboots</name>

        <position>VP of Gnomish Affairs</position>

        <salary>125000</salary>

</employee>

Now **** happens. How do you access those employees by DOM? "/employee/name" will not work. I suppose I can put a where clause in, but what if I want to retrieve all the information? If I do a select I will retrieve ONE row with the information I asked for. Is there a way to ask for a select * and then return multiple rows, something that the result would be like:

Code:

id        name                        position                salary

550        Smartypus McGnome        CEO and President        150000

551        Kazzap Kaboom                COO                        125000

552        Whizbang Dustyboots        VP of Gnomish Affairs        125000

Is storing the XML file as a XMLType wise? Or is it better to create tables? The problem is that the potential of the XML file is that there are hundreds of different element types, any or all of which may be present in the XML file, in quantities of one or more. The max depth of the DOM would be about 4, 5 levels though.

Any advice would be appreciated.

I'm using Oracle 9i.

If you want to see the XML doc def I want to store, go here:
http://justicexml.gtri.gatech.edu/ex...%203.0.0.0.xls