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:

How to add a datafile to a bigfile tablespace in Oracle 23ai Database.

  Syntax for Bigfile Tablespace: SQL > ALTER TABLESPACE tablespace_name ADD DATAFILE SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED; Ho...