pl/sql XMLTable INVALID_NUMBER error
Posted on 2012-12-21
I'm relatively new to pl/sql and xml.
I have an sql query that uses XMLTable to shred transaction details. When I run the query I get an INVALID_NUMBER error. If I comment out the 'Amount' element the query runs, so I'm assuming the problem lies here. I have no control over the content, so can't fix it. Is there any way to alter the XMLTable to either ignore the row with the error or do a decode to substitute zero when an invalid_number is found?
FROM mytable1 so,
XMLTABLE('//Charge' PASSING xmltype(so.xmlClob)
COLUMNS "GeneralChargeID" NUMBER PATH 'GeneralChargeID',
"Name" VARCHAR2(255) PATH 'Name',
"Status" VARCHAR2(25) PATH 'Status',
"Quantity" NUMBER PATH 'Quantity',
"Amount" NUMBER(10,2) PATH 'Amount',
"CreditFlag" CHAR(1) PATH 'CreditFlag'
) as CHARGES
I tried calling this statement, but the same error was thrown.
alter session set nls_numeric_characters=". ";