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

existsNode() [Oracle SQL]

existsNode(xml-type-instance, 'xpath-expression')
existsNode(xml-type-instance, 'xpath-expression', 'name-space')
existsNode() checks if xpath-expression returns at least one XML element or text node. If so, existsNode returns 1, otherwise, it returns 0.
existsNode should only be used in the where clause of the select statement.

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>
'));
How many of these documents do have an animals element beneath things?
select count(*) from some_things 
where existsNode(object_value,'/things/animals') = 1;
  COUNT(*)
----------
         2
See also extract (xml).