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>