SQL> CREATE TABLE myTable
2 (id NUMBER PRIMARY KEY
3 ,doc XMLType NOT NULL)
4 XMLTYPE doc STORE AS CLOB
5 /
Table created.
SQL>
SQL>
SQL>
SQL> DECLARE
2 v_doc XMLType;
3 v_text varchar2(100);
4 BEGIN
5 select doc into v_doc from myTable
6 where id = 2;
7
8 v_text := v_doc.extract('/message/body/text()' ).getstringval;
9
10 dbms_output.put_line(v_text);
11 END;
12 /
SQL>
SQL> drop table myTable;
Table dropped.
SQL>