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

Oracle XE and utl_http = ORA-00904: invalid identifier

Link: http://www.mqaa.nl/2009/06/19/oracle-xe-and-utl_http/

This was working OK on our database server but my local Oracle XE instance gave the following error:

ORA-00904: invalid identifier

After looking around a bit I found that the utl_http library should be available in XE. So after digging a bit further I found the reason why it was not working. The fix is to login as sys (not system!) and execute the following command:

grant execute on utl_http to public

After this all is working OK again

Oracle Paquete UTL

• UTL URL -> ESCAPE, UNESCAPE http://www.orafaq.com/wiki/UTL_URL