Querying XML in Oracle (using EXTRACTVALUE)
It had to happen sooner or later, today I had to query an XML type column in Oracle. It’s a lot easier than I thought it would be – but only because I wanted to return a singular attribute.
This is how I did it using Toad:
Run a normal query including the XML column. The data browser will show that column as (ORAXML) for all records. Double click the XML field and you’re presented with a tree structure for the XML. If you right click on a node and choose “Copy Path” then the path will be copied to the clipboard. You’ll have something like this:
abc1:ROOT\Foo\Bar
The function to extract a singular attribute from XML is EXTRACTVALUE, and it’s really straightforward:
EXTRACTVALUE
(XMLType_instance, XPath_string
[, namespace_string ]
)
First the path from Toad needs to be converted to an XPath string:
abc1:ROOT\Foo\Bar becomes //Foo/Bar
Then it’s a simple
SELECT
EXTRACTVALUE(xml_col_name, ‘//Foo/Bar’)
FROM
MY_TABLE
Very easy – and made much easier by Toad. Quest software really know their audience.
