Nov 12, 2011

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 xmltype 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;


No comments:

OraApps Search

Custom Search

Search This Blog