4 Replies Latest reply: Sep 23, 2013 12:07 PM by Jason_(A_Non) RSS

    Oracle reading Large xml from Clob Column

    5100f8f8-5869-43b6-912b-a1a05bd6993f


      Hi ,

       

      I have a xml > 32 KB stored in a clob colum in oracle 10g , i am trying to extract the values by using the below piece of code

       

      DECLARE

      v_clob CLOB;

      v_xml_type XMLTYPE;

       

      BEGIN

      select XML_CONTENT INTO v_clob from table1 ;

      v_xml_type:=XMLTYPE(v_clob);

       

      I am trying to parse and extract values using XMLTable and inserting values in other table but when parsing through XMLTYPE it is throwing me below error

       

      ORA-31011: XML parsing failed

      ORA-19202: Error occurred in XML processing

      LPX-00216: invalid character 0 (0x0)

      Error at line 1

      ORA-06512: at "SYS.XMLTYPE", line 254

      ORA-06512: at line 8

       

      Same PLSQL block works if the xml size is <15 KB , i have gone through lot of forums and came to know that XMLTYPE loads xml into memory thats why erroring for large xml.

       

      Not able to find soution in any of threads how to handle this scenario i have seen similar threads but none with proper soltuion.

       

      Please help.

       

      Thank you.

        • 1. Re: Oracle reading Large xml from Clob Column
          Jason_(A_Non)

          First, the underlying cause of your error message is shown as

          > LPX-00216: invalid character 0 (0x0)

          If search via that, one of hits should be LPX-00216 error help

          So as that states, you have a null character in the particular XML.  Without knowing the XML/DB charactersets, another option is

          LPX-00216: invalid character 0 (0x0) - Urgent

           

          > ... came to know that XMLTYPE loads xml into memory ...

          All variables in PL/SQL are stored in memory, regardless of whether a VARCHAR2, CLOB, or XMLType.  I don't recall a 32k limit, but I do know there is a 64k limit.  See

          ORA-31167: 64k size limit for XML node

           

          Also, 10g is a marketing label covering from 10.1.0.1 through 10.2.0.5.  A lot changed from start to finish so please been specific and include the results of

          select * from v$version

          in the start of future posts.

          • 2. Re: Oracle reading Large xml from Clob Column
            odie_63

             

            Same PLSQL block works if the xml size is <15 KB , i have gone through lot of forums and came to know that XMLTYPE loads xml into memory thats why erroring for large xml.

             

            So how large is your XML in reality ?

            Don't focus on the 32k boundary, it's certainly not the cause of the error.

             

            As Jason's already pinpointed, you probably have a not so wellformed content.

            Could you check that, or even better, share one the problematic XMLs with us so that we see for ourselves ?

            • 3. Re: Oracle reading Large xml from Clob Column
              5100f8f8-5869-43b6-912b-a1a05bd6993f

              Thank you for your responses.

               

              My XML in reality it greater than 20 MB and less than 30 MB

               

              I have tried correcting my XML and now able to parse my XML with help of DOM Document and xslProcessor with below piece of code


              Where as when i try to read it using XMLTYPE and XMLTable with the above mentioned code

               

              DECLARE

              v_clob CLOB;

              v_xml_type XMLTYPE;

               

              BEGIN

              select XML_CONTENT INTO v_clob from table1 ;

              v_xml_type:=XMLTYPE(v_clob)

               

              for c in (SELECT x.* into detail1,detail2 FROM XMLTable ('for $i in /main/data/Details return $i'

                                 passing  v_xml_type columns

                                     detail1 VARCHAR2(200) path 'detail1 ',

                                       detail2 VARCHAR2(200) path 'detail2 '

                                  ) x  )

              Loop

              dbms_output.put_line(c.detail1);

              dbms_output.put_line(c.detail2);

              End Loop;

               

              I am getting below error now

              ORA-31167: XML nodes over 64K in size cannot be inserted

              ORA-06512: at "SYS.XMLTYPE", line 5

              ORA-06512: at line 8

               

              Once Again Thank you

              • 4. Re: Oracle reading Large xml from Clob Column
                Jason_(A_Non)

                From the link I included in my first response

                ORA-31167: 64k size limit for XML node

                one of the facts was

                In 10.x if we detect a node > 64K while parsing the document we throw an error.

                where we = oracle database.  Note that this means only a single node with more than 64K of text, not a node with children nodes.  The overall size of the XML does not matter, only the size of each individual node.  You will want to read through that whole post as lots of good info in it.

                 

                The best answer now is to go to 11.2 or 12.1, otherwise you end up doing clob manipulations to strip out the large node so that you can convert it to an XMLType.