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:
- Use utl_file.fopen to open the file
- Loop through it to put the contents into a CLOB variable
- Insert the content of the CLOB variable into a SYS.XMLTYPE column
- 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