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

XPath query with XML DB

XML documents stored in XML DB can be queried using XPath.
First, a table is needed to store the document. 
create table xml_xpath (
  id  number primary key,
  doc XMLType
);
Storing two XML documents: 
insert into xml_xpath values (1, sys.XMLType.CreateXML(
'<?xml version="1.0"?>
<person>
  <firstname>John  </firstname>
  <lastname> Miller</lastname>
  <extension>403   </extension>
</person>'));

insert into xml_xpath values (2, sys.XMLType.CreateXML(
'<?xml version="1.0"?>
<person>
  <firstname>Paul  </firstname>
  <lastname> Donare</lastname>
  <extension>233   </extension>
</person>'));
Querying the persons' first names: 
column firstname format a30
select id, sys.XMLType.extract(doc,'/person/firstname/text()') firstname from xml_xpath;

        ID FIRSTNAME
---------- ------------------------------
         1 John
         2 Paul