SQL Loader with XML DATA

1. Login with Username and Password
2. Create table load_test of xmltype;
3. Exit from 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 usename/password control= c:/tmp/test.ctl

6. Sqlplus   usename/password
  select * from load_test;

Example to store XML type data from SQL*loader

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

1.  Login with Username and Password

2.  Create table load_test of xmltype;

3.  Create a data file c:\bhaskar.dat - contains XML data

4.  Create a control file c:\loaddata.ctl
LOAD DATA

INFILE *

INTO TABLE test_load

APPEND XMLType(XMLDATA)

( lobfn FILLER CHAR TERMINATED BY ',',

     XMLDATA LOBFILE(lobfn) TERMINATED BY ''

   )

BEGINDATA

c:\bhasakr.dat

5. c :\> sqlldr usename/password
    control=c:\ loaddata.ctl
6. sqlplus
    usename/password
  select * from load_test;