Oracle PL/SQL Tutorial/XML/XPath

Oracle support XPath predicate expressions

   <source lang="sql">

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></source>

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

   <source lang="sql">

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.</source>

XPath text() function

   <source lang="sql">

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></source>