I receive:
When attempting to execute:
select extractValue(
XMLType(
replace(myxmlcolumn,CHR(0),''
)
), '/xpath/to/data'
)
foo,bar,foobar
from sch.tab
WHERE cond='cond'
"myxmlcolumn" is of course a CLOB datatype, but contains XML. This error would make sense if I wasn't doing the replace() on the NULL. That and the error is intermittent (possibly do to the matching of my clauses). How would a null be reported when it should have been replaced before the XML cast?
10g Enterprise Edition Release 10.2.0.5.0, Toad for Oracle pro 10.6.1.3
edit: - also tried replacing with 'H' to prove it wasn't inserting a NULL back in (same error) - also tried replace(myxmlcolumn,'\n','' in case it didn't like the chr(0) (same error)
This is not an elegant solution, and I feel a bit dirty. From @jonearles answer it looks like a known bug. The simple solution is to NOT even attempt to cast to XML. Hopefully none of the advanced xpath was needed, in my case it was not.
Select dbms_lob.substr(myxmlcolumn, 10, dbms_lob.instr(myxmlcolumn,'<data>') + 6)
"data" was the element I was after, and it happens to always hold a value that is 10 long, and is the first and only occurrence of the element so... I got lucky.