Use extract function and xpath function to retrieve xml element data
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>
Related examples in the same category