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!? :-)

Continue reading “Select an XML tag or node using Oracle PL SQL”