Total Pageviews

October 4, 2018

10/04/2018 03:25:00 PM
How To Load XML Documents Into a Table With XMLTYPE Column Using SQL*Loader 


XML documents to be loaded:: 

doc1.xml contains: <ENAME>CHANDRA</ENAME/> 
doc2.xml contains: <ENAME>SEKHAR</ENAME/> 

- Table specifications: 

create table EMP 
(pl_id number(4), pl_name varchar2(20), xml_doc SYS.XMLTYPE); 

SQL*Loader control file called load_xml.ctl contains: 

LOAD DATA 
INFILE * 
INTO TABLE planets 
REPLACE 
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' 
(pl_id, 
pl_name, 
fname FILLER CHAR, 
xml_doc LOBFILE(fname) TERMINATED BY EOF 


BEGINDATA 
1,CHANDRA,doc1.xml 
2,SEKHAR,doc2.xml

Run SQL*Loader using the controlfile:

sqlldr scott/tiger control=load_xml.ctl 

Results seen in table planets: 

PL_ID PL_NAME XML_DOC 
---------- -------------------- ---------------------------------------- 
1 CHANDRA<ENAME>CHANDRA</ENAME> 
2 SEKHAR <ENAME>SEKHAR</ENAME>
 
Related Posts Plugin for WordPress, Blogger...