Request:

select
  nvl (extractvalue (xmltype (p.src_doc, 871), '// *:' || pd.name),
    extractvalue (xmltype (p.src_doc, 871), '* / * [local-name () = "AdditionalData"] [* [local-name () = "Name"] = "' || pd.name || ' "] [1] / * [local-name () =" Value "] ')) as elm_value,
  pd.guid as param_guid,
  p.guid as pmnt_guid
 from
  payments p
   join services s
  on p.srv_guid = s.guid
   join parameters_definitions pd
  on s.guid = pd.srv_guid
   left outer join pmnt_elements_values ​​elm
  on p.guid = elm.pmnt_guid and pd.guid = elm.param_guid
 where
  elm.guid is null and p.src_doc is not null;

In principle, it parses XML correctly into clob , but sometimes an exception may occur:

ORA-31011: XML parsing failed
ORA-19202: error occurred while processing XML
LPX-00601: Invalid token in: '// *: PAYMENTTYPE'
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.

The problem is that some XML are correct, some are not, the table is quite large. I can’t give a single example of XML either, since all XML documents are correct within their schema, but for obvious reasons they have different meanings in nodes, different namespaces and attributes in some nodes.

Is there any way to bypass / ignore / handle XML parsing exceptions? Is it possible at least somehow to get an understanding of where exactly and on what the parser falls apart?

Version: Oracle Database 11g Enterprise Edition.

Answer 1, authority 100%

It is necessary to wrap all the logic for parsing XML into a function that will handle exceptions and write full information about errors, for example, into a table. Something like this:

create table parseErrors (guid raw (16), errm varchar2 (4000), created timestamp);
create or replace function myXmlParser (docGuid raw, doc clob) return varchar2 is
  procedure saveParseError (guid raw, errm varchar2) is
  pragma autonomous_transaction;
  begin
    insert into parseErrors values ​​(guid, errm, systimestamp);
    commit;
  end;
begin
  - ** here is the necessary logic in which the error might occur **
  return xmlType (doc) .getStringVal ();
exception when others then
  saveParseError (docGuid, sqlerrm || chr (10) || sys.dbms_utility.format_error_backtrace ());
  return 'errorneous';
end myXmlParser;
/

Sample for test (instead of sys_guid () there should be a column with GUID from the request):

select myXmlParser (sys_guid (), '& lt; row & gt; some data & lt; / row & gt;') from dual union all
select myXmlParser (sys_guid (), '& lt; / row & gt; illegal XML syntax & lt; / row & gt;') from dual
;

will output:

RESULT
------------------------
& lt; row & gt; some data & lt; / row & gt;
errorneous

What were the errors:

set lines 999
col errm for a40 wrapp
select * from parseErrors;
GUID ERRM CREATED
-------------------------------- ------------------ ---------------------- ---------------------------- -
7106268B980F02D3E0530A01A8C04FBB ORA-31011: XML parsing failed 2018-07-15 12: 32: 41,204757000
                 ORA-06512: at "SYS.XMLTYPE", line 272
                 ORA-06512: at "DB.MYXMLPARSER", line 10

PS There is little information in the question on the localization of the cause of the error, but I think links to similar topics here and here will be helpful .