3 Replies Latest reply: Mar 19, 2014 7:05 PM by Marco Gralike RSS

    Load and Read XML file size more than 4GB


      Hi All


      My environment is Oracle on Solaris and I have processes to work with XML file as below detail by PL/SQL

      1. I read XML file over HTTP port into XMLTYPE column in table.

      2. I read value no.1 from table and extract to insert into another table


      On test db, everything is work but I got below error when I use production XML file

           ORA-31186: Document contains too many nodes


      Current XML size about 100MB but the procedure must support XML file size more than 4GB in the future.


      Belows are some part of my code for your info.


      1. Read XML by line into variable and insert into table

         UTL_HTTP.read_text(http_resp, v_resptext, 32767);
         DBMS_LOB.writeappend (v_clob, LENGTH(v_resptext), v_resptext);

          END LOOP;




      2. Read cell value from XML column and extract to insert into another table

         CURSOR c_xml IS
         (SELECT  trim(y.cvalue)
         FROM XMLTAB xt,
         XMLTable('/Table/Rows/Cells/Cell' PASSING xt.XMLDoc
         cvalueVARCHAR(50)PATH '/') y;






         OPEN c_xml;
         FETCH c_xml INTO v_TempValue;
         <Generate insert statement into another table>
         EXIT WHEN c_xml%NOTFOUND;
         CLOSE c_xml;




      And one more problem is performance issue when XML file is big, first step to load XML content to XMLTYPE column slowly.

      Could you please suggest any solution to read large XML file and improve performance?


      Thank you in advance.