SQL Loader - Load XML data into XMLTYPE column

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..

4 comments:

  1. Hi,
    I 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!

    ReplyDelete
  2. @ Andreescu

    I 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.

    ReplyDelete
  3. Is there any size restriction of the XML file to be loaded at a single go?

    ReplyDelete
  4. could upload the copy of the CCS_DELIVERY_TEST.XML file

    ReplyDelete