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