Hi,
We're in the proces of migrating our 10.2.0.4 db to 11.2.0.3, and have discovered an issue with one of our queries - basically, we extract a node's text as a number, and if that node consists of whitespace only, it's now returning a 0 in 11.2.0.3 rather than the NULL it used to return in 10.2.0.4 (and earlier versions).
Here's the examples I've been working with:
select extract(xmltype('<root><test> </test></root>'), '/root/test/text()').getnumberval() from dual;
select extract(xmltype('<root><test>
</test></root>'), '/root/test/text()').getnumberval() from dual;
I've raised an SR with Oracle, due to the difference in behaviour from our 10g db, but in the meantime, I need to workaround this issue. Is there a neat way of removing whitespace from the node's text? I'd rather avoid having to convert the xmltype back to a clob to do a replace, or extracting the node as text and then trimming/replacing and converting it to a number, if at all possible.