http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=42560
SQL> create view my_emp as
2 select to_number(extractvalue(value(r), '/ROW/EMPNO')) empno,
3 cast(extractvalue(value(r), '/ROW/ENAME') as varchar2(10)) ename,
4 to_number(extractvalue(value(r), '/ROW/DEPTNO')) deptno,
5 cast(extractvalue(value(r), '/ROW/JOB') as varchar2(9)) job,
6 to_number(extractvalue(value(r), '/ROW/SAL')) sal,
7 to_date(extractvalue(value(r), '/ROW/HIREDATE'), 'MM/DD/YYYY HH24:MI:SS') hiredate
8 from ( select xmltype(bfilename('MY_DIR', 'x.txt'), NLS_CHARSET_ID('WE8MSWIN1252')) val
9 from dual ) x,
10 table(xmlsequence(extract(x.val, '/ROWSET/ROW'))) r
11 /
http://www.oraclealchemist.com/oracle/undone-by-undo/