Somewhere 😉 on this site is a post regarding how to validate against an XML schema and although it is not incorrect, you should not use the method isXMLValid for validation.
Instead one should use one of the member functions of XMLTYPE like schemaValidate() or isSchemaValid(), if not only you will get extra information about “the why” the XML content does not validate against the used XML Schema. I will show here some ways to do it.
Be aware that the XML schema must be present, registered (dbms_xmlschema.registerSchema), and not a reference outside the database (not supported and a security issue from database point of view). Check views USER_XML_SCHEMAS of ALL_XML_SCHEMAS for which XML Schemas are registered in the database (or you have enough privileges to see).
If the XMLType content exists, is stored in an XML Schema based object like an XMLType Object Relational table or column (aka the storage is associated with an XML Schema):
[code]
BEGIN
FOR getDocuments IN ( SELECT im.id
, im.metadata as XDATA
FROM ic_metadata im
WHERE rownum >= 10
)
LOOP
—
BEGIN
getDocuments.XDATA.schemaValidate();
dbms_output.put_line (‘XML document with ID ‘||getDocuments.id||’ is VALID’);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line (getDocuments.id||’ = INVALID => ‘||sqlerrm);
END;
—
END LOOP;
END;
/
[/code]
The actual method to focus on in the small piece of code is “schemaValidate()”. Because the column, “ic.metadata” in this case, is an XMLType Object Relational column, an XML Schema is already associated and restricting the content based on the XML Schema content. XMLType Object Relational storage is a schema based storage XMLType and therefore can only be created if it is associated with a in the database registered XML Schema.
The output of the piece of code, if the content is valid against the XML schema (and it should be otherwise the database would have rejected it during insert), will be:
[code]
XML document with ID 130001 is VALID
XML document with ID 130002 is VALID
XML document with ID 130003 is VALID
XML document with ID 100015 is VALID
XML document with ID 100016 is VALID
XML document with ID 100030 is VALID
XML document with ID 100031 is VALID
XML document with ID 100032 is VALID
XML document with ID 134038 is VALID
XML document with ID 150008 is VALID
[/code]
If the XML content, XMLType, is stored in, for example, a Securefile column or CLOB column, then no association with an XML Schema exists. That is, the database is not aware of such an association. In that case you will have to associate the XML content with the XML Schema of your choice.
Based on the example above, but now with XML content in a Binary XML Securefile XMLType column:
[code]
BEGIN
FOR getDocuments IN ( SELECT id
, bim.metadata as XDATA
FROM bck_ic_metadata bim
WHERE rownum < = 10
)
LOOP
--
BEGIN
getDocuments.XDATA.schemaValidate();
dbms_output.put_line ('XML document with ID '||getDocuments.id||' is VALID');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line (getDocuments.id||' = INVALID => ‘||sqlerrm);
END;
—
END LOOP;
END;
/
[/code]
And the output:
[code]
5524627 = INVALID => ORA-19030: Method invalid for non-schema based XML Documents.
5524625 = INVALID => ORA-19030: Method invalid for non-schema based XML Documents.
5524625 = INVALID => ORA-19030: Method invalid for non-schema based XML Documents.
5524623 = INVALID => ORA-19030: Method invalid for non-schema based XML Documents.
5524623 = INVALID => ORA-19030: Method invalid for non-schema based XML Documents.
5524621 = INVALID => ORA-19030: Method invalid for non-schema based XML Documents.
5524621 = INVALID => ORA-19030: Method invalid for non-schema based XML Documents.
5524619 = INVALID => ORA-19030: Method invalid for non-schema based XML Documents.
8534439 = INVALID => ORA-19030: Method invalid for non-schema based XML Documents.
8534466 = INVALID => ORA-19030: Method invalid for non-schema based XML Documents.
[/code]
Via the ORA-19030 message, the database is warning you that it can not find an associated XML Schema and therefore can’t validate the XML content of a valid method.
You can solve this via using the XMLType member function createSchemaBasedXML() to associate/create an registered XML Schema with the to be validated XML content.
[code]
BEGIN
FOR getDocuments IN ( SELECT id
, bim.metadata.createSchemaBasedXML(‘http://www.somewhere.nl/source/schemas/xsd/metadata.xsd’) as XDATA
FROM bck_ic_metadata bim
WHERE rownum < = 10
)
LOOP
--
BEGIN
getDocuments.XDATA.schemaValidate();
dbms_output.put_line ('XML document with ID '||getDocuments.id||' is VALID');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line (getDocuments.id||' = INVALID => ‘||sqlerrm);
END;
—
END LOOP;
END;
/
[/code]
The output will be something like the following or you will get an useful error message hinting WHY it doesn’t validate against the XML Schema.
[code]
XML document with ID 5524627 is VALID
XML document with ID 5524625 is VALID
XML document with ID 5524625 is VALID
XML document with ID 5524623 is VALID
XML document with ID 5524623 is VALID
XML document with ID 5524621 is VALID
XML document with ID 5524621 is VALID
XML document with ID 5524619 is VALID
XML document with ID 8534439 is VALID
XML document with ID 8534466 is VALID
[/code]
An alternative way, to associate the XML Schema is via XMLTYPE itself.
See the following examples:
[code]
declare
xdata xmltype;
begin
select xmltype(‘
into xdata
from dual;
end;
/
[/code]
…and now via the XMLTYPE constructor itself.
[code]
declare
xdata xmltype;
begin
select xmltype(‘
into xdata
from dual;
end;
/
[/code]
Describe XMLTYPE => XMLTYPE constructor function XMLType(xmlData IN …, schema IN varchar2 := NULL) etc.
Both will give the same output, mentioning that element “root” is not part/defined in the registered XML Schema purchaseOrder.xsd
[code]
ORA-31043: Element root is not defined in schema http://localhost:80/xsd/purchaseOrder.xsd.
ORA-06512: in SYS.XMLTYPE
31043. 00000 – Element ‘%s’ not globally defined in schema ‘%s’
*Cause: The specified element name has not been defined at the top level
of the XML schema (i.e. globally). Elements must be defined
globally to be the root of an XMLType object.
*Action: Check the XML schema definition to make sure the specified
element name has been defined at the top level.
[/code]
Do only use XMLIsValid for constraints. Functions like schemaValidate(), isSchemaValid() are way more useful and informative regarding why some XML content is not valid against an XML Schema.
HTH
Read more here on “Validating XMLType Instances”.
Marco,
A few comments if you allow me 😉
> if the content is valid against the XML schema (and it should be otherwise the database would have rejected it during insert)
Not entirely true. With OR storage, Oracle only performs a partial validation (such as datatype and size checking against the internal object structure).
> If the XML content, XMLType, is stored in, for example, a Securefile column or CLOB column, then no association with an XML Schema exists.
Unless the XMLType has been explicitly schema-based of course.
> An alternative way, to associate the XML Schema is via XMLTYPE itself.
Or, if the XML document possesses an xsi:noNamespaceSchemaLocation or xsi:SchemaLocation attribute referencing a registered XML schema, then a simple call to createSchemaBasedXML() is sufficient.
1) It was not my intent to be complete, but get the initial steps out. There is a lot going on and/or possible via all the different methods and/or storage options. But you are right, it is “not entirely” true. OR storage has by default a “lax” or “lazy” validation only.
As stated in (and demonstrated) on the XMLDB FAQ pages for XML validation (https://community.oracle.com/thread/418458):
“Lax” validation takes place whenever a schema based document is converted from it’s textual representation into the XML DB internal object model. Errors caught by this validation will be prefixed with ‘ORA’.”
“Stict’ validation takes place when the XMLType methods schemaValidate() or isSchemaValid() are invoked.
2) Yep 🙂 / Indeed. If the storage type is schema-based then validation will kick in as well. And/or other variations or options like XDB event methods that might get triggered via, for example, “schemalocation mapping”.
http://docs.oracle.com/database/121/ADXDB/xdb22pro.htm#ADXDB5523
http://docs.oracle.com/database/121/ADXDB/appaman.htm#ADXDB5710
3) As said, no intent to target with this post all possibilities or options but more to layout the basics regarding schemaValidate() and/or isSchemaValid()