Use xmltype to convert xml string to xmltype 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(‘ ?>
2
3
4 address 1
5
6 ‘)
7 );
1 row created.
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> drop table myTable;
Table dropped.
SQL>

link: http://www.java2s.com/Tutorial/Oracle/0640__XML/Usexmltypetoconvertxmlstringtoxmltypedata.htm