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(' ?>
2
3
4
5 i1
6 i2
7 i3
8
9
10
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.