- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
- 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;
- Related Articles
- How to generate JSON data and perform Schema Validation in Oracle?
- How to extract Key XML Elements from an XML Document in Oracle?
- How to translate SQL data to XML in Oracle?
- How to store XML data in a table in Oracle?
- How to perform string aggregation/concatenation in Oracle?
- How to perform case-insensitive search in Oracle?
- Perform simple validation in MongoDB?
- How to convert XML to Json and Json back to XML using Newtonsoft.json?
- How to check Minlength and Maxlength validation of a property in C# using Fluent Validation?
- How to find and replace text in Oracle?
- How to insert and retrieve dates in Oracle ?
- How to work with XML and JSON in .NET?
- Difference between Star Schema and Snowflake Schema?
- How to identify blocked and blocking sessions in Oracle ?
- Difference between oracle golden gate and oracle active guard in the oracle
