SQL Loader with XML DATA

SQL Loader with XML DATA

SQL Loader with XML DATA

1. Conn hr/hr
2. Create table load_test of xmltype;
3. Exit fom user
4. Create a control file test.ctl


LOAD DATA
INFILE *
TRUNCATE INTO TABLE load_test
XMLType(xmldata)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
xmldata
)
BEGINDATA
KING<  ./name><  ./  EMP>,
SCOTT<. /name><  ./EMP>,
SMITH< . /name><  ./EMP>

5. c:\> sqlldr hr/hr control= c:\test.ctl

6. sqlplus
   hr/hr
   select * from load_test;

Example to store XML type data from sql*loader

--------------------------------------------------------------------------

1. conn hr/hr
2. create table load_test of xmltype;
3. create a data file c:\person.dat - contains XML data

4. create a control file c:\load.ctl
LOAD DATA
INFILE *
INTO TABLE test_load
APPEND XMLType(XMLDATA)
(
 lobfn FILLER CHAR TERMINATED BY ',',
 XMLDATA LOBFILE(lobfn) TERMINATED BY ''
 )
BEGINDATA
c:\person.dat

5. c:\> sqlldr hr/hr
control=c:\load.ctl
6.sqlplus
hr/hr
select * from load_test;