| Loading Multiple XML files using SQL*Loader [message #506525] |
Mon, 09 May 2011 11:21  |
 |
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  |
ThomasG
Messages: 3212
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 $
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
