It's not the first time I write about ORA-19025, however I believe that what follows is an interesting and not so common situation.
Imagine you receive an XML file that have been working perfectly for 3 years and suddenly it throws out the infamous exception:
ORA-19025: EXTRACTVALUE returns value of only one node
After mumbling and rummaging in your bullet-proof, supertested code, you come up with a query that at least pinpoints the offending XML node:
The problem is that for 3 years the title element never contained child nodes other than a single text node, then suddenly, someone managed to enter that sup element. You can easily try yourself:
ORA-19025: EXTRACTVALUE returns value of only one node
A quick and dirty workaround consists in adding XPath's text() function:
X ----------------------------------------- Don't be by unwanted tags in a text node
This will keep you going, at the cost of stripping out the text inside the inner element(s). The problem with XML files is in that a stupid problem like that above makes unusable the whole lot even if the erroneous information is negligible.
If loosing some text is not an option, I fear that the only way forward is to process these nodes through a XSL transform that will strip out the tags and copy the text nodes inside.
May be I'll switch to this fancier option later on, for the moment I am just happy to keep the ball rolling without worrying too much about loosing an insignificant text node out of millions, in the end it's their fault if they added unexpected stuff inside that element.
See message translations for ORA-19025 and search additional resources.
2 comments:
How about the "node()" xpath function?
select extract(xmltype('blah blah foo blah '), '/e/node()').getStringVal()
from dual;
Hi,
yes you can also do that albeit you'll get also the unwanted tags.
It really depends on the usage you are going to make of the content.
Thanks
Flavio
Post a Comment