Results 1 to 5 of 5
  1. #1
    Join Date
    May 2010
    Posts
    56

    Unanswered: Most efficient way to load XML file data into tables

    I have a complex XML file running into MBs. I want to load it's data into 7-8 tables.
    Which way will be better:

    1) Use SQL Loader to actually load directly into the 7-8 tables directly by modifying the control card.
    Is this really possible and feasible? I am not even sure about it

    2) Load data as XML Type in a table and register it. Then extract from there to load into various tables.

    Please help. I have to find the most efficient way of doing it.

    Regards,
    Sudhir

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Please help. I have to find the most efficient way of doing it.
    run tests doing it both way & select the way that completes in the least amount of time
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    No need to store it in a table do something like this.

    Code:
    PROCEDURE update_stuff (
      p_xmltext   IN CLOB
    ) IS
      v_xml            XMLTYPE;
    BEGIN
      v_xml := XMLTYPE(p_xmltext);
    
      MERGE INTO table1 t
      USING (
        SELECT * 
        FROM XMLTABLE('/root/path_to/table1',
          PASSING v_xml
          COLUMNS ...
      ) x ON (t.pk = x.pk) 
      WHEN MATCHED ...;
    
      MERGE INTO table2 t
      USING (
        SELECT * 
        FROM XMLTABLE('/root/path_to/table2',
          PASSING v_xml
          COLUMNS ...
      ) x ON (t.pk = x.pk) 
      WHEN MATCHED ...;
    END;

  4. #4
    Join Date
    May 2010
    Posts
    56
    Thanks anacedent and artacus72.

    Also I want to know how can I directly load XML File data from SQL Loader into the 7-8 relational tables I have.

    Regards,
    Sudhir

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by sudhirc212 View Post
    Also I want to know how can I directly load XML File data from SQL Loader into the 7-8 relational tables I have.
    Check out this: http://www.oracle-base.com/articles/10g/LoadLobDataUsingSqlLoader.php

    More details are here: http://tinyurl.com/2bzqjgw

Posting Permissions

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