Jerrek
12-08-2003, 05:01
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:
<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:
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/extern/v2/JXDD%203.0.0.0.xls
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:
<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:
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/extern/v2/JXDD%203.0.0.0.xls