Home » RDBMS Server » Server Utilities » Loading Multiple XML files using SQL*Loader
Loading Multiple XML files using SQL*Loader [message #506525] Mon, 09 May 2011 11:21 Go to next message
silvermission
Messages: 1
Registered: May 2011
Junior Member
Hi All,

I believe I read most of the topics, but found nothing that I am looking for.

I am trying to load multiple XML files into Oracle DB using SQL Loader. The filenames of the XML files starts with a description and then numbers, where the numbers are different each time.

Here's my CTL file:

LOAD DATA
INFILE *
INTO TABLE XML_TABLE TRUNCATE
xmltype(XML_TABLE)
FIELDS
(
EXT_XML filler char(60),
DATA_XML LOBFILE (EXT_XML) TERMINATED BY EOF
)
BEGINDATA
decription_1.xml
~
~

I don't want to keep having to go into the ctl file and change the numbers of the xml file. Is there a way where I could just load all .xml files that begins with 'description'? Like maybe

description*.xml

Thanks for your help.
Re: Loading Multiple XML files using SQL*Loader [message #506526 is a reply to message #506525] Mon, 09 May 2011 11:40 Go to previous message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, you can always create the control file dynamically before the load, based on the files in the directory.

lnxvmdevel tmp $ ls -l
total 4
-rw-r--r-- 1 oracle dba   25 May  9 18:35 file1.xml
-rw-r--r-- 1 oracle dba   25 May  9 18:35 file2.xml
-rw-r--r-- 1 oracle dba  158 May  9 18:36 header.ctl
lnxvmdevel tmp $ cat header.ctl
LOAD DATA
INFILE *
INTO TABLE XML_TABLE TRUNCATE
xmltype(XML_TABLE)
FIELDS
(
EXT_XML filler char(60),
DATA_XML LOBFILE (EXT_XML) TERMINATED BY EOF
)
BEGINDATA
lnxvmdevel tmp $ cat header.ctl >load.ctl
lnxvmdevel tmp $ ls file* >> load.ctl
lnxvmdevel tmp $ cat load.ctl
LOAD DATA
INFILE *
INTO TABLE XML_TABLE TRUNCATE
xmltype(XML_TABLE)
FIELDS
(
EXT_XML filler char(60),
DATA_XML LOBFILE (EXT_XML) TERMINATED BY EOF
)
BEGINDATA
file1.xml
file2.xml
lnxvmdevel tmp $



Previous Topic: Difference in size of exports (.dmp) files
Next Topic: Decode value for sqlldr
Goto Forum:
  


Current Time: Thu Mar 28 19:01:44 CDT 2024