2 Replies Latest reply: Nov 7, 2012 8:33 AM by Solomon Yakobson RSS

    Load XML File using SQL Loader

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