Load Simple XML File to Ora Table

[thermidor]

Can anyone tell me (or point me to an online resource) how to load a small XML file into an Oracle table? I need to have a nightly process that picks up and loads a file.

TIA,
Sven

 

[taupirho]

Do you want to just load the whole file en-masse into a column in a table or actually parse the xml and extract the individual bits and load these into several columns that correspond to the XML nodes/attributes

 

[thermidor]

I just want to load the whole file en-masse.

Thanks!

 

[taupirho]

OK,

The quickest way is to sqlload it into a clob.

Lets make a sample XML file first

$ sqlplus db/pw
SQL> set pages 0
SQL> spool xml_test.xml
SQl> select xmlelement("emp",xmlforest(empno,ename,job)) from scott.emp
where rownum < 5
SQL> exit
$

Should create a file with the following data

<emp>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
</emp>

<emp>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
</emp>

<emp>
<EMPNO>7521</EMPNO>
<ENAME>WARD</ENAME>
<JOB>SALESMAN</JOB>
</emp>

<emp>
<EMPNO>7566</EMPNO>
<ENAME>JONES</ENAME>
<JOB>MANAGER</JOB>
</emp>

Next create a table to hold our xml data

SQL> create table xml_test(xml_data clob)
2 /

Table created.

SQL>


Next create a control file like the one below:

$ type xml_test.xml
load data
infile *
into table xml_test
append
fields
(
ext_fname filler char(80),
xml_data lobfile(ext_fname) terminated by EOF)
begindata
xml_test.xml
$
$ sqlloader capel/james control=tom

SQL*Loader: Release 9.2.0.2.0 - Production on Thu Oct 26 15:56:32 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 1
$
$
$

Lets see if its there

$ sqlplus db/pw
SQL>
SQL> set long 100000
SQL> set longchunksize 100000
SQL> col xml_data form a60
SQL> set pages 0
SQL> select xml_data from xml_test;
<emp>

<EMPNO>7369</EMPNO>

<ENAME>SMITH</ENAME>

<JOB>CLERK</JOB>

</emp>

<emp>

<EMPNO>7499</EMPNO>

<ENAME>ALLEN</ENAME>

<JOB>SALESMAN</JOB>

</emp>

<emp>

<EMPNO>7521</EMPNO>

<ENAME>WARD</ENAME>

<JOB>SALESMAN</JOB>

</emp>

<emp>

<EMPNO>7566</EMPNO>

<ENAME>JONES</ENAME>

<JOB>MANAGER</JOB>

</emp>



SQL>