You can use the XMLIsValid function to verify XMLType rows in a table.
This returns 1 if the XML conforms and 0 if it doesn't.
Using the linked example:
BEGIN
dbms_xmlschema.registerSchema(schemaURL => 'xsd1.xsd',
schemaDoc => '<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="userdetails">
<xs:complexType>
<xs:sequence>
<xs:element name="username" type="xs:string"/>
<xs:element name="password" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>');
END;
/
create table t (
x xmltype
);
-- valid XML
insert into t values (XMLTYPE('
<userdetails xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<username>Fred</username>
<password>pass123</password>
</userdetails>'));
-- does not conform to XSD
insert into t values (XMLTYPE('
<userdetails xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<user>Fred</user>
<password>pass123</password>
</userdetails>'));
-- find rows that do not conform
select t.*
from t
where XMLIsValid(x, 'xsd1.xsd') = 0;
X
-------------------------------------------------------
<userdetails xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<user>Fred</user>
<password>pass123</password>
</userdetails>