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>
