XML parsing failed on Oracle

Question

I'm using a query on a oracle db which extracts values from clobs using the xmltype function and an xpath. Unfortunately the query fails and reports a 'XML Parsing failed'. I assume that one of the strings in the clob is not a valid XML. How to determine which row is affected?

I added rownum to the original query and the parsing failed error simply disappeared... — paweloque

Hugo Hugo · Accepted Answer · 2012-07-18T14:18:47

One solution would be to perform the query on a smaller bunch of data and slowly decrease the amount of records by just adding a condition similar to this:

AND id BETWEEN 20 AND 2000

Another way would be to fetch the records one after another with a cursor like this:

DECLARE
  CURSOR cur IS 
    /* Your SQL goes here */
    SELECT * FROM xx_invalid_row_t;

  table_row xx_invalid_row_t%ROWTYPE; /* change the table name*/
  row_count NUMBER :=0;
BEGIN
  OPEN cur;
  LOOP
    row_count := row_count + 1;
    FETCH cur INTO table_row;
    IF cur%NOTFOUND THEN
      EXIT;
    END IF;
  END LOOP;
  CLOSE cur;

  dbms_output.put_line('No errors here');
EXCEPTION
 WHEN OTHERS THEN
   dbms_output.put_line('Error on row '||row_count||' with message: '|| sqlerrm);
   dbms_output.put_line('Last successfully fetched row has the id:'||table_row.id);
END;

if you stumble up on a more straight forward way please let me know.

XML parsing failed on Oracle

1 Answers