8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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...

Back to the Top.