How to perform Schema Registration and XML Validation in Oracle ?

Problem Statement:
You want to enforce XML schema validity on XML data stored in your database.

Solution:

Oracle provides the DBMS_XMLSCHEMA.REGISTERSCHEMA function to define XML schemas within the Oracle database. Inorder to validate the xml data generated, we need to register the schema. While registering the schema, the format must match the xml format generated or register the schema based on how you want to generate the xml .

The registration provides two key features. First, it allows Oracle to identify the external location or locations from which it can source the schema. Second and most important, REGISTERSCHEMA parses the schema for syntactical correctness and completeness.

The REGISTERSCHEMA function can take the XML schema from a numerous locations, such as the text presented as a VARCHAR, as well as from a BFILE or URI reference.

 Given below are the steps to perform xml schema validations.

1. Register the schema

DECLARE
  l_schema CLOB;
BEGIN

l_schema := '<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<!-- definition of simple elements -->
<xs:element name="customer_id" type="xs:positiveInteger"/>
<xs:element name="email" type="xs:string"/>
<xs:element name="name" type="xs:string"/>
<xs:element name="order_id" type="xs:positiveInteger"/>
<xs:element name="status" type="xs:string"/>

<!-- definition of complex elements -->
<xs:element name="order">
<xs:complexType>
<xs:sequence>
<xs:element ref="order_id"/>
<xs:element ref="status"/>
</xs:sequence>
</xs:complexType>
</xs:element>

<xs:element name="customer">
<xs:complexType>
<xs:sequence>
<xs:element ref="customer_id"/>
<xs:element ref="email"/>
<xs:element ref="name"/>
<xs:element ref="order" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'

--In SCHEMAURL , provide the schema name of your choice
DBMS_XMLSCHEMA.REGISTERSCHEMA(SCHEMAURL       => 'xml_schema_test.xsd',
                                SCHEMADOC       => l_schema,
                                LOCAL           => TRUE,
                                GENTYPES        => FALSE,
                                GENTABLES       => FALSE,
                                ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE);


END;

Check if the schema is created.

Example

SELECT schema_url FROM user_xml_schemas;

Output

-- Output
xmtest_schema.xsd

• Just in case if the schemas had to be dropped. Refer below code

3) If schema created needs to be dropped.

BEGIN
     DBMS_XMLSCHEMA.DELETESCHEMA(
     SCHEMAURL=> 'xml_schema_test.xsd',
     DELETE_OPTION => DBMS_XMLSCHEMA.DELETE_CASCADE_FORCE);
END;

• Validate the xml against the schema created using XMLISVALID function.

XMLISVALID: XMLISVALID checks whether the input XMLType_instance conforms to the relevant XML schema. If the schema is valid,the result is 1 else 0.

Example

CREATE TABLE t1 ( xml XMLTYPE);

INSERT INTO t1 VALUES ('<?xml version="1.0"?>
<customer>
   <customer_id>134</customer_id>
   <email>taylor.cauchon@internalmail</email>
   <name>Taylor Cauchon</name>
   <order>
      <order_id>921</order_id>
      <status>COMPLETE</status>
   </order>
   <order>
      <order_id>485</order_id>
      <status>COMPLETE</status>
   </order>
   <order>
      <order_id>1528</order_id>
      <status>COMPLETE</status>
   </order>
   <order>
      <order_id>1672</order_id>
      <status>COMPLETE</status>
   </order>
   <order>
      <order_id>1319</order_id>
      <status>COMPLETE</status>
   </order>
</customer>
');

SELECT XMLISVALID(xml, 'my_schema.xsd') AS is_valid
  FROM t1;