In this example, we will see how to load XML data into XMLTYPE column in an Oracle table.
In one of my earlier posts, I'd described how to load BLOB type data into an Oracle table using SQL Loader. The link for that post is available here. To load XMLTYPE data, just assume that the column image_data is of XMLTYPE and not BLOB.
The rest of the process is the same. Prepare your XML data in separate xml files. You can reuse the same control file as given in the post and make sure that file has a valid path. When this control file is executed, SQL Loader will automatically push XML data into XMLTYPE field. If you are stuck or you are getting any errors in this process, post it on this blog and let us have a look..
Hi,
ReplyDeleteI tried the example as follows:
created the table:
create table PS_H1UK_CCXML_STG(
id number, file_name varchar2(100), xml_data xmltype)
then I created the .ctl file:
LOAD DATA
INFILE *
INTO TABLE PS_H1UK_CCXML_STG
REPLACE
FIELDS TERMINATED BY ','
(
id,
file_name,
xml_data XMLTYPE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,CCS_DELIVERY_TEST.XML
and then I run the OS command:
sqlldr psftadm/psftadm@crmshado control=load_xml_1.ctl
The next error I received:
SQL*Loader-350: Syntax error at line 9.
Expecting "," or ")", found "XMLTYPE".
xml_data XMLTYPE (file_name) TERMINATED BY EOF
Could you help please, do you know why that error has been raised?
Thank you!
@ Andreescu
ReplyDeleteI checked your control file..
You have to change the line
xml_data XMLTYPE (file_name) TERMINATED BY EOF
to
xml_data LOBFILE (file_name) TERMINATED BY EOF
..
it worked after doing this. If it does not work for you, let us know.
Is there any size restriction of the XML file to be loaded at a single go?
ReplyDeletecould upload the copy of the CCS_DELIVERY_TEST.XML file
ReplyDelete