How to Register XML Schemas with DBMS_XMLSCHEMA

An XML schema, or XSD, defines the structure and rules of an XML document type. Registering it with DBMS_XMLSCHEMA.REGISTERSCHEMA makes it known to the database under a URL, so documents can be checked against it, and XMLTYPE columns can be restricted to it.

Code for This Guide

The main examples are in the examples/pkg-xml folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

dbms_xmlschema.registerschema(schemaurl => 'url', schemadoc => 'xsd text',
                              local => true, gentypes => false, gentables => false);

xmltype(...).createschemabasedxml('url').isschemavalid()   -- 1 or 0
dbms_xmlschema.deleteschema('url', dbms_xmlschema.delete_cascade_force);

LOCAL TRUE registers the schema for the current user only. GENTYPES and GENTABLES FALSE keep Oracle from creating object types and tables for it.

Validate Gate Changes

The schema requires a flight and a gate, where the gate is a letter from A to D followed by one or two digits.

Example:

begin
  dbms_xmlschema.registerschema(
    schemaurl => 'http://nimbus.example/gate.xsd',
    schemadoc => '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
                    <xs:element name="gateChange">
                      <xs:complexType><xs:sequence>
                        <xs:element name="flight" type="xs:string"/>
                        <xs:element name="gate">
                          <xs:simpleType><xs:restriction base="xs:string">
                            <xs:pattern value="[A-D][0-9]{1,2}"/>
                          </xs:restriction></xs:simpleType>
                        </xs:element>
                      </xs:sequence></xs:complexType>
                    </xs:element>
                  </xs:schema>',
    local     => true, gentypes => false, gentables => false);
end;
/
select xmltype('<gateChange><flight>NM150</flight><gate>B12</gate></gateChange>')
         .createschemabasedxml('http://nimbus.example/gate.xsd').isschemavalid() as good,
       xmltype('<gateChange><flight>NM150</flight><gate>Z99</gate></gateChange>')
         .createschemabasedxml('http://nimbus.example/gate.xsd').isschemavalid() as bad
from   dual;

select schema_url, local from user_xml_schemas;

Output:

PL/SQL procedure successfully completed.

   GOOD    BAD
_______ ______
      1      0

SCHEMA_URL                        LOCAL
_________________________________ ________
http://nimbus.example/gate.xsd    YES

B12 matches the pattern, so the first document is valid (1), and Z99 does not (0). USER_XML_SCHEMAS lists the registered schema as local. The example deletes it afterward.

Things to Know

• The schema URL is a name, not a web address the database fetches.
• Declaring a column as XMLTYPE with XMLSCHEMA and ELEMENT ties its documents to the registered schema.
• SCHEMAVALIDATE raises an error describing the problem, where ISSCHEMAVALID only returns 0.

Related Guides

• How to Validate JSON with DBMS_JSON_SCHEMA
• How to Use XMLTYPE Methods in PL/SQL

Conclusion

DBMS_XMLSCHEMA.REGISTERSCHEMA stores an XSD under a URL so XML documents can be validated against it with ISSCHEMAVALID or SCHEMAVALIDATE. Register schemas locally, and delete them with DELETESCHEMA when no longer needed.