Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2993
  • Last Modified:

load xml file with SQL Loader

Experts,

I want to import an XML file into Oracle using SQL Loader and then extract data once it's loaded. I've found some examples but I cant get it to work.

My xml file is -- ReturnFile.xml (I've attached a sample)


CREATE TABLE XML_INPUT
(
RAWXML XMLTYPE
)

My Contol File looks like this:

OPTIONS ( DIRECT=TRUE)
LOAD DATA
INFILE '\\dw2\e$\data\ARDENT\Milliman\Output File\Return_File.xml'


INTO TABLE "JVO0"."XML_INPUT"
APPEND
XMLTYPE
FIELDS TERMINATED BY ','
(
RAWXML LOBFILE(ReturnFile) TERMINATED BY EOF
)


When I run from the command line:

sqlldr jvo0/pw@company direct=true, control=C:\XML.CTL, log=c:\XML.LOG

I get this error:
SQL*Loader-350: Syntax error at line 9.
Expecting "(" , found "XMLTYPE".

Any assistance is appreciated.
ReturnFile.xml
0
jvoconnell
Asked:
jvoconnell
  • 6
  • 5
1 Solution
 
slightwv (䄆 Netminder) Commented:
Check the docs:
docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb25loa.htm#autoId5

I was able to load your file with:

OPTIONS ( DIRECT=TRUE)
LOAD DATA
INFILE *
INTO TABLE TAB1
APPEND
FIELDS TERMINATED BY ','
(
fill filler char(1),
RAWXML LOBFILE(CONSTANT '\\dw2\e$\data\ARDENT\Milliman\Output File\Return_File.xml'
) TERMINATED BY EOF
)
begindata
0
0
 
jvoconnellAuthor Commented:
Thank you very much. Do I have to also change the datatype for the column? I get a sql loader error 418-bad datafile datatype for column RAWXML
0
 
slightwv (䄆 Netminder) Commented:
I used your create table script.  I ran it against 10.2.0.3.

What is your Oracle version (all 4 numbers)?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
jvoconnellAuthor Commented:
10.2.0.4
0
 
slightwv (䄆 Netminder) Commented:
I didn't try it using UNC.  I saved the file to a local disk.

For grins, can you copy the file locally and remove '\\dw2\e$\data\ARDENT\Milliman\Output File\Return_File.xml'?
0
 
jvoconnellAuthor Commented:
copying the return_file.xml to the C: and using this syntax in the control still produces the error

OPTIONS ( DIRECT=TRUE)
LOAD DATA
INFILE *
INTO TABLE XML_INPUT
APPEND
FIELDS TERMINATED BY ','
(
fill filler char(1),
RAWXML LOBFILE(CONSTANT 'Return_File.xml'
) TERMINATED BY EOF
)
begindata
0
0
 
slightwv (䄆 Netminder) Commented:
Is the database you are loading into also 10g or might it be 9i?


askTom actually has a cleaner version and talks about this error but also says it is fixed in 10g.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:738791653185

Here is the updated control file base on the askTom link:

OPTIONS ( DIRECT=TRUE)
LOAD DATA
INFILE *
INTO TABLE TAB1
APPEND
FIELDS TERMINATED BY ','
(
lob_file filler char(50),
RAWXML LOBFILE(lob_file) TERMINATED BY EOF
)
begindata
q.txt
0
 
jvoconnellAuthor Commented:
Thanks for the help. It really is appreciated. I'm going to keep plugging away.

I looked at the askTom page you sent. I changed the datatype in the table from XMLTYPE to CLOB. THen tried the loader and got an error to use Trailing Null Cols in ctl file. When adding that I received sqlldr error 462 "error inserting LOB into column RAWXML" and error 646 "lob set to empty in column RAWXML"...and I get two empty rows

So it looks like changing the datatype to clob wasn't going down the right path.

I am using 10G but the sql loader is release 9.2.0.1 (i don't know if that has anything to do with it)

Thanks for the time.
0
 
slightwv (䄆 Netminder) Commented:
>>but the sql loader is release 9.2.0.1 (i don't know if that has anything to do with it)


It has everything to do with it.  The askTom article specifically points you to the solution:

That is why I asked it you were going against a 9i database.

Excerpt from the link above:


Followup   June 29, 2004 - 7pm Central time zone:

create tbl_xmlwork( docid number, xmldoc SYS.xmltype );


it is an issue with sqlldr in 9ir2 with the SYNONYM for xmltype -- it got "confused"

it was corrected in 10g
0
 
jvoconnellAuthor Commented:
I can't believe I missed that. Very embarrasing. Thank you for the help....again!!!
0
 
slightwv (䄆 Netminder) Commented:
No problem.  Glad to help.

I mis-read ALL the time!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now