My Oracle Support Banner

Primary Note for Oracle XML Database (XDB) Schema Registration (Doc ID 1316134.1)

Last updated on JANUARY 30, 2022

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 9.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.


This primary note provides information for DBA's on registering XML schemas in an XML Database (XDB). This note covers releases through 11.2. The note doesn't cover all aspects of registering schemas as it is a broad topic, however it gives a detailed introduction. For further details refer to links just below for the Oracle XML Database Developer's Guide documentation.


Where can documentation about the schema registration be found?





General Concepts:

What is an XML Schema?

XML schema unifies both document and data modeling. An XML schema defines the set of elements and attributes that exist for an XML document and defines the relationships between them. It can be used to describe the structure and various other semantics of conforming instance documents. XML Schema is a schema definition language written in XML. XML schema is used to declare which elements and attributes can be used and what kinds of element nesting, and datatypes are allowed in the XML documents being stored or processed. The XML schema definition language is simply an XML document that conforms to the standards defined by the XML Schema

The XML Schema is made up of the following components:

-- complexTypes which contain child elements and attributes
-- simpleTypes which contain scalar values (such as numbers,strings, and dates) but cannot not contain sub-elements.
-- Oracle XML Schema Annotations provides the ability to influence the objects/types and tables that are generated by the XML Schema registration process.

What is the difference between a schema document and an instance document?:

A XML Schema document is used to describe storage structures/mappings and language bindings of XML documents that conform to a schema. The term "instance document" is often used to describe an XML document that conforms to a particular XML schema. An instance document is the actual XML content/data (XML) that follows the rules of the XML Schema document (XSD).

What is the difference between an DTD and XSD?:

Document Type Definition (DTD) is a mechanism provided by XML 1.0 for declaring constraints on XML markup. The XML Schema Definition (XSD) serves a similar purpose to DTDs, but it is more flexible in specifying XML document constraints as it enables namespace technology, built-in datatypes and user-defined datatypes.

Explanation of Schema Registration:

Registering a schema creates the appropriate database storage structures to maintain the integrity of a schema within the database. To register an XML schema you must firstly provide the XMLSchema document and also a unique identifier in the form of a URL, which will be used by XML documents which claim to conform to this Schema. This URL will be provided in the root element of the instance document using either the noNamespaceSchemaLocation attribute or schemaLocation attribute as defined in the W3C XML Schema recommendation. XML schemas are registered using methods provided by PL/SQL package DBMS_XMLSCHEMA. Schemas can be registered as global or local schemas. Oracle XML DB provides a number of options for automatically generating default database objects and Java classes as part of the schema registration process.

Purpose of Registering a Schema:

Oracle XML DB Supports the use of schema for validation of instance documents. By default Oracle XML DB performs a minimum amount of validation when a storing an instance document. This minimal validation ensures that the structure of the XML document conforms to the structure specified in the XML schema.

Another purpose of registering a schema is for the defining of storage models. Prior to 11g, there were two options to store XML which included object-relational and CLOB based. In 11g, the binary XML storage was also introduced.

What steps are done when registering a schema:

-- Parse and validate the XML schema
-- Create a set of entries in Oracle Data Dictionary that describe the XML schema
-- Create a set of SQL object types and XMLType tables required to manage the instance documents.

Schema annotation:

-- Schema Annotation provides the option to control the mapping between the XML Schema and SQL object structure. By setting various attributes in the schema, we can influence various aspects of XML storage and processing in the database. By default SQL Objects generated when an XML schema is registered are given system-generated names. When we annotate a schema we can specify what names we wish to give the underlying SQL objects. To annotate an XML schema, you must first include the Oracle XML DB namespace in the XMLSchema tag, defined as:

-- XML Database implements schema registration through the PL/SQL supplied package called dbms_xmlschema.registerSchema() where you can perform the following annotations:

-> When GENTYPES or GENTABLES is set TRUE, schema annotation makes it possible for developers to ensure that the names of the tables, objects, and attributes are well-known names, compliant with any application-naming standards.

-> When GENTYPES or GENTABLES is set FALSE, schema annotation makes it possible for developers to map between the XML Schema and existing objects and tables within the database.

-> To prevent the generation of mixed-case names that require the use of quoted identifies when working directly with SQL. For example, desc "PurchaseOrder_T" would need to be used to desc this type.

-> By editing the XML Schema document (xsd) you can use the following annotations:

-> defaultTable: Used to control the name of the default table generated for each global element when the GENTABLES parameter is FALSE. Setting this to "" will prevent a default table from being generated for the element in question.

-> SQLName: Used to specify the name of the SQL attribute that corresponds to each element or attribute defined in the XML Schema

-> SQLType:  For complexType definitions, SQLType is used to specify the name of the SQL object type that are corresponds to the complexType definitions. For simpleType definitions, SQLType is used to override the default mapping between XML Schema data types and SQL data types. A very common use of SQLType is to define when unbounded strings should be stored as CLOB values, rather than VARCHAR(4000).

-> SQLCollType:  Used to specify the name of the VARRAY type that will manage a collection of elements.

-> maintainDOM:  Used to determine whether or not DOM fidelity should be maintained for a given complexType definition

-> storeVarrayAsTable: Specified in the root element of the XML Schema. Used to force all collections to be stored as nested tables. There will be one nested table created for each element that specifies maxOccurs > 1. The nested tables will be created with system-generated names. This is the default setting for 11.1.0.X onwards.

Local vs Global:

XML Schemas can be registered as local or global:

-- Local XML schema: An XML schema registered as a local schema is, by default, visible only to the owner. Local XML schema resources are created under the repository in the /sys/schemas/<username> directory.

-- Global XML schema: An XML schema registered as a global schema is, by default, visible and usable by all database users. Global schemas are visible to all users and stored under the /sys/schemas/PUBLIC/ directory in Oracle XML DB Repository.


If you have two XML schemas that depend on each other in order to get registered, you must use the FORCE parameter in DBMS_XMLSCHEMA.registerSchema. For example the following steps would take place:

Step 1 : Register "s1.xsd" in FORCE mode:
dbms_xmlschema.registerSchema("s1.xsd", "<schema ...", ..., force => true)

At this point, s1.xsd is invalid and cannot be used.

Step 2 : Register "s2.xsd" in FORCE mode:
dbms_xmlschema.registerSchema("s2.xsd", "<schema ..", ..., force => true)

The second operation automatically compiles s1.xsd and makes both XML schemas valid.

Large XML Schemas:

A number of issues can arise when working with large, complex XML schemas. Sometimes the error ORA-01792: maximum number of columns in a table or view is 1000 is encountered when registering an XML schema or creating a table based on a global element defined by an XML schema. This error occurs when an attempt is made to create an XMLType table or column based on a global element and the global element is defined as a complexType that contains a very large number of element and attribute definitions. The error only occurs when creating an XMLType table or column that uses object-relational storage. Please refer to ease of use tools for workaround if you run into this error.

Debugging Schema Registration:

You can monitor the object types and tables created during XML Schema registration by setting the following event before calling DBMS_XMLSCHEMA.registerSchema():

ALTER SESSION SET events='31098 trace name context forever'; Setting this event causes the generation of a log of all the CREATE TYPE and CREATE TABLE statements. The log is written to the user session trace file, typically found in <ORACLE_HOME>/admin/<ORACLE_SID>/udump. This script can be a useful aid in diagnosing problems during XML Schema registration.

Schema Data Dictionary Views:

USER_XML_SCHEMAS -- Lists all registered XML Schemas owned by the user.

ALL_XML_SCHEMAS   -- Lists all registered XML Schemas usable by the current user.

DBA_XML_SCHEMAS  -- Lists all registered XML Schemas in Oracle XML DB.

Best Practises

Registration of Schemas example with Object Relational Storage:

The example shown below will use the PurchaseOrder.Annotated.xsd, which is a detailed W3C XML Schema example to demonstrate various features when registering an XML Schema with Oracle XML Database (XDB):

Schema definition Explanation
<xs:schema The <schema> element as root element for the xsd.
targetNamespace="" Target namespace of schema is another URL that specifies an abstract namespace within which elements/types get declared. Usually the target namespace is the same as the URL of the XML Schema.
xmlns:xs="" Is the required W3C definition XML Schema with an xs prefix.
xmlns:xdb="" This is reserved by Oracle for XML DB schema annotations.
xmlns:po="" The default namespace also referenced by the alias po.
version="1.0" Version 1.0 document
xdb:storeVarrayAsTable="true XDB annotation storeVarrayAsTable means that any collection will be stored as a nested table.
<xs:element name="PurchaseOrder" type="po:PurchaseOrderType" Global element PurchaseOrder and the type is coming from default namespace.
xdb:defaultTable="PURCHASEORDER"/> The defaultTable annotationxmltype table name for the global element PurchaseOrder.
<xs:complexType name="PurchaseOrderType" xdb:SQLType="PURCHASEORDER_T"> ComplexType as it has subelements. SQLType is the SQL object type for the PurchaseOrderType which is PURCHASEORDER_T.
<xs:sequence> Specifies ordered sequence of elements that is contained in complextypes.
<xs:element name="Reference" type="po:ReferenceType" minOccurs="1" xdb:SQLName="REFERENCE"/> Reference element has xdb annotation so that SQL attribute is REFERENCE.
<xs:element name="Actions" type="po:ActionsType" xdb:SQLName="ACTION_COLLECTION"/> Actions element takes SQLName so SQL attribute is ACTION_COLLECTION.
<xs:element name="Reject" type="po:RejectionType" minOccurs="0"/>
<xs:element name="Requestor" type="po:RequestorType"/>
<xs:element name="User" type="po:UserType" minOccurs="1"xdb:SQLName="EMAIL"/>
User element includes SQLName so that User attribute will be EMAIL.
<xs:element name="CostCenter" type="po:CostCenterType"/>
<xs:element name="ShippingInstructions" type="po:ShippingInstructionsType"/>
<xs:element name="SpecialInstructions" type="po:SpecialInstructionsType"/>
<xs:element name="LineItems" type="po:LineItemsType"xdb:SQLName="LINEITEM_COLLECTION"/>
LineItems element includes SQLName Annotation so that LineItems element will be LINEITEM_COLLECTION
<xs:sequence> Specifies ordered sequence of elements that is contained in complextypes.
<xs:element name="LineItem" type="po:LineItemType" maxOccurs="unbounded" xdb:SQLCollType="LINEITEM_V" xdb:SQLName="LINEITEM_VARRAY"/> LineItems has SQLType associating it with LINEITEM_VARRAY and SQLCollName. Name of SQL Object Type manages the collection is LINEITEM_V.
<xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T">
Global complextype LineItemType is called LINEITEM_T
<xs:element name="Description" type="po:DescriptionType"/>
<xs:element name="Part" type="po:PartType"/>
<xs:attribute name="ItemNumber" type="xs:integer"/>
<xs:complexType name="PartType" xdb:SQLType="PART_T" xdb:maintainDOM="false">
Global complexType PartType name of object type is PART_T and XDB Will not maintain DOM Fidelity
<xs:attribute name="Id">
<xs:restriction base="xs:string">
<xs:minLength value="10"/>
<xs:maxLength value="14"/>
Attribute ID has to be between 10 and 14 string length.
<xs:attribute name="Quantity" type="po:moneyType"/>
<xs:attribute name="UnitPrice" type="po:quantityType"/>
</xs:schema> Closing out schema root element.

-- Now to register the schema to show the resulting object-relational tables/types created:

Register schema commands Description of registration options.
Database API needed to register the schema.
SCHEMAURL => '', The unique identifier for the XML Schema which would be recorded in schema_url in dba_xml_schemas.
SCHEMADOC => bfilename('XMLDIR','purchaseOrder.Annotated.xsd'), The actual xsd been registered using BFILE. It is going to the location on the local filesystem as specified by the directory XMLDIR.
LOCAL => TRUE, Schema registered locally (DEFAULT).
GENTYPES => TRUE, Object types will be created (DEFAULT).
GENTABLES => TRUE, XMLType tables will be created for each global element.
CSID => nls_charset_id('AL32UTF8')); Character-set ID of source-document encoding, when schemaDoc is BFILE.
Execute the API.

-- Creates default XMLType tables for all global elements:


------------- ----------------------------------- -------------

1 row selected.


