SQL>
SQL> --EXTRACTVALUE is an Oracle function that uses an XPath expression,
SQL>
SQL> CREATE TABLE testxml (id NUMBER(3), dt SYS.XMLTYPE);
Table created.
SQL>
SQL> INSERT INTO testxml VALUES(111,
2 sys.xmltype.createxml(
3 '
4
5 Joe Smith
6 Mathematician
7 '))
8 /
1 row created.
SQL>
SQL> SET LONG 2000
SQL>
SQL> SELECT *
2 FROM testxml
3 /
ID DT
----------------------------------------------------------------------------------------------------
111 Joe SmithMathematician
SQL>
SQL> select EXTRACTVALUE(t.dt,'//customer/name') from testxml t;
EXTRACTVALUE(T.DT,'//CUSTOMER/NAME')
-----------------------------------------------------------------------------------------
Joe Smith
SQL>
SQL> drop table testxml;
Table dropped.
SQL>