5 Replies Latest reply on Mar 11, 2019 4:02 PM by Jason_(A_Non)

    How to fetch that large XML more than 70K chars data in xmltype?

    SonalP

      Hi Guys,

       

       

      I have a table with a clob column storing the large XML strings (100 > length > 70,000) in each cell. I am trying to fetch the values between the XML tags in the columns as shown in the below SQL.

       

       

      I'm receiving the following error message while performing this operation-> ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

       

       

      How to fetch that large XML data??

       

       

      Regards,

      Mukund

       

       

      =============================================================================================================================

      declare

      cursor cur1 is

      select clobData -- XML String with length > 70,000 characters

      , supportingData

      from clobTable;

       

      clob_dt clobTable.clobData%type;

      sup_dt clobTable.supportingData%type;

       

      xmlTagCount number;

      i number;

      cur2           sys_refcursor;

       

      col1 varchar(128);

      col2 varchar(128);

      begin

      open cur1;

       

      <<outerloop>>

      loop

      fetch cur1 into clob_dt, sup_dt;

       

      exit when cur1%NOTFOUND;

       

      begin

      select count(*)

      into xmlTagCount

      from (

      xmltable(

      xmlnamespaces(

      'http://www.abc.com/testworkflowmodel/8.1/dms' as "tns1"

      , default ''

      )

      , '/tns1:mainTag/*:subTagWithNullNamespace'

      passing xmltype.createxml(clob_dt)

      columns

      column1       varchar2(128 char) path 'column1_path',

      column2       varchar2(128 char) path 'column2_path'

      ) xt

      );

       

      open cur2 for

      select sup_dt as col_sup_dt, xt.*

      from (

      xmltable(

      xmlnamespaces(

      'http://www.abc.com/testworkflowmodel/8.1/dms' as "tns1"

      , default ''

      )

      , '/tns1:mainTag/*:subTagWithNullNamespace'

      passing xmltype.createxml(clob_dt) --XML String with length > 70,000 characters

      columns

      column1       varchar2(128 char) path 'column1_path',

      column2       varchar2(128 char) path 'column2_path'

      ) xt

      );

       

      i := 0;

       

      <<innerloop>>

      while (i < xmlTagCount) loop

      fetch cur2 into col1, col2;

       

      dbms_output.put_line(col1||','||col2);

       

      i := i + 1;

       

      end loop innerloop;

      close cur2;

         

      end loop outerloop;

      close cur1;

      end;

      /

      ----------------------------------------------------------------------------------------------------------------------------

      Error Code: -19279,

      Error Message: ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

      =============================================================================================================================

       

       

      If i try to execute the SQL under "cur2" cursor separately, i'm receiving the different error-> ORA-01704: string literal too long.

       

       

       

       

      select 12345 as col_sup_dt, xt.*

      from (

      xmltable(

      xmlnamespaces(

      'http://www.abc.com/testworkflowmodel/8.1/dms' as "tns1"

      , default ''

      )

      , '/tns1:mainTag/*:subTagWithNullNamespace'

      passing xmltype.createxml('XML String with length > 70,000 characters')

      columns

      column1       varchar2(128 char) path 'column1_path',

      column2       varchar2(128 char) path 'column2_path'

      ) xt

      );

      ----------------------------------------------------------------------------------------------------------------------------

      ORA-01704: string literal too long

      01704. 00000 -  "string literal too long"

      *Cause:    The string literal is longer than 4000 characters.

      *Action:   Use a string literal of at most 4000 characters.

                 Longer values may only be entered using bind variables.

      =============================================================================================================================

       

       

       

       

      However, when i tried to execute the same scripts on comparatively small length data like 1600, i'm getting the correct results

        • 1. Re: How to fetch that large XML more than 70K chars data in xmltype?
          Jason_(A_Non)

          Going back to my response back over in

          Unable to fetch data from nested null namespace using XMLTABLE

          You've not provided any XML for people to work with.  The first issue is

          ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

          That means one of the XPaths is encountering two (or more) matches when it was expecting to find only one.  Without seeing some representative XML, no one can help you.  You say you are bound by policy to not provide real data, well fine.  Take real data and alter it so it is no longer real.

           

          Other than an old system (10.2 or earlier), why are you storing XML data into a CLOB?

           

          As for your code, you are using cur1, Select count(*) and cur2 (which does the same thing as the select count(*)) to replicate the single SQL statement I wrote for you.  Focus on using that to find and fix the  ORA-19279: XPTY0004 error first without using the approach you are going down.

          1 person found this helpful
          • 2. Re: How to fetch that large XML more than 70K chars data in xmltype?
            Mustafa KALAYCI

            without a sample data we can not say anything about what is wrong but by looking error you got, you have an xml which has same tags repeating under a main tag like:

            with myxml as (
              select xmltype('
            <a>
              <b>1</b>
              <c>a</c>
              <b>2</b>
              <c>b</c>
            </a>') xml_data
            from dual)
            select *
            from myxml, xmltable('/a' 
                          passing xml_data
                           columns
                             b number path 'b',
                             c varchar2(80) path 'c');
            
            

            so I deal with something like that many times ago and I solved it like this:

            with myxml as (
              select xmltype('
            <a>
              <b>1</b>
              <c>a</c>
              <b>2</b>
              <c>b</c>
            </a>') xml_data
            from dual)
            select d11.b,d22.c
            from (select d1.*, rownum as my_id
                  from   xmltable('/a/b' 
                          passing (select * from myxml)
                           columns
                             b number path '.') d1 ) d11,
                 (select d2.*, rownum as my_id
                  from   xmltable('/a/c' 
                          passing (select * from myxml)
                           columns
                             c varchar2(80) path '.') d2 ) d22 
            where d11.my_id = d22.my_id
                        ;
            
            
            1 person found this helpful
            • 3. Re: How to fetch that large XML more than 70K chars data in xmltype?
              Gaz in Oz

              If the number of elements are known beforehand then you can reference them directly.

              For example:

              with myxml as (
                 select xmltype(
              '<a>
                 <b>1</b>
                 <c>a</c>
                 <b>2</b>
                 <c>b</c>
              </a>') xml_data
                 from   dual)
              select d.*, x.*
              from   myxml d,
                     xmltable('/a'
                              passing xml_data
                              columns b1 number      path 'b[1]',
                                      b2 number      path 'b[2]',
                                      c1 varchar2(5) path 'c[1]',
                                      c2 varchar2(5) path 'c[2]'
                 ) x;
              
              XML_DATA             B1         B2 C1    C2
              ------------ ---------- ---------- ----- -----
              <a>                   1          2 a     b
                 <b>1</b>
                 <c>a</c>
                 <b>2</b>
                 <c>b</c>
              </a>
              
              
              1 row selected.
              
              1 person found this helpful
              • 4. Re: How to fetch that large XML more than 70K chars data in xmltype?
                SonalP

                Thanks Guys for all of your input.

                 

                I've got the cause of this issue for this.

                 

                The function "xmltype.createxml" supports only 4004 characters.

                 

                We need to program our logic in such a way that the function "xmltype.createxml" always get char stream less than 4005 characters

                 

                Please do let me know, if you have any better solution on this.

                • 5. Re: How to fetch that large XML more than 70K chars data in xmltype?
                  Jason_(A_Non)

                  No it doesn't.  It supports CLOBs.

                  https://docs.oracle.com/database/121/ARPLS/t_xml.htm#ARPLS71959

                  You didn't provide a version so I grabbed a random doc to show that it supports a CLOB.

                   

                  Also, if that was the issue, you would get an invalid XML error message because the data was truncated.  The ORA-19279: XPTY0004 error happens due to repeating tags as mentioned above.