Use extract function and xpath function to retrieve xml element data : extract « XML « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. XML
3. extract

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>