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 := '





























'

--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 ('

   134
   taylor.cauchon@internalmail
   Taylor Cauchon
   
      921
      COMPLETE
   
   
      485
      COMPLETE
   
   
      1528
      COMPLETE
   
   
      1672
      COMPLETE
   
   
      1319
      COMPLETE
   

');

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