Script to load XML file to Oracle table

Experts.

I have created a oracle table as below.

create table xml_tab  
(  
File_No  number ,  
File_content Varchar2(2000),  
file_type xmltype  
);

Daily we are receiving many XML files as below.

here is our sample xml file.

File1 :

<?xml version="1.0" encoding="UTF-8"?>  
 -<ContentMap>  
 <Object uri="x-wc://e29f9e65f5ad7794:wt.part.WHO:PROG1" metaFilePath="WHO:PROG1.xml.meta" contentPath="WHO:PROG1.xml"/>  
</ContentMap>

File2 :

<?xml version="1.0" encoding="UTF-8"?>  
 <OptionsDefinition version="2.0"> </OptionsDefinition>  
 

Required output is

File_No       File_Type            File_Content  
===================================  
File1                    xml                  <?xml version="1.0" encoding="UTF-8"?>  
                                                -<ContentMap>  
                                                <Object uri="x-wc://e29f9e65f5ad7794:wt.part.WHO:PROG1" metaFilePath="WHO:PROG1.xml.meta" contentPath="WHO:PROG1.xml"/>  
                                                    </ContentMap>  
                                                      
FILE2                  xml                  <?xml version="1.0" encoding="UTF-8"?>  
                                               <OptionsDefinition version="2.0"> </OptionsDefinition>  

Please suggest shell script to resove this.

Hi,
the SQL*Loader-tool is designed to do that, no script needed. You do not say what database version you use, so I'll point you to the 10g documentation for loading XML-files into tables:
29 Loading XML Data Using SQL*Loader

appreciate your quick response. I have around 10000 xml files with different contents/tags/column values. Please advise a script to automate /direct load data into Oracle tables.

Here in the above have posted only 2 record values for providing you some information. Please advise the script/oracle procedure.

Thanks,

Experts,

please advise. As we don't have any directory in Oracle we need to create a directory in Unix and need to load XML files to oracle table using Shell script.

Please suggest.

Thanks

What's wrong with cero's fine suggestion?

We have no access to create Directory. So we can't use SQL Loader in Oracle side.

As the FILE3 is very big we are getting error as below

at <?xml version="1.0" encoding="UTF-8"?>

SQL Error: ORA-01704: string literal too long
01704. 00000 - "string literal too long"
*Cause: The string literal is longer than 4000 characters.
*Action: Use a string literal of at most 4000 characters.
Longer values may only be entered using bind variables.

You do not need to be on Oracle side to use sqlloader.

It's a client program, you have local (any) directory with data and oracle client installed (which contains sqlloader).

Then using credentials you upload the data to database thru network as you would use toad or sqlplus.

Have you looked up the ORA-01704 error and advice for it ?

Regards
Peasant.

This issue am able to resolve it. But would like to write shell script to load the data from XML files to Oracle. We are going to use SQL Loaded with UNIX. Please suggest me the sample code to load data.

------ Post updated at 01:55 AM ------

Hi .. Have resolved this issue with the help of Oracle expertise. My requirement is to write a Shell script to load XML data to Oracle tables.

Please help me to write a Shell script.

Thanks