Load XML Data Using Oracle SQLLDR
--SQLLDR control file to load xml data

--cmslog.ctl
LOAD DATA APPEND
CONCATENATE 4
INTO TABLE cms_usr_log
FIELDS  OPTIONALLY ENCLOSED BY "</ROWSET>"
TRAILING NULLCOLS
(dummy      FILLER CHAR(15)  TERMINATED BY "<ROW>",
 cms_id     CHAR(10) ENCLOSED BY "<IDX>" AND "</IDX>",
 log_desc   CHAR(30) ENCLOSED BY "<LOG_DESC>" AND "</LOG_DESC>"
)

-- Batch file to execute SQLLDR (sqlldrxml.bat) - all arguments 
-- should be in one line xml data password to be input at the 
-- prompt.  The data file (cmslog.xml) and control file 
-- (cmslog.ctl) should be in the same directory, if they
-- are in other directories, complete path has to be specified 
-- for successful data load.

c:\oracle11g\app\oracle\product\11.2.0\server\bin\sqlldr webusr1@xe 
 control=cmslog.ctl
 data=cmslog.xml
 log=cmslog.log
 bad=cmslog.bad

--Data File: cmslog.xml
XML Data File

=> Partial cmslog.log file 


Oracle registered trademark of Oracle Corporation.

Last Revised On: May 15th, 2014

  358