Oracle PL/SQL Tutorial/XML/XPath

Oracle support XPath predicate expressions

SQL> CREATE TABLE myTable
  2  (id  NUMBER PRIMARY KEY
  3  ,doc XMLType NOT NULL)
  4  XMLTYPE doc STORE AS CLOB
  5  /
Table created.
SQL>
SQL>
SQL> 
SQL> update myTable
  2  set doc = updateXml( doc,"message[greeting="Hi"]/body/text()", "test again")
  3  /
0 rows updated.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>

Use the EXTRACT function and the following XPath:/emps/emp/interests/interest/

SQL>
SQL> CREATE TABLE myTable(
  2      id  NUMBER PRIMARY KEY,
  3      emps XMLType NOT NULL
  4  );
Table created.
SQL>
SQL> INSERT INTO myTable VALUES (1, xmltype("<?xml version="1.0" standalone="no"
 ?>
  2  <emps>
  3      <emp>
  4          <interests>
  5              <interest>i1</interest>
  6              <interest>i2</interest>
  7              <interest>i3</interest>
  8          </interests>
  9      </emp>
 10  </emps>")
 11  );
1 row created.
SQL>
SQL>
SQL> select extract(emps,"/emps/emp/interests/interest/text()")
  2  from myTable;

EXTRACT(EMPS,"/EMPS/EMP/INTERESTS/INTEREST/TEXT()")
------------------------------------------------------
i1i2i3
1 row selected.
SQL>
SQL> drop table myTable;
Table dropped.

XPath text() function

SQL>
SQL> CREATE TABLE myTable(
  2      id  NUMBER PRIMARY KEY,
  3      emps XMLType NOT NULL
  4  );
Table created.
SQL>
SQL> INSERT INTO myTable VALUES (1, xmltype("<?xml version="1.0" standalone="no"
 ?>
  2  <emps>
  3      <emp>
  4          <home_address>address 1</home_address>
  5      </emp>
  6  </emps>")
  7  );
1 row created.
SQL>
SQL>
SQL> select extract(emps, "/emps/emp/home_address/text()" )
  2  from myTable
  3  /

EXTRACT(EMPS,"/EMPS/EMP/HOME_ADDRESS/TEXT()")
------------------------------------------------------
address 1
1 row selected.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL>