Saturday, July 15, 2017

Loading Large XML Documents Using SQL Loader

I have few XML files which needs to be loaded in oracle database. We'll use SQL Loader utility to load in database. 

Step1 : Create table in database


SQL> create table hr.test1(id number ,file_name varchar2(100), xml_data xmltype);


Step 2 : Create controlfile for SQL loader

#>vi load_xmlfile.ctl

LOAD DATA
INFILE *
BADFILE 'C:\Users\MyPC\load_xmlfile.bad'
DISCARDFILE 'C:\Users\MyPC\load_xmlfile.dsc'
INTO table hr.test1
APPEND
FIELDS TERMINATED BY ','
(
id,
file_name,
xml_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,My_File_Test1_20170715.xml
002,My_File_Test2_20170715.xml

Note :-
I put my controlfile (load_xmlfile.ctl) and *.xml files in same directory.


Step 3 : Run SQL Loader


#Run SQL Loader utility using above mentioned controlfile.

#sqlldr userid=/ as sysdba control= load_xmlfile.ctl

OR

# Use toad to launch SQL Loader and use mentioned contrfile file. 


Step 4: Check Your table in database


SQL> select * from hr.test1;

ID       FILE_NAME                            XML_DATA
--------------------------------------------------------------------
001     My_File_Test1_20170715.xml   (HUGECLOB)
002     My_File_Test1_20170715.xml   (HUGECLOB)




No comments:

Post a Comment