Results 1 to 8 of 8

Thread: How to load xml file to oracle table

  1. #1
    Junior Member
    Join Date
    Jun 2012
    Posts
    1

    How to load xml file to oracle table

    Hi,

    I am using Oracle 9i r2 database and I have an XML file in d: of my machine. I want to try xml data load into a table. How to do this?

    Thanks,

  2. #2
    Member
    Join Date
    May 2012
    Posts
    93
    Not sure what is your XML file structure and what are the fields you want to define for the new table. I am going with my own example, please change as per your need.

    1) Create a Virtual directory after connecting to Oracle database. Here below /var/tmp is the directory on the Oracle Server. If you have file on your local system, you need to
    copy that to Oracle server host under /var/tmp

    SQL> CREATE directory my_xmldir AS '/var/tmp';

    2) Let us say “emp_file.xml” is the file I copied under /var/tmp

    3) Let us create target table with name “Emp_XML” with fields - ID, empName, empSal and empJoinDate.

    create table emp_xml
    (
    id number,
    empname varchar2(40),
    empsal number,
    empjoindate date
    )

    Then, you can see your XML file data using below query

    Code:
    SELECT XMLTYPE(bfilename('MY_XMLDIR', 'emp_file.xml'), nls_charset_id('UTF8')) xml_data FROM dual
    And, to load the xml file into the table created, you need to run INSERT statement as below:

    Code:
    INSERT INTO emp_XML(ID,empName,empSal,empJoinDate)
    WITH t AS (SELECT xmltype(bfilename('MY_XMLDIR','emp_file.xml'), nls_charset_id('UTF-8')) xmlcol FROM dual)
    SELECT
    extractValue(value(x),'/ROW/@ID') empid
    ,extractValue(value(x),'/ROW/EmpName') empname 
    ,extractValue(value(x),'ROW/EmpSal') empsal
    ,extractValue(value(x),'ROW/JoinDate') empjoindate
    FROM t,TABLE(XMLSequence(extract(t.xmlcol,'/ROWSET/ROW'))) x;

  3. #3
    Member
    Join Date
    May 2012
    Posts
    35
    Hi krkanth
    Could you pls share Emp_file.xml , so that I can try on my PC \\

  4. #4
    Member
    Join Date
    May 2012
    Posts
    93
    Code:
    $cd /var/tmp/
    [/var/tmp]>cat emp_file.xml
    <ROWSET>
      <ROW ID="10">
        <EmpName>Gabbar</EmpName>
        <EmpSal>10000</EmpSal>
        <JoinDate>03-Apr-2006</JoinDate>
    <ROW>
      <ROW ID="20">
        <EmpName>Singh</EmpName>
        <EmpSal>20000</EmpSal>
        <JoinDate>05-May-2007</JoinDate>
    </ROW>
    </ROWSET>

  5. #5
    Junior Member
    Join Date
    Feb 2013
    Posts
    2
    Hi Kanth,
    How to create an .xml file with the SQL QUERY? I have tried select dbms_xmlgen.getxml('select * from hz_parties where rownum <=10') from dual and it worked showing me the xml data. But i want that xml data to be loaded into an xml file so that other external process can pick that file..

    I have created a table 1) create table test_clob(rtclob clob)
    2) insert into test_clob select dbms_xmlgen.getxml('select * from hz_parties where rownum <=10');

    i have the xml data stored in test_clob but not sure how to put the data into an xml file.. Could you please help me with some ideas how to perform it.

    Thanks
    Ravi.

  6. #6
    Senior Member vijay's Avatar
    Join Date
    May 2012
    Location
    Hyderabad
    Posts
    515
    @rpampana,

    Oracle has the utl file package, which can be used to read data from files and write data into files. Check the following link for more details.
    UTL_FILE

    I hope this will help you.

  7. #7
    Junior Member
    Join Date
    Feb 2013
    Posts
    2
    Hi Vijay,
    I tried using utl_file for xml data to load in, but not going into the file as expected.. The xml output with all the elements should go exactly the same into the file but through UTL_FILE, I couldn't acheive that.

    The xml output is huze and it is unable to load the data with UTL_FILE due to buffer size issues
    Last edited by rpampana; 02-28-2013 at 02:42 PM.

  8. #8
    Senior Member vijay's Avatar
    Join Date
    May 2012
    Location
    Hyderabad
    Posts
    515
    Try the following methods...

    using xml dbms_xslprocessor

    Code:
    drop directory XMLSTORE;
     
    CREATE directory XMLSTORE AS 'C:\2012\XML';
     
    declare
     var sys_refcursor;
    BEGIN
       open var FOR 
         SELECT * FROM 
         ( SELECT rownum FROM dual connect BY level < 25 );
     dbms_xslprocessor.clob2file( xmltype( var ).getclobval( ) , 'STORE','temp.xml');
     END;
    / 
     
    SELECT extract((XMLTYPE(bfilename('XMLSTORE','temp.xml'),NLS_CHARSET_ID('AL32UTF8'))),'*') AS "XML"
    from   dual;

    Using DBMS_XMLDOM.WRITETOFILE

    Code:
     
    declare
    	var sys_refcursor;
    	doc DBMS_XMLDOM.DOMDocument;
    begin
       open var for 
          select * from 
            ( select rownum from dual connect by level < 25 );
          doc := DBMS_XMLDOM.NewDOMDocument(xmltype( var ));
          DBMS_XMLDOM.WRITETOFILE(doc, 'XMLSTORE/temp.xml');
    end;
    / 
     
    SELECT extract((XMLTYPE(bfilename('XMLSTORE','temp.xml'),NLS_CHARSET_ID('AL32UTF8'))),'*') AS "XML"
    from   dual;

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •