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:

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

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:

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>

Next we’ll query our XML_TABLE:

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;

Which results in this output:

Workspace 1_034

Awesome, right!? :-)

Read more:

Oracle Database Reference for EXTRACTVALUE

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s