jvoconnell
asked on
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\Mill iman\Outpu t 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
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\Mill
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
ASKER
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
I used your create table script. I ran it against 10.2.0.3.
What is your Oracle version (all 4 numbers)?
What is your Oracle version (all 4 numbers)?
ASKER
10.2.0.4
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\Mill iman\Outpu t File\Return_File.xml'?
For grins, can you copy the file locally and remove '\\dw2\e$\data\ARDENT\Mill
ASKER
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
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
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I can't believe I missed that. Very embarrasing. Thank you for the help....again!!!
No problem. Glad to help.
I mis-read ALL the time!
I mis-read ALL the time!
docs.oracle.com/cd/B28359_
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\Mill
) TERMINATED BY EOF
)
begindata
0