How to handle malformed XML in EXTRACT called from SQL
Posted on 2007-10-04
I have a query EXTRACTing values from standalone UTF-8 xml in a varchar2 field e.g.
select (extract(xmltype(tr.txn_raw_data), '/FEE/FEEID/text()').GetStringVal()) as Foo
it falls over due to malformed XML in some rows. How do I handle this? I'm looking for some way of testing the contents of the column for wellformedness so that I can write something like:
select (case when >>isMalformed<<(...) then 'XXXX' else extract(....) end) as Foo
It has to be inlined code because I only have Select access to the DB.