This discussion is archived
2 Replies Latest reply: Nov 7, 2012 6:33 AM by Solomon Yakobson RSS

Load XML File using SQL Loader

953487 Newbie
Currently Being Moderated
Hi guys,

Need help on using SQL Loader to load an xml file using sql developer. I find some documents related to this but I can't seem to understand those clearly. But I was able to load an xml using its content directly in sql developer without referencing to the xml file. however, what i need is to have the xml loaded directly from the xml file since the contents will be prone to changes. TIA!
  • 1. Re: Load XML File using SQL Loader
    odie_63 Guru
    Currently Being Moderated
    XML DB Dev Guide : Loading XML Data using SQL*Loader

    Follow-up question : where does the file come from? client or server side?

    If the latter, you can directly use SQL and the XMLType constructor, for example :
    INSERT INTO my_table (my_id, my_xml_column) 
    VALUES (
      1,
      XMLType(bfilename('XML_DIR', 'my_file.xml'), nls_charset_id('AL32UTF8'))
    );
    Edited by: odie_63 on 7 nov. 2012 15:07
  • 2. Re: Load XML File using SQL Loader
    Solomon Yakobson Guru
    Currently Being Moderated
    And, if XML is on client side, you could use SQL*Loader.

    Control file:
    load data
      infile * "str '</contact>'"
    INSERT
       into table address_book
          FIELDS(
                 dummy1 filler char(2000) terminated by "<contact>",
                 contact_name char(2000) enclosed by "<contact_name>" and "</contact_name>",
                 address char(2000) enclosed by "<address>" and "</address>",
                 dummy2 filler char(2000) terminated by "</start>"
                )
    BEGINDATA
    <?xml version="1.0" encoding="UTF-8"?>
    <start>
    <contact><contact_name>Joe Shmoe</contact_name><address>1 Main St, Smallville USA</address></contact>
    <contact><contact_name>Jane Doe</contact_name><address>Unknown</address></contact>
    </start>
    Load:
    SQL> select  *
      2    from  address_book
      3  /
    
    no rows selected
    
    SQL> host sqlldr scott@orcl/tiger control=c:\temp\contact.ctl log=c:\temp\contact.log
    
    SQL> select  *
      2    from  address_book
      3  /
    
    CONTACT_NAME         ADDRESS
    -------------------- ----------------------------------------
    Joe Shmoe            1 Main St, Smallville USA
    Jane Doe             Unknown
    
    SQL> 
    SY.