How to disable XML DTD validation in Oracle DB?

Question

Hi

I am using following statement in select portion of the query:

extract(XMLTYPE(doc.payload),'/SHOW_SHIPMENT_005/DATAAREA/SHOW_SHIPMENT/SHIPMENT/SHIPITEM/DOCUMNTREF/DOCUMENTID')

it works well if the doc.payload contains XML without DTD declaration, but raises an error when DTD declaration is present as Oracle tries to validate the document but cannot find the DTD.

How to disable the XML validation for this query? I don't want to affect my session setting nor global system setting in this matter.

Thanks in advance.

Answer 1

You can turn off validation when you create an XMLTYPE, constructor signature looks like this:

XMLType(
   xmlData IN varchar2,
   schema IN varchar2 := NULL,
   validated IN number := 0,
   wellformed IN number := 0)

So you can disable validation like this:

   extract(XMLTYPE(doc.payload, NULL, 1, 1),
'/SHOW_SHIPMENT_005/DATAAREA/SHOW_SHIPMENT/SHIPMENT/SHIPITEM/DOCUMNTREF/DOCUMENTID')

This will not help you though, if you have an external DTD reference. It'd still try to load it. Is it not possible to upload the DTD file into the XMLDB repository? That would be the simplest solution. If not, there is no 'nice' solution, you have to get rid of the DTD reference before creating the XMLTYPE:

   extract(XMLTYPE(REGEXP_REPLACE(doc.payload, '<!DOCTYPE[^<]*>', '')),
'/SHOW_SHIPMENT_005/DATAAREA/SHOW_SHIPMENT/SHIPMENT/SHIPITEM/DOCUMNTREF/DOCUMENTID')