Select an XML tag or node using Oracle PL SQL

Hi guys,
I’ve been using this trick for a while and it’s quite useful when querying Oracle Service Bus logs. I found myself trying to explain this one to a colleague and thought it made a nice post 🙂
Let’s start with the basic command:
extractvalue
Which translates into:
[code language=”sql”]
SELECT EXTRACTVALUE(
xmltype(xml_val),
‘/xml-fragment/tns:product’,
‘xmlns:tns="http://example.org/"’,
‘xmlns:ans="http://anothernamespace.org/"’,
‘xmlns:yans="http://yetanothernamespace.org/"’
)
x
FROM xml_table
[/code]
Note: the first argument is being cast from CLOB to XMLTYPE and that you can keep adding namespaces at the end by adding commas.
I’ve added three rows in my table “XML_TABLE” for this example:
[code language=”XML”]
ROW1:
<book category="WEB">
<title lang="en">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
ROW2:
<book category="CHILDREN">
<title lang="en">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
ROW3:
<book category="WEB">
<title lang="en">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
[/code]
Next we’ll query our XML_TABLE:
[code language=”sql”]
SELECT
EXTRACTVALUE( xmltype(xmlval), ‘/book/title’ ) AS title,
EXTRACTVALUE( xmltype(xmlval), ‘/book/author’ ) AS author,
EXTRACTVALUE( xmltype(xmlval), ‘/book/year’ ) AS year,
EXTRACTVALUE( xmltype(xmlval), ‘/book/price’ ) AS price
FROM xml_table;
[/code]
Which results in this output:
Workspace 1_034
Awesome, right!? 🙂

Read more:
Oracle Database Reference for EXTRACTVALUE

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.