Wednesday, July 2, 2008

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>

2 comments:

New said...
This comment has been removed by a blog administrator.
Reji said...

I am trying to load the below XML data using external table, but it's not working. Could you please help?

************



129



100.00



001296
1,318,542,280.16
3,929
1,318,542,280.16


FMG4
0000015901
8,688.70
ACH
96
12/15/2009
SETTLED
MORRIS


FMG5
0000015902
42,198.66
ACH
001296
12/15/2009
SETTLED
CASTLE


4FMG6
0000015903
57,278.25
ACH
001296
12/15/2009
SETTLED
FRANKLIN



ACHActivityFile_12152009.xml
12/15/2009 10:08:31 AM

"

*****************

External table structre

CREATE TABLE MY_XML_ET
(
PAYGOV_TX_ID VARCHAR2(30 BYTE),
AGENCY_TX_ID VARCHAR2(30 BYTE),
COLLECTION_AMOUNT VARCHAR2(30 BYTE),
COLLECTION_METHOD VARCHAR2(30 BYTE),
DEPOSIT_TICKET_NUMBER VARCHAR2(30 BYTE),
SETTLEMENT_DATE VARCHAR2(30 BYTE),
COLLECTION_STATUS VARCHAR2(30 BYTE),
SUBMITTER_NAME VARCHAR2(60 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY XXDP_XDIR_LDR_GLJOURNAL
ACCESS PARAMETERS
( records delimited by ""
badfile XXDP_XDIR_LDR_GLJOURNAL:'paygovesample.bad'
logfile XXDP_XDIR_LDR_GLJOURNAL:'paygovesample.log'
FIELDS
(
filler terminated by "",
paygov_tx_id enclosed by "" and "",
agency_tx_id enclosed by "" and "",
collection_amount enclosed by "" and "",
collection_method enclosed by "" and "",
deposit_ticket_number enclosed by "" and "",
settlement_date enclosed by "" and "",
collection_status enclosed by "" and "",
submitter_name enclosed by "" and ""
)
)
LOCATION (XXDP_XDIR_LDR_GLJOURNAL:'paygovesample.xml')
)
REJECT LIMIT UNLIMITED
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
NOMONITORING;


Thanks
Reji