Reading XML in Oracle -2

By | April 26, 2016

Loading XML into Oracle might require some SQLLDR code. Of course, the standard insert is a possibility, but this might be slow if many XML files are to be loaded. From publications, I got the idea that the SQLLDR is the most recommended tool to do it. It does require some time and tuning but once, the script is ready, it might be re-used and it saves quite some time.
Using this SQLLDR vehicle, one might load a series of XML files into an Oracle table.
The code to do so is not really trivial. I used this table:

CREATE TABLE "SCOTT"."XML_TAB" 
   (	"XML_DATA" "XMLTYPE", 
	"ID" NUMBER(*,0)
   )

and the code to load XML files is as follows:

load data
infile *
append
into table XML_TAB
fields terminated by ';' 
(
 filename FILLER char(47),
 XML_DATA  lobfile(filename) terminated by eof,
 id  
)


begindata
c:\app\tom\product\12.1.0\dbhome_1\BIN\weg.xml;14
c:\app\tom\product\12.1.0\dbhome_1\BIN\weg1.xml;15
c:\app\tom\product\12.1.0\dbhome_1\BIN\weg2.xml;16


This file can be loaded by a SQLLDR command that may be something like:
SQLLDR CONTROL=C:\Users\Tom\Dropbox\Uitwisseling\xml.ctl LOG=sample.log, BAD=baz.bad, USERID=scott/binvegni, ERRORS=999, LOAD=500000, DISCARD=toss.dsc, DISCARDMAX=5