Loding XML using external table

A small example to load XML using external table.

SQL> CREATE DIRECTORY my_xml_dir AS 'E:\oracle\Log_files\UTL_AKIVATST'
  2  /

Directory created.

SQL> DROP TABLE my_xml_et
  2  /

Table dropped.

SQL> CREATE TABLE my_xml_et
  2  (  EMPNO NUMBER,
  3     EMPNAME VARCHAR2(10),
  4     JOB VARCHAR2(10),
  5     HIREDATE DATE,
  6     SAL NUMBER
  7  )
  8  ORGANIZATION EXTERNAL
  9  (
 10     TYPE ORACLE_LOADER
 11     DEFAULT DIRECTORY my_xml_dir
 12     ACCESS PARAMETERS
 13     (
 14             records delimited by "</EMP>"
 15             badfile my_xml_dir:'empxt%a_%p.bad'
 16             logfile my_xml_dir:'empxt%a_%p.log'
 17             FIELDS
 18             (
 19                     filler            char(2000) terminated by "<EMP>",
 20                     EMPNO             char(2000) enclosed by "<EMPNO>" and "</EMPNO>",
 21                     EMPNAME           char(2000) enclosed by "<ENAME>" and "</ENAME>",
 22                     JOB               char(2000) enclosed by "<JOB>" and "</JOB>",
 23                     HIREDATE          char(2000) enclosed by "<HIREDATE>" and "</HIREDATE>",
 24                     SAL               char(2000) enclosed by "<SAL>" and "</SAL>"
 25             )
 26     )
 27     LOCATION ('emp.xml')
 28  )
 29  PARALLEL
 30  REJECT LIMIT UNLIMITED
 31  /

Table created.

SQL> SELECT * FROM my_xml_et
  2  /

     EMPNO EMPNAME    JOB        HIREDATE         SAL
---------- ---------- ---------- --------- ----------
      7369 SMITH      CLERK      17-DEC-80        800
      7499 ALLEN      SALESMAN   20-FEB-81       1600

This is the sample XML that i used.

emp.xml
----------------

<EMPLOYEES>
<EMP>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<HIREDATE>17-DEC-80</HIREDATE>
<SAL>800</SAL>
</EMP>
<EMP>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<HIREDATE>20-FEB-81</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
</EMP>
</EMPLOYEES>