[Oracle] Help me!!!!

Jerrek · 12-08-2003, 06: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:

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

Mr_love_monkey · 12-08-2003, 08:41

Quote:

Originally posted by Jerrek

Now **** happens. How do you access those employees by DOM? "/employee/name" will not work. [/url]

Can't you do /employee/name/text() ?

Jerrek · 12-08-2003, 14:10

Well no. See there are more than employee node? I need something to enumerate it...