René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

extractValue() [Oracle SQL]

extractValue(xml-type-instance, 'xpath-expression')
extractValue(xml-type-instance, 'xpath-expression', 'name-space')
extractValue() is similar to extract(), but it extracts the value without the XML element tags. It only works if it operates on a single element, otherwise, it throws an ORA-19025: EXTRACTVALUE returns value of only one node.

An example

create table some_things of xmltype;
Three xml documents are inserted into the table. 
insert into some_things values (xmltype('
  <things>
    <numbers><item>1</item><item>59</item></numbers>
    <animals><item>bird</item><item>cat</item><item>dog</item></animals>
  </things>
'));

insert into some_things values (xmltype('
  <things>
    <countries><item>Canada</item><item>Egypt</item><item>Italy</item></countries>
    <numbers><item>55</item><item>101</item></numbers>
  </things>
'));

insert into some_things values (xmltype('
  <things>
    <cities><item>New York</item><item>Tokyo</item><item>Zurich</item></cities>
    <animals><item>elephant</item><item>snake</item></animals>
  </things>
'));
Selecting the 2nd animal in each document: 
select extractValue(object_value,'/things/animals/item[position()=2]') "2nd Animal" from some_things;

2nd Animal
------------------------------------------------------------
<item>cat</item>

<item>snake</item>
The 2nd inserted document doesn't have an animal, so null is returned instead.