Use the EXTRACT function and the following XPath:/emps/emp/interests/interest/ : XPath « XML « Oracle PL/SQL Tutorial

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

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.