ORA-31011: XML parsing failed invalid character 3 (U 0003)-CodePudding

col1 is CLOB datatype contains XML data. Table 1 contains lot of xml data rows.

select xml_type(col1) from table1

Above query returns list of rows without any issues but when we scroll the rows in SQL developer it is throwing the below error. So we want to know which row in col1 causing this issue.

ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 283
LPX-00217: invalid character 3 (U 0003)
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

CodePudding user response：

By default, SQL Developer will fetch the first 50 rows. What you see is SQL Developer lazy-loading the rest of the rows and finding a row where there is a syntax error in the XML.

You can use a simplified version of this answer to find the errors:

WITH FUNCTION test_xml(v_xml clob) RETURN NUMBER
IS
  v_type XMLTYPE;
BEGIN
  v_type := XMLTYPE(v_xml);
  return 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;
SELECT *
FROM   table_name
WHERE  test_xml(string_column) = 0;

Or, if you are on an earlier Oracle version that does not support declaring functions in SQL:

CREATE FUNCTION test_xml(v_xml clob) RETURN NUMBER
IS
  v_type XMLTYPE;
BEGIN
  v_type := XMLTYPE(v_xml);
  return 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;
/

then:

SELECT *
FROM   table_name
WHERE  test_xml(string_column) = 0;

db<>fiddle here