Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Best Way to Insert XML Data into Tables

3245956May 25 2016 — edited Jun 1 2016

I need to create a process to read XML files so the values can be inserted into potentially multiple tables with 1-Many relationships.  My approach to date is to:

  1. Use utl_file.fopen to open the file
  2. Loop through it to put the contents into a CLOB variable
  3. Insert the content of the CLOB variable into a SYS.XMLTYPE column
  4. Use ‘XML’ SQL to select values to insert into the final tables.  For example:

SELECT t.id, x.*

  FROM xml_table t,

       XMLTABLE ('/RFQ/RFQ_HEADER'

       PASSING t.xml_file

          COLUMNS requestor VARCHAR2(30) PATH 'requestor',

                  email VARCHAR2(30) PATH 'email') x

   WHERE t.id = 1;

I have a prototype working for inserting into a single table but am concerned with how big the CLOB can be. From what I am reading it can be 4GB, but there is a reference on-line about multiplying by block size to make it  8 to 128 TBs.  Is this accurate?


Also, is this a good way to do what I need to do?  I am trying to adhere to my keep it simple philosophy but I am new to all this and would really appreciate some feedback on the best way to do it.


Thanks

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 29 2016
Added on May 25 2016
6 comments
49,143 views