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
> ... 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
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.
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 ?
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
select XML_CONTENT INTO v_clob from table1 ;
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 )
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
From the link I included in my first response
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.