Re: Extracting XML

Markus Schmidt schrieb:
> 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...
This depends on how you store the XML.
If you always want to extract values, the recommended procedure is to define an XML schema and create an xml column type for this. Then oracle itself generates a few background tables (you won't need to bother about them) and stores them already parsed. Then things like extractvalue become as fast as you normally expect from a database.
What happens right now in your case is that /every/ document gets parsed in /every/ select statement. Storing XML documents like you do today is only recommended if you just want to store and retrieve the documents as such, much like CLOBs.

Look it up at
http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb03usg.htm#sthref237

Lots of Greetings!
Volker

-- 
For email replies, please substitute the obvious.