8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Validate an XML Document Against an XML Schema (XSD) in an Oracle Database
This article gives some simple examples of validating XML documents against a XML Schema (XSD) in an Oracle database.
For example data I've used a modified version of the example shown here.
Related articles.
Register an XML Schema
First we need to register the schema using the DBMS_XMLSCHEMA.REGISTERSCHEMA
procedure. This has a number of overloads, allowing you to specify the XML schema using a VARCHAR2
, BFILE
, BLOB
, CLOB
, XMLTYPE
or URIType
types. The parameter list is quite important. By default the REGISTERSCHEMA
procedure will create database types and object tables, allowing documents to be shredded into object tables. For complex XSD documents you might get thousands of objects created in your database schema. For a basic document validation this is unnecessary and messy, so check you are using the correct settings.
DECLARE l_schema CLOB; BEGIN l_schema := '<?xml version="1.0" encoding="UTF-8" ?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <!-- definition of simple elements --> <xs:element name="orderperson" type="xs:string"/> <xs:element name="name" type="xs:string"/> <xs:element name="address" type="xs:string"/> <xs:element name="city" type="xs:string"/> <xs:element name="country" type="xs:string"/> <xs:element name="title" type="xs:string"/> <xs:element name="note" type="xs:string"/> <xs:element name="quantity" type="xs:positiveInteger"/> <xs:element name="price" type="xs:decimal"/> <!-- definition of attributes --> <xs:attribute name="orderid" type="xs:string"/> <!-- definition of complex elements --> <xs:element name="shipto"> <xs:complexType> <xs:sequence> <xs:element ref="name"/> <xs:element ref="address"/> <xs:element ref="city"/> <xs:element ref="country"/> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="item"> <xs:complexType> <xs:sequence> <xs:element ref="title"/> <xs:element ref="note" minOccurs="0"/> <xs:element ref="quantity"/> <xs:element ref="price"/> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="shiporder"> <xs:complexType> <xs:sequence> <xs:element ref="orderperson"/> <xs:element ref="shipto"/> <xs:element ref="item" maxOccurs="unbounded"/> </xs:sequence> <xs:attribute ref="orderid" use="required"/> </xs:complexType> </xs:element> </xs:schema>'; DBMS_XMLSCHEMA.registerSchema(schemaurl => 'my_schema.xsd', schemadoc => l_schema, local => TRUE, gentypes => FALSE, gentables => FALSE, enablehierarchy => DBMS_XMLSCHEMA.enable_hierarchy_none); END; /
We can check the schema details using the USER_XML_SCHEMAS
view.
SELECT schema_url FROM user_xml_schemas; SCHEMA_URL -------------------------------------------------------------------------------- my_schema.xsd SQL>
With the schema registered, we can now validate XML documents against it.
The DELETESCHEMA
procedure can be used to un-register the schema. The DELETE_OPTION
parameter allows you to drop any dependent objects.
BEGIN DBMS_XMLSCHEMA.deleteschema( schemaurl => 'my_schema.xsd', delete_option => DBMS_XMLSCHEMA.delete_cascade_force); END; /
Validate XML Document (SCHEMAVALIDATE)
In the following PL/SQL example, we create an XMLTYPE
from some XML in a CLOB
, then call the SCHEMAVALIDATE
member procedure to test the XML against the XML schema.
DECLARE l_xml CLOB; l_xmltype XMLTYPE; BEGIN l_xml := '<?xml version="1.0" encoding="UTF-8"?> <shiporder orderid="889923"> <orderperson>John Smith</orderperson> <shipto> <name>Ola Nordmann</name> <address>Langgt 23</address> <city>4000 Stavanger</city> <country>Norway</country> </shipto> <item> <title>Empire Burlesque</title> <note>Special Edition</note> <quantity>1</quantity> <price>10.90</price> </item> <item> <title>Hide your heart</title> <quantity>1</quantity> <price>9.90</price> </item> </shiporder>'; l_xmltype := XMLTYPE(l_xml, 'my_schema.xsd'); l_xmltype.schemavalidate; END; / PL/SQL procedure successfully completed. SQL>
To see an example of a validation failure, rename the "name" tag to "name1". This is not part of the XML schema, so it will fail the validation.
DECLARE l_xml CLOB; l_xmltype XMLTYPE; BEGIN l_xml := '<?xml version="1.0" encoding="UTF-8"?> <shiporder orderid="889923"> <orderperson>John Smith</orderperson> <shipto> <name1>Ola Nordmann</name1> <address>Langgt 23</address> <city>4000 Stavanger</city> <country>Norway</country> </shipto> <item> <title>Empire Burlesque</title> <note>Special Edition</note> <quantity>1</quantity> <price>10.90</price> </item> <item> <title>Hide your heart</title> <quantity>1</quantity> <price>9.90</price> </item> </shiporder>'; l_xmltype := XMLTYPE(l_xml, 'my_schema.xsd'); l_xmltype.schemavalidate; END; / DECLARE * ERROR at line 1: ORA-30937: No schema definition for 'name1' (namespace '##local') in parent '/shiporder/shipto' ORA-06512: at "SYS.XMLTYPE", line 354 ORA-06512: at line 29 SQL>
Validate XML Document (XMLISVALID)
An alternative is to use the XMLISVALID
function.
Create a table to hold the XML we used in the previous tests.
CREATE TABLE t1 ( id NUMBER, xml XMLTYPE ); INSERT INTO t1 VALUES (1, '<?xml version="1.0" encoding="UTF-8"?> <shiporder orderid="889923"> <orderperson>John Smith</orderperson> <shipto> <name>Ola Nordmann</name> <address>Langgt 23</address> <city>4000 Stavanger</city> <country>Norway</country> </shipto> <item> <title>Empire Burlesque</title> <note>Special Edition</note> <quantity>1</quantity> <price>10.90</price> </item> <item> <title>Hide your heart</title> <quantity>1</quantity> <price>9.90</price> </item> </shiporder>'); INSERT INTO t1 VALUES (2, '<?xml version="1.0" encoding="UTF-8"?> <shiporder orderid="889923"> <orderperson>John Smith</orderperson> <shipto> <name1>Ola Nordmann</name1> <address>Langgt 23</address> <city>4000 Stavanger</city> <country>Norway</country> </shipto> <item> <title>Empire Burlesque</title> <note>Special Edition</note> <quantity>1</quantity> <price>10.90</price> </item> <item> <title>Hide your heart</title> <quantity>1</quantity> <price>9.90</price> </item> </shiporder>'); COMMIT;
Now we can use the XMLISVALID
function in SQL to test against the registered XML Schema. It returns a "1" if the XML is valid, and "0" if it isn't.
SELECT id, XMLISVALID(xml, 'my_schema.xsd') AS is_valid FROM t1; ID IS_VALID ---------- ---------- 1 1 2 0 SQL>
For more information see:
Hope this helps. Regards Tim...