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>
October 4, 2018