Name Null? Type
------------------------------ ----- -----------------
"" ELEMENT "PurchaseOrder") STORAGE Object-relational TYPE "PURCHASEORDER_T"

-- Creates the appropriate SQL object types in database that conform to XML Schema:


Name Null? Type
-------------------- ----- --------------------------
Requestor VARCHAR2(128 CHAR)
CostCenter VARCHAR2(4 CHAR)
SpecialInstructions VARCHAR2(2048 CHAR)
Notes CLOB

Name Null? Type
-------------------- ----- --------------------------


Name Null? Type
-------------------- ----- --------------------------
ItemNumber NUMBER(38)
Description VARCHAR2(256 CHAR)


Name Null? Type
-------------------- ----- --------------------------
Quantity NUMBER(12,2)
UnitPrice NUMBER(8,4)


---------- -----------------------

2 rows selected.

New features in relation to Schemas:


-- OEM can be used to manage XML Schemas.


-- Since binary XML was introduced in 11g, there is now an option to register an XML schema for binary XML. Please note, when registering binary xml, the schema data types are encoded in binary format, so the underlying data cannot be manipulated. As seen below in the syntax, it is necessary to specify the option dbms_xmlschema.register_binaryxml and also gentypes must be set to false:

Registration option for binary XML Description of Options
SCHEMADOC => bfilename('XMLDIR','purchaseOrder.Annotated.xsd'),
Database API used to register the schema that is used to specify the unique identifier for the XML Schema and the actual xsd document (purchaseOrder.Annotated.xsd) we are loading into the database.
genTypes => FALSE, Mandatory for it to be set to false when using binary XML.
genTables => FALSE, XMLType tables will be created for each global element.
OPTIONS => DBMS_XMLSCHEMA.REGISTER_BINARYXML); Mandatory for this option to be specified for binary xml.
Execute the API.

-- The default value of XML Schema Annotation storeVarrayAsTable is now true. In prior releases, the default value of XML schema annotation storeVarrayAsTable was false; the default value is now true. This means that, by default, an XML collection is now stored as a set of rows in an ordered collection table (OCT). Each row corresponds to an element in the collection. With annotation storeVarrayAsTable = "false", the entire collection is instead serialized as a varray and stored in a LOB column.

Ease of Tools Options:

The Ease of Use Packages can be downloaded into your database and provide PL/SQL packages that help your control schema registration options when using Object-Relational Storage. These packages improve manageability and ease of use by mapping annotations to a XML Schema. For example, a sample of the following functionality is possible by using these packages:

-- the ability to generate a specified xmltype table name that you specify for a specified global element.
-- assign a SQL type name for any child, a coollection or a SQL attribute
-- set the TimeStampWIthTimeZone data type to all dateTime typed elements in the XML Schema
-- enable or disable DOM fidelity
-- set out of line or in line storage
-- provides additional views so you can determine tables and type structures related to the underlying table.

You can download from: and then clicking on "Technology Preview: Oracle XML DB Ease of Use Tools for Structured Storage",

Known Issues

<Document 1279463.1>  After Schema Registration W/GENTABLES=>True, Table Creation W/O-R Storage=ORA-19046
<Document 471568.1>   ORA-31001 Invalid resource handle or path name error with Schema Registration
<Document 250796.1> How To Get XML Files Automatically Registered To An XML Schema
<Document 249854.1>  What Permissions are Needed to Register a Schema?
<Document 563802.1>  How To Use Partitioning On A Schemabased XMLtype Table
<Document 423404.1>  dbms_xmlschema.copyEvolve can fail if recyclebin not set to off
<Document 725838.1>  How to Remove Invalid 'XML SCHEMA' Objects if Schema does not Exist

-- When querying an underlying XMLType table or type getting the following error:

ORA-00942: table or view does not exist

The names of SQL tables, object, and attributes generated by XML Schema registration are case sensitive. For example, a table called PurchaseOrder1669_TAB was created automatically during registration of the XML Schema. Since the table name was derived from the element name, PurchaseOrder, the name of the table is also mixed case. This means that you must refer to this table in SQL using a quoted identifier: "PurchaseOrder1669_TAB". Failure to do so results in an object-not-found error, such as ORA-00942: table or view does not exist.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.