Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In trying to extract a subset of data from a Oracle DB table, one of the columns is XMLType, which is extracted with xmlquery() for relevant information.

The problem is that the XML data is from an outside source, which often gives us malformed XML with unescaped special characters.

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00244: invalid use of less-than ('<') character (use &lt;)
Error at line 1
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.

It would be ok for my purposes to ignore these rows.

How can I ignore these parse errors and have Oracle continue on with the SQL statement?


share|improve this question
up vote 2 down vote accepted

I don't think you can directly. I don't know of a way of putting the XML parser into a sort of "lax" mode where it would ignore validation errors, and you can't catch exceptions inside plain SQL.

One approach to deal with this (except harassing your outside source so that they produce valid XML, which is the solution) would be to validate the XML as you store it, and only process data that has been validated. You can do that simple PL/SQL code. Here's a minimal example of one way to approach this.

Sample table and data:

create table foo(id int, xml clob, valid int default null);
insert into foo(id,xml) values (1, '<?xml version="1.0"?><root><a foo="b"></a></root>');
insert into foo(id,xml) values (2, '<?xml version="1.0"?><root><a foo="<"></a></root>');

Validation function:

create function is_valid_xml(xml_param clob)
return int
  scratch xmltype;
  select xmltype(xml_param) into scratch from dual;
  return 1;
  when others then
    return 0;

Applying the validation function

SQL> update foo set valid = is_valid_xml(xml) where valid is null;
2 rows updated.

SQL> select id, valid from foo;
 1 |     1
 2 |     0

You can then query/report based your usual criteria plus the valid column.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.