Oracle® XML DB Developer's Guide 10g Release 2 (10.2) Part Number B14259-02 |
|
|
View PDF |
The XML Schema Recommendation was created by the World Wide Web Consortium (W3C) to describe the content and structure of XML documents in XML. It includes the full capabilities of Document Type Definitions (DTDs) so that existing DTDs can be converted to XML Schema. XML schemas have additional capabilities compared to DTDs.
This chapter provides basic information about using XML Schema with Oracle XML DB. It explains how to do all of the following:
Register, update, and delete an XML schema
Create storage structures for XML schema-based data
Map XML simpleType
and complexType
to SQL storage types
See Also:
Chapter 7, "XML Schema Storage and Query: Advanced" for more advanced information on using XML Schema with Oracle XML DB
Chapter 6, "XPath Rewrite" for information on the optimization of XPath expressions in Oracle XML DB
Appendix A, "XML Schema Primer" for an introduction to XML Schema
This chapter contains these topics:
XML Schema is a schema definition language written in XML. It can be used to describe the structure and various other semantics of conforming instance documents. For example, the following XML schema definition, purchaseOrder.xsd
, describes the structure and other properties of purchase-order XML documents.
This manual refers to an XML schema instance definition as an XML schema.
Example 5-1 XML Schema Instance purchaseOrder.xsd
The following is an XML schema that declares a complexType
called purchaseOrderType
and a global element PurchaseOrder
of this type. This is the same schema as Example 3-7, "Purchase-Order XML Schema, purchaseOrder.xsd", with the exception of the lines in bold
here, which are additional. For brevity, part of the schema is omitted here (marked ...
).
<xs:schema targetNamespace="http://xmlns.oracle.com/xdb/documentation/purchaseOrder" xmlns:po="http://xmlns.oracle.com/xdb/documentation/purchaseOrder" xmlns:xs="http://www.w3.org/2001/XMLSchema" version="1.0"> <xs:element name="PurchaseOrder" type="po:PurchaseOrderType"/> <xs:complexType name="PurchaseOrderType"> <xs:sequence> <xs:element name="Reference" type="po:ReferenceType"/> <xs:element name="Actions" type="po:ActionsType"/> <xs:element name="Reject" type="po:RejectionType" minOccurs="0"/> <xs:element name="Requestor" type="po:RequestorType"/> <xs:element name="User" type="po:UserType"/> <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"/> <xs:element name="Notes" type="po:NotesType"/> </xs:sequence> </xs:complexType> <xs:complexType name="LineItemsType"> <xs:sequence> <xs:element name="LineItem" type="po:LineItemType" maxOccurs="unbounded"/> </xs:sequence> </xs:complexType> ... <xs:simpleType name="DescriptionType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="256"/> </xs:restriction> </xs:simpleType> <xs:simpleType name="NotesType"> <xs:restriction base="xs:string"> <xs:minLength value="1"/> <xs:maxLength value="32767"/> </xs:restriction> </xs:simpleType> </xs:schema>
Example 5-2 purchaseOrder.XML: Document That Conforms to purchaseOrder.xsd
The following is an example of an XML document that conforms to XML schema purchaseOrder.xsd
:
<po:PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:po="http://xmlns.oracle.com/xdb/documentation/purchaseOrder" xsi:schemaLocation= "http://xmlns.oracle.com/xdb/documentation/purchaseOrder http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"> <Reference>SBELL-2002100912333601PDT</Reference> <Actions> <Action> <User>SVOLLMAN</User> </Action> </Actions> <Reject/> <Requestor>Sarah J. Bell</Requestor> <User>SBELL</User> <CostCenter>S30</CostCenter> <ShippingInstructions> <name>Sarah J. Bell</name> <address>400 Oracle Parkway Redwood Shores CA 94065 USA </address> <telephone>650 506 7400</telephone> </ShippingInstructions> <SpecialInstructions>Air Mail</SpecialInstructions> <LineItems> <LineItem ItemNumber="1"> <Description>A Night to Remember</Description> <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/> </LineItem> <LineItem ItemNumber="2"> <Description>The Unbearable Lightness Of Being</Description> <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/> </LineItem> <LineItem ItemNumber="3"> <Description>Sisters</Description> <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/> </LineItem> </LineItems> <Notes>Section 1.10.32 of "de Finibus Bonorum et Malorum", written by Cicero in 45 BC "Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium doloremque laudantium, totam rem aperiam, eaque ips ... tiae consequatur, vel illum qui dolorem eum fugiat quo voluptas nulla pariatur?" 1914 translation by H. Rackham "But I must explain to you how all this mistaken idea of denouncing pleasure and praising pain was born and I will give you a c ... o avoids a pain that produces no resultant pleasure?" Section 1.10.33 of "de Finibus Bonorum et Malorum", written by Cicero in 45 BC "At vero eos et accusamus et iusto odio dignissimos ducimus qui blanditiis praesentium voluptatum deleniti atque corrupti quos ... delectus, ut aut reiciendis voluptatibus maiores alias consequatur aut perferendis doloribus asperiores repellat." 1914 translation by H. Rackham "On the other hand, we denounce with righteous indignation and dislike men who are so beguiled and demoralized by the charms of ... secure other greater pleasures, or else he endures pains to avoid worse pains." </Notes> </po:PurchaseOrder>
Note:
The URL used here is simply a name that uniquely identifies the registered XML schema within the database:http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd
. This need not be the physical URL at the which the XML schema document is located. The target namespace of the XML schema is another URL, different from the XML schema location URL, which specifies an abstract namespace within which elements and types get declared.
An XML schema can optionally specify the target namespace URL. If this attribute is omitted, the XML schema has no target namespace. The target namespace is commonly the same as the URL of the XML schema.
An XML instance document must specify the namespace of the root element (same as the target namespace of the XML schema) and the location (URL) of the XML schema that defines this root element. The location is specified with attribute xsi:schemaLocation
. When the XML schema has no target namespace, use attribute xsi:noNamespaceSchemaLocation
to specify the schema URL.
Oracle XML DB uses annotated XML Schemas as metadata, that is, the standard XML Schema definitions along with several Oracle XML DB-defined attributes. These attributes control how instance XML documents get mapped to the database. Because these attributes are in a different namespace from the XML Schema namespace, such annotated XML Schemas are still legal XML Schema documents.
See Also:
http://www.w3.org/2001/XMLSchema
When using Oracle XML DB with XML Schema, you must first register the XML schema. You can then use the XML schema URLs while creating XMLType
tables, columns, and views. The XML schema URL, in other words, the URL that identifies the XML schema in the database, is associated with parameter schemaurl
of PL/SQL procedure DBMS_XMLSCHEMA.registerSchema
.
Oracle XML DB provides XML Schema support for the following tasks:
Registering any W3C-compliant XML schemas.
Validating your XML documents against registered XML schema definitions.
Registering local and global XML schemas.
Generating XML schemas from object types.
Referencing an XML schema owned by another user.
Explicitly referencing a global XML schema when a local XML schema exists with the same name.
Generating a structured database mapping from your XML schemas during XML schema registration. This includes generating SQL object types, collection types, and default tables, and capturing the mapping information using XML schema attributes.
Specifying a particular SQL type mapping when there are multiple legal mappings.
Creating XMLType
tables, views and columns based on registered XML schemas.
Performing manipulation (DML) and queries on XML schema-based XMLType
tables.
Automatically inserting data into default tables when schema-based XML instances are inserted into Oracle XML DB Repository using FTP, HTTP(S)/WebDAV protocols and other languages.
See Also:
Chapter 3, "Using Oracle XML DB"As described in Chapter 4, "XMLType Operations", XMLType
is a datatype that facilitates storing XMLType
in columns and tables in the database. XML schemas further facilitate storing XML columns and tables in the database, and they offer you more storage and access options for XML data along with space- performance-saving options.
For example, you can use XML schemas 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.
Using XML Schema with Oracle XML DB provides a flexible setup for XML storage mapping. For example:
If your data is highly structured (mostly XML), then each element in the XML documents can be stored as a column in a table.
If your data is unstructured (all or most is not XML data), then the data can be stored in a Character Large Object (CLOB).
Which storage method you choose depends on how your data will be used and depends on the queriability and your requirements for querying and updating your data. In other words, using XML Schema gives you more flexibility for storing highly structured or unstructured data.
Another advantage of using XML Schema with Oracle XML DB is that you can perform XML instance validation according to XML schemas and with respect to Oracle XML Repository requirements for optimal performance. For example, an XML schema can check that all incoming XML documents comply with definitions declared in the XML schema, such as allowed structure, type, number of allowed item occurrences, or allowed length of items.
Also, by registering XML schemas in Oracle XML DB, when inserting and storing XML instances using protocols such as FTP or HTTP(S), the XML schema information can influence how efficiently XML instances are inserted.
When XML instances must be handled without any prior information about them, XML schemas can be useful in predicting optimum storage, fidelity, and access.
A DTD is a set of rules that define the allowable structure of an XML document. DTDs are text files that derive their format from SGML and can be associated with an XML document either by using the DOCTYPE
element or by using an external file through a DOCTYPE
reference. In addition to supporting XML Schema, which provides a structured mapping to object- relational storage, Oracle XML DB also supports DTD specifications in XML instance documents. Though DTDs are not used to derive the mapping, XML processors can still access and interpret the DTDs.
When an XML instance document has an inline DTD definition, it is used during document parsing. Any DTD validations and entity declaration handling is done at this point. However, once parsed, the entity references are replaced with actual values and the original entity reference is lost.
Oracle XML DB also supports external DTD definitions if they are stored in Oracle XML DB Repository. Applications needing to process an XML document containing an external DTD definition such as /public/flights.dtd
, must first ensure that the DTD document is stored in Oracle XML DB at path /public/flights.xsd
.
Before an XML schema can be used by Oracle XML DB, it must be registered with Oracle Database. You register an XML schema using the PL/SQL package DBMS_XMLSCHEMA
.
Some of the main DBMS_XMLSCHEMA
procedures are these:
registerSchema
– register an XML schema with Oracle Database
deleteSchema
– delete a previously registered XML schema.
copyEvolve
– update a registered XML schema; see Chapter 8, "XML Schema Evolution".
The main arguments to procedure DBMS_XMLSCHEMA.registerSchema
are these:
schemaURL
– the XML schema URL. This is a unique identifier for the XML schema within Oracle XML DB. It is conventionally in the form of a URL; however, this is not a requirement. The XML schema URL is used with Oracle XML DB to identify instance documents, by making the schema location hint identical to the XML schema URL. Oracle XML DB will never attempt to access the Web server identified by the specified URL.
schemaDoc
– the XML schema source document. This is a VARCHAR
, CLOB
, BLOB
, BFILE
, XMLType
, or URIType
value.
CSID
– the character-set ID of the source-document encoding, when schemaDoc
is a BFILE
or BLOB
value.
Example 5-3 Registering an XML Schema with DBMS_XMLSCHEMA.REGISTERSCHEMA
The following code registers the XML schema at URL http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd
. This example shows how to register an XML schema using the BFILE
mechanism to read the source document from a file on the local file system of the database server.
BEGIN DBMS_XMLSCHEMA.registerSchema( SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd', SCHEMADOC => bfilename('XMLDIR','purchaseOrder.xsd'), CSID => nls_charset_id('AL32UTF8')); END; /
When you register an XML schema, keep in mind the following considerations:
The act of registering a schema has no effect on the status of any instance documents already loaded into Oracle XML DB Repository that claim to be members of the class defined the schema.
Because the schema they reference was not yet registered, such instance documents were non-schema-based when they were loaded. They remain non-schema-based after the schema is registered.
You must delete such instance documents, and reload them after registering the schema, in order to obtain schema-based documents.
As part of registering an XML schema, Oracle XML DB also performs several tasks that facilitate storing, accessing, and manipulating XML instances that conform to the XML schema. These steps include:
Creating types: When an XML schema is registered, Oracle Database creates the appropriate SQL object types that enable the structured storage of XML documents that conform to this XML schema. You can use XML-schema annotations to control how these object types are named and generated. See "SQL Object Types" for details.
Creating default tables: As part of XML schema registration, Oracle XML DB generates default XMLType
tables for all global elements. You can use XML-schema annotations to control the names of the tables and to provide column-level and table-level storage clauses and constraints for use during table creation.
After registration has completed:
XMLType
tables and columns can be created that are constrained to the global elements defined by this XML schema.
XML documents conforming to the XML schema, and referencing it using the XML Schema instance mechanism, can be processed automatically by Oracle XML DB.
See Also:
Chapter 3, "Using Oracle XML DB"Registration of an XML schema is non-transactional and auto-committed, as follows:
If registration succeeds, then the operation is auto-committed.
If registration fails, then the database is rolled back to the state before registration began.
Because XML schema registration potentially involves creating object types and tables, error recovery involves dropping any such created types and tables. The entire XML schema registration process is guaranteed to be atomic: either it succeeds or the database is restored to its state before the start of registration.
XML schema documents are themselves stored in Oracle XML DB as XMLType
instances. XML schema-related XMLType
types and tables are created as part of the Oracle XML DB installation script, catxdbs.sql
.
The XML schema for XML schemas is called the root XML Schema, XDBSchema.xsd
. XDBSchema.xsd
describes any valid XML schema document that can be registered by Oracle XML DB. You can access XDBSchema.xsd
through Oracle XML DB Repository at /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/XDBSchema.xsd
.
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_BASE/admin/ORACLE_SID/udump
. This script can be a useful aid in diagnosing problems during XML schema registration.
Assuming that the parameter GENTYPES
is set to TRUE
when an XML schema is registered, Oracle XML DB creates the appropriate SQL object types that enable structured storage of XML documents that conform to this XML schema. By default, all SQL object types are created in the database schema of the user who registers the XML schema. If the defaultSchema
annotation is used, then Oracle XML DB attempts to create the object type using the specified database schema. The current user must have the necessary privileges to perform this.
Example 5-4 Creating SQL Object Types to Store XMLType Tables
For example, when purchaseOrder.xsd
is registered with Oracle XML DB, the following SQL types are created.
DESCRIBE "PurchaseOrderType1668_T" "PurchaseOrderType1668_T" is NOT FINAL Name Null? Type -------------------- ------ ------------------------------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T Reference VARCHAR2(30 CHAR) Actions ActionsType1661_T Reject RejectionType1660_T Requestor VARCHAR2(128 CHAR) User VARCHAR2(10 CHAR) CostCenter VARCHAR2(4 CHAR) ShippingInstructions ShippingInstructionsTyp1659_T SpecialInstructions VARCHAR2(2048 CHAR) LineItems LineItemsType1666_T Notes VARCHAR2(4000 CHAR) DESCRIBE "LineItemsType1666_T" "LineItemsType1666_T" is NOT FINAL Name Null? Type -------------------- ----- ------------------------------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T LineItem LineItem1667_COLL DESCRIBE "LineItem1667_COLL" "LineItem1667_COLL" VARRAY(2147483647) OF LineItemType1665_T "LineItemType1665_T" is NOT FINAL Name Null? Type ------------------- ----- -------------------------------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T ItemNumber NUMBER(38) Description VARCHAR2(256 CHAR) Part PartType1664_T
Note:
By default, the names of the object types and attributes in the preceding example are system-generated.Developers can use XML-schema annotations to provide user-defined names (see "Oracle XML Schema Annotations" for details).
If the XML schema does not contain the SQLName
attribute, then the name is derived from the XML name.
As part of XML schema registration, you can also create default tables. Default tables are most useful when XML instance documents conforming to this XML schema are inserted through APIs and protocols that do not have any table specification, such as FTP or HTTP(S). In such cases, the XML instance is inserted into the default table.
Example 5-5 Default Table for Global Element PurchaseOrder
DESCRIBE "purchaseorder1669_tab" Name Null? Type --------------------------- ----- ----------------------- TABLE of SYS.XMLTYPE( XMLSchema "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd" Element "PurchaseOrder") STORAGE Object-relational TYPE "PurchaseOrderType1668_T"
If you provide a value for attribute defaultTable
, then the XMLType
table is created with that name. Otherwise it gets created with an internally generated name.
Further, any text specified using the tableProps
and columnProps
attributes is appended to the generated CREATE TABLE
statement.
The names of SQL tables, object, and attributes generated by XML schema registration are case sensitive. For instance, in Example 5-3, "Registering an XML Schema with DBMS_XMLSCHEMA.REGISTERSCHEMA", 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
.
The following objects are dependent on registered XML schemas:
Tables or views that have an XMLType
column that conforms to some element in the XML schema.
XML schemas that include or import this schema as part of their definition.
Cursors that reference the XML schema name, for example, within functions of package DBMS_XMLGEN
. Such cursors are purely transient objects.
To obtain a list of the XML schemas registered with Oracle XML DB using DBMS_XMLSCHEMA.registerSchema
, use the code in Example 5-6. You can also examine USER_XML_SCHEMAS
, ALL_XML_SCHEMAS
, USER_XML_TABLES
, and ALL_XML_TABLES
.
Example 5-6 Data Dictionary Table for Registered Schemas
DESCRIBE DBA_XML_SCHEMAS Name Null? Type ------------ ----- ----------------------- OWNER VARCHAR2(30) SCHEMA_URL VARCHAR2(700) LOCAL VARCHAR2(3) SCHEMA XMLTYPE(XMLSchema "http://xmlns.oracle.com/xdb/XDBSchema.xsd" Element "schema") INT_OBJNAME VARCHAR2(4000) QUAL_SCHEMA_URL VARCHAR2(767) SELECT OWNER, LOCAL, SCHEMA_URL FROM DBA_XML_SCHEMAS; OWNER LOC SCHEMA_URL ----- --- ---------------------- XDB NO http://xmlns.oracle.com/xdb/XDBSchema.xsd XDB NO http://xmlns.oracle.com/xdb/XDBResource.xsd XDB NO http://xmlns.oracle.com/xdb/acl.xsd XDB NO http://xmlns.oracle.com/xdb/dav.xsd XDB NO http://xmlns.oracle.com/xdb/XDBStandard.xsd XDB NO http://xmlns.oracle.com/xdb/log/xdblog.xsd XDB NO http://xmlns.oracle.com/xdb/log/ftplog.xsd XDB NO http://xmlns.oracle.com/xdb/log/httplog.xsd XDB NO http://www.w3.org/2001/xml.xsd XDB NO http://xmlns.oracle.com/xdb/XDBFolderListing.xsd XDB NO http://xmlns.oracle.com/xdb/stats.xsd XDB NO http://xmlns.oracle.com/xdb/xdbconfig.xsd SCOTT YES http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd 13 rows selected. DESCRIBE DBA_XML_TABLES Name Null? Type ------------ ----- ----------------------- OWNER VARCHAR2(30) TABLE_NAME VARCHAR2(30) XMLSCHEMA VARCHAR2(700) SCHEMA_OWNER VARCHAR2(30) ELEMENT_NAME VARCHAR2(2000) STORAGE_TYPE VARCHAR2(17) SELECT TABLE_NAME FROM DBA_XML_TABLES WHERE XMLSCHEMA = 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd'; TABLE_NAME --------------------- PurchaseOrder1669_TAB 1 row selected.
You can delete a registered XML schema by using procedure DBMS_XMLSCHEMA.deleteSchema
. When you attempt to delete an XML schema, DBMS_XMLSCHEMA
checks:
That the current user has the appropriate privileges (ACLs) to delete the resource corresponding to the XML schema within Oracle XML DB Repository. You can thus control which users can delete which XML schemas, by setting the appropriate ACLs on the XML Schema resources.
For dependents. If there are any dependents, then it raises an error and the deletion operation fails. This is referred to as the RESTRICT mode of deleting XML Schemas.
When deleting XML Schemas, if you specify the FORCE
mode option, then the XML Schema deletion proceeds even if it fails the dependency check. In this mode, XML Schema deletion marks all its dependents as invalid.
The CASCADE
mode option drops all generated types and default tables as part of a previous call to register XML Schema.
Example 5-7 Deleting an XML Schema with DBMS_XMLSCHEMA.DELETESCHEMA
The following example deletes XML schema purchaseOrder.xsd
. Then, the schema is deleted using the FORCE
and CASCADE
modes with DBMS_XMLSCHEMA
.DELETESCHEMA
:
BEGIN DBMS_XMLSCHEMA.deleteSchema( SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd', DELETE_OPTION => dbms_xmlschema.DELETE_CASCADE_FORCE); END; /
Table 5-1 lists the XMLType
XML schema-related methods.
Table 5-1 XMLType Methods Related to XML Schema
XMLType Method | Description |
---|---|
isSchemaBased() |
Returns |
getSchemaURL() getRootElement() getNamespace() |
Return the XML schema URL, name of root element, and the namespace for an XML schema-based |
schemaValidate() isSchemaValid() isSchemaValidated() setSchemaValidated() |
An |
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.
Global XML Schema: An XML schema registered as a global schema is, by default, visible and usable by all database users.
When you register an XML schema, DBMS_XMLSCHEMA
adds an Oracle XML DB resource corresponding to the XML schema to Oracle XML DB Repository. The XML schema URL determines the path name of the resource in the repository (and is associated with the SCHEMAURL
parameter of registerSchema
) according to the following rules:
By default, an XML schema belongs to you after registering the XML schema with Oracle XML DB. A reference to the XML schema document is stored in Oracle XML DB Repository. Such XML schemas are referred to as local. In general, they are usable only by you, the owner.
In Oracle XML DB, local XML schema resources are created under the /sys/schemas/
username
directory. The rest of the path name is derived from the schema URL.
Example 5-8 Registering A Local XML Schema
BEGIN
DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd',
SCHEMADOC => bfilename('XMLDIR','purchaseOrder.xsd'),
LOCAL => TRUE,
GENTYPES => TRUE,
GENTABLES => FALSE,
CSID => nls_charset_id('AL32UTF8'));
END;
/
If this local XML schema is registered by user SCOTT
, it is given this path name:
/sys/schemas/SCOTT/xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd
Database users need appropriate permissions and Access Control Lists (ACL) to create a resource with this path name in order to register the XML schema as a local XML schema.
Note:
Typically, only the owner of the XML schema can use it to defineXMLType
tables, columns, or views, validate documents, and so on. However, Oracle Database supports fully qualified XML schema URLs, which can be specified as:
http://xmlns.oracle.com/xdb/schemas/SCOTT/xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd
This extended URL can be used by privileged users to specify XML schemas belonging to other users.
In contrast to local schemas, privileged users can register an XML Schema as a global XML Schema by specifying an argument in the DBMS_XMLSCHEMA
registration function.
Global XML schemas are visible to all users and stored under the /sys/schemas/PUBLIC/
directory in Oracle XML DB Repository.
Note:
Access to this directory is controlled by Access Control Lists (ACLs) and, by default, is writable only by a DBA. You need write privileges on this directory to register global schemas.Role XDBAdmin
also provides write access to this directory, assuming that it is protected by the default protected Access Control Lists (ACL). See Chapter 24, "Repository Resource Security" for further information on privileges and for details on role XDBAdmin
.
You can register a local schema with the same URL as an existing global schema. A local schema always hides any global schema with the same name (URL).
Example 5-9 Registering A Global XML Schema
GRANT XDBADMIN TO SCOTT; Grant succeeded. CONNECT scott/tiger Connected. BEGIN DBMS_XMLSCHEMA.registerSchema( SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd', SCHEMADOC => bfilename('XMLDIR','purchaseOrder.xsd'), LOCAL => FALSE, GENTYPES => TRUE, GENTABLES => FALSE, CSID => nls_charset_id('AL32UTF8')); END; /
If this global XML schema is registered by user SCOTT
, it is given this path name:
/sys/schemas/PUBLIC/xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd
Database users need appropriate permissions (ACLs) to create this resource in order to register the XML schema as global.
Document Object Model (DOM) fidelity is the concept of retaining the structure of a retrieved XML document, compared to the original XML document, for DOM traversals. DOM fidelity is needed to ensure the accuracy and integrity of XML documents stored in Oracle XML DB.
See Also:
"Overriding the SQLType Value in XML Schema When Declaring Attributes" and "Overriding the SQLType Value in XML Schema When Declaring Elements"All elements and attributes declared in the XML schema are mapped to separate attributes in the corresponding SQL object type. However, some pieces of information in XML instance documents are not represented directly by these element or attributes, such as:
Comments
Namespace declarations
Prefix information
To ensure the integrity and accuracy of this data, for example, when regenerating XML documents stored in the database, Oracle XML DB uses a data integrity mechanism called DOM fidelity.
DOM fidelity refers to how similar the returned and original XML documents are, particularly for purposes of DOM traversals.
In order to provide DOM fidelity, Oracle XML DB has to maintain instance-level metadata. This metadata is tracked at a type level using the system-defined binary attribute SYS_XDBPD$
. This attribute is referred to as the positional descriptor, or PD for short. The PD attribute is intended for Oracle XML DB internal use only. You should never directly access or manipulate this column.
The positional descriptor attribute stores all information that cannot be stored in any of the other attributes. PD information is used to ensure the DOM fidelity of all XML documents stored in Oracle XML DB. Examples of such information include: ordering information, comments, processing instructions, and namespace prefixes.
If DOM fidelity is not required, you can suppress SYS_XDBPD$
in the XML schema definition by setting the attribute maintainDOM=FALSE
at the type level.
Note:
The attributeSYS_XDBPD$
is omitted in many examples here for clarity. However, the attribute is always present as a positional descriptor (PD) column in all SQL object types generated by the XML schema registration process.
In general, it is not a good idea to suppress the PD attribute, because the extra information, such as comments and processing instructions, could be lost if there is no PD column.
Using Oracle XML DB, developers can create XMLType
tables and columns that are constrained to a global element defined by a registered XML schema. After an XMLType
column has been constrained to a particular element and a particular XML schema, it can only contain documents that are compliant with the schema definition of that element. An XMLType
table column is constrained to a particular element and a particular XML schema by adding the appropriate XMLSCHEMA
and ELEMENT
clauses to the CREATE TABLE
operation.
Figure 5-1 shows the syntax for creating an XMLType
table:
CREATE [GLOBAL TEMPORARY] TABLE [schema.] table OF XMLType [(object_properties)] [XMLType XMLType_storage] [XMLSchema_spec] [ON COMMIT {DELETE | PRESERVE} ROWS] [OID_clause] [OID_index_clause] [physical_properties] [table_properties];
A subset of the XPointer notation, shown in the following example, can also be used to provide a single URL containing the XML schema location and element name. See also Chapter 4, "XMLType Operations".
Example 5-10 Creating XML Schema-Based XMLType Tables and Columns
This example shows CREATE TABLE
statements. The first creates an XMLType
table, purchaseorder_as_table
. The second creates a relational table, purchaseorder_as_column
, with an XMLType
column, xml_document
. In both, the XMLType
value is constrained to the PurchaseOrder
element defined by the schema registered under the URL http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd
.
CREATE TABLE purchaseorder_as_table OF XMLType XMLSCHEMA "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd" ELEMENT "PurchaseOrder"; CREATE TABLE purchaseorder_as_column (id NUMBER, xml_document XMLType) XMLTYPE COLUMN xml_document ELEMENT "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd#PurchaseOrder";
There are two ways to specify the XMLSchema
and Element
:
as separator clauses
using the Element
clause with an XPointer notation
The data associated with an XMLType
table or column that is constrained to an XML schema can be stored in two different ways:
The default storage model is structured storage. To override this behavior, and store the entire XML document as a single LOB column, use the STORE AS CLOB
clause.
Example 5-11 Specifying CLOB Storage for Schema-Based XMLType Tables and Columns
This example shows how to create an XMLType
table and a table with an XMLType
column, where the contents of the XMLType
are constrained to a global element defined by a registered XML schema, and the contents of the XMLType
are stored using a single LOB column.
CREATE TABLE purchaseorder_as_table OF XMLType XMLTYPE STORE AS CLOB XMLSCHEMA "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd" ELEMENT "PurchaseOrder"; CREATE TABLE purchaseorder_as_column (id NUMBER, xml_document XMLType) XMLTYPE COLUMN xml_document STORE AS CLOB XMLSCHEMA "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd" ELEMENT "PurchaseOrder";
You can add LOB storage parameters to the STORE AS CLOB
clause.
When structured storage is selected, collections (elements which have maxOccurs
> 1, allowing them to appear multiple times) are mapped into SQL varray values. By default, the entire contents of such a varray is serialized using a single LOB column. This storage model provides for optimal ingestion and retrieval of the entire document, but it has significant limitations when it is necessary to index, update, or retrieve individual members of the collection. A developer may override the way in which a varray is stored, and force the members of the collection to be stored as a set of rows in a nested table. This is done by adding an explicit VARRAY STORE AS
clause to the CREATE TABLE
statement.
Developers can also add STORE AS
clauses for any LOB columns that will be generated by the CREATE TABLE
statement.
The collection and the LOB column must be identified using object-relational notation.
Example 5-12 Specifying Storage Options for Schema-Based XMLType Tables and Columns
This example shows how to create an XMLType
table and a table with an XMLType
column, where the contents of the XMLType
are constrained to a global element defined by a registered XML schema, and the contents of the XMLType
are stored using as a set of SQL objects.
CREATE TABLE purchaseorder_as_table OF XMLType (UNIQUE ("XMLDATA"."Reference"), FOREIGN KEY ("XMLDATA"."User") REFERENCES hr.employees (email)) ELEMENT "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd#PurchaseOrder" VARRAY "XMLDATA"."Actions"."Action" STORE AS TABLE action_table1 ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)) ORGANIZATION INDEX OVERFLOW) VARRAY "XMLDATA"."LineItems"."LineItem" STORE AS TABLE lineitem_table1 ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)) ORGANIZATION INDEX OVERFLOW) LOB ("XMLDATA"."Notes") STORE AS (TABLESPACE USERS ENABLE STORAGE IN ROW STORAGE(INITIAL 4K NEXT 32K)); CREATE TABLE purchaseorder_as_column ( id NUMBER, xml_document XMLType, UNIQUE (xml_document."XMLDATA"."Reference"), FOREIGN KEY (xml_document."XMLDATA"."User") REFERENCES hr.employees (email)) XMLTYPE COLUMN xml_document XMLSCHEMA "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd" ELEMENT "PurchaseOrder" VARRAY xml_document."XMLDATA"."Actions"."Action" STORE AS TABLE action_table2 ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)) ORGANIZATION INDEX OVERFLOW) VARRAY xml_document."XMLDATA"."LineItems"."LineItem" STORE AS TABLE lineitem_table2 ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)) ORGANIZATION INDEX OVERFLOW) LOB (xml_document."XMLDATA"."Notes") STORE AS (TABLESPACE USERS ENABLE STORAGE IN ROW STORAGE(INITIAL 4K NEXT 32K));
The example also shows how to specify that the collection of Action
elements and the collection of LineItem
elements are stored as rows in nested tables, and how to specify LOB storage clauses for the LOB that will contain the content of the Notes
element.
Note:
Use the thick JDBC driver with schema-basedXMLType
values stored object-relationally. (You can use either the thin or the thick driver with CLOB
storage of XMLType
values.)When structured storage is selected, typical relational constraints can be specified for elements and attributes that occur once in the XML document. Example 5-12 shows how to use object-relational notation to define a unique constraint and a foreign key constraint when creating the table.
It is not possible to define constraints for XMLType
tables and columns that make use of unstructured storage.
Oracle XML DB gives application developers the ability to influence the objects and tables that are generated by the XML schema registration process. You use the schema annotation mechanism to do this.
Annotation involves adding extra attributes to the complexType
, element
, and attribute
definitions that are declared by the XML schema. The attributes used by Oracle XML DB belong to the namespace http://xmlns.oracle.com/xdb
. In order to simplify the process of annotationg an XML schema, it is recommended that a namespace prefix be declared in the root element of the XML schema.
Common reasons for wanting to annotate an XML schema include the following:
When GENTYPES
or GENTABLES
is set to TRUE
, schema annotation makes it possible for developers to ensure that the names of the tables, objects, and attributes created by registerSchema
are well-known names, compliant with any application-naming standards.
When GENTYPES
or GENTABLES
is set to 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 identifiers when working directly with SQL.
To allow XPath rewriting in the case of (document-correlated recursive) XPath queries, that is, for certain extract
, extractValue
, and existsNode
applications whose XPath expression targets recursive XML data.
The most commonly used annotations are the following:
defaultTable
– Used to control the name of the default table generated for each global element when the GENTABLES
parameter is FALSE
. Setting this to the empty string ""
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 datatypes and SQL datatypes. A very common use of SQLType
is to define when unbounded strings should be stored as CLOB
values, rather than VARCHAR(4000) CHAR
values (the default).
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. A nested table is created for each element that specifies maxOccurs > 1
. The nested tables are created with system-generated names.
Note:
AnnotationstoreVarrayAsTable="true"
causes element collections to be persisted as rows in an index-organized table (IOT). Oracle Text does not support IOTs. Do not use this annotation if you will need to use Oracle Text indexes for text-based ora:contains
searches over a collection of elements. See "ora:contains Searches Over a Collection of Elements". To provide for searching with Oracle Text indexes:
Set genTables="false"
during schema registration.
Create the necessary tables manually, without using the clause ORGANIZATION INDEX OVERFLOW
, so the tables will be heap-organized instead of index-organized (IOT).
You do not need to specify values for any of these attributes. Oracle XML DB fills in appropriate values during the XML schema registration process. However, it is recommended that you specify the names of at least the top-level SQL types, so that you can reference them later.
Example 5-13 shows a partial listing of the XML schema in Example 5-1, modified to include some of the most important XDB
annotations.
Example 5-13 Using Common Schema Annotations
<xs:schema targetNamespace="http://xmlns.oracle.com/xdb/documentation/purchaseOrder" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:po="http://xmlns.oracle.com/xdb/documentation/purchaseOrder" version="1.0" xdb:storeVarrayAsTable="true"> <xs:element name="PurchaseOrder" type="po:PurchaseOrderType" xdb:defaultTable="PURCHASEORDER"/> <xs:complexType name="PurchaseOrderType" xdb:SQLType="PURCHASEORDER_T"> <xs:sequence> <xs:element name="Reference" type="po:ReferenceType" minOccurs="1" xdb:SQLName="REFERENCE"/> <xs:element name="Actions" type="po:ActionsType" xdb:SQLName="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"/> <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"/> <xs:element name="Notes" type="po:NotesType" xdb:SQLType="CLOB"/> </xs:sequence> </xs:complexType> <xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T"> <xs:sequence> <xs:element name="LineItem" type="po:LineItemType" maxOccurs="unbounded" xdb:SQLCollType="LINEITEM_V" xdb:SQLName="LINEITEM_VARRAY"/> </xs:sequence> </xs:complexType> <xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T"> <xs:sequence> <xs:element name="Description" type="po:DescriptionType"/> <xs:element name="Part" type="po:PartType"/> </xs:sequence> <xs:attribute name="ItemNumber" type="xs:integer"/> </xs:complexType> <xs:complexType name="PartType" xdb:SQLType="PART_T" xdb:maintainDOM="false"> <xs:attribute name="Id"> <xs:simpleType> <xs:restriction base="xs:string"> <xs:minLength value="10"/> <xs:maxLength value="14"/> </xs:restriction> </xs:simpleType> </xs:attribute> <xs:attribute name="Quantity" type="po:moneyType"/> <xs:attribute name="UnitPrice" type="po:quantityType"/> </xs:complexType> </xs:schema>
Note:
As always:SQL is case-insensitive, but names in SQL code are implicitly uppercase, unless you enclose them in double-quotes.
XML is case-sensitive. You must refer to SQL names in XML code using the correct case: uppercase SQL names must be written as uppercase.
For example, if you create a table named my_table
in SQL without using double-quotes, then you must refer to it in XML as "MY_TABLE
".
The schema element includes the declaration of the xdb
namespace. It also includes the annotation xdb:storeVarrayAsTable="true"
. This causes all collections within the XML schema to be managed using nested tables.
The definition of the global element PurchaseOrder
includes a defaultTable
annotation that specifies that the name of the default table associated with this element is purchaseorder
.
The global complexType
PurchaseOrderType
includes a SQLType
annotation that specifies that the name of the generated SQL object type will be purchaseorder_t
. Within the definition of this type, the following annotations are used:
The element Reference
includes a SQLName
annotation that ensures that the name of the SQL attribute corresponding to the Reference
element will be named reference
.
The element Actions
includes a SQLName
annotation that ensures that the name of the SQL attribute corresponding to the Actions
element will be action_collection
.
The element USER
includes a SQLName
annotation that ensures that the name of the SQL attribute corresponding to the User
element will be email
.
The element LineItems
includes a SQLName
annotation that ensures that the name of the SQL attribute corresponding to the LineItems
element will be lineitem_collection
.
The element Notes
includes a SQLType
annotation that ensures that the datatype of the SQL attribute corresponding to the Notes
element will be CLOB
.
The global complexType
LineItemsType
includes a SQLType
annotation that specifies that the names of generated SQL object type will be lineitems_t
. Within the definition of this type, the following annotations are used:
The element LineItem
includes a SQLName
annotation that ensures that the datatype of the SQL attribute corresponding to the LineItems
element will be lineitem_varray
, and a SQLCollName
annotation that ensures that the name of the SQL object type that manages the collection will be lineitem_v
.
The global complexType
LineItemType
includes a SQLType
annotation that specifies that the names of generated SQL object type will be lineitem_t
.
The global complexType
PartType
includes a SQLType
annotation that specifies that the names of generated SQL object type will be part_t
. It also includes the annotation xdb:maintainDOM="false"
, specifying that there is no need for Oracle XML DB to maintain DOM fidelity for elements based on this type.
Example 5-14 Results of Registering an Annotated XML Schema
The following code shows some of the tables and objects created when the annotated XML schema is registered.
BEGIN DBMS_XMLSCHEMA.registerSchema( schemaurl => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd', schemadoc => bfilename('XMLDIR', 'purchaseOrder.Annotated.xsd'), local => TRUE, gentypes => TRUE, gentables => TRUE, CSID => nls_charset_id('AL32UTF8')); END; / SELECT table_name, xmlschema, element_name FROM user_xml_tables; TABLE_NAME XMLSCHEMA ELEMENT_NAME ------------- ----------------------------------- ------------- PURCHASEORDER http://xmlns.oracle.com/xdb/documen PurchaseOrder tation/purchaseOrder.xsd 1 row selected. DESCRIBE purchaseorder Name Null? Type ------------------------------ ----- ----------------- TABLE of SYS.XMLTYPE(XMLSchema "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd" ELEMENT "PurchaseOrder") STORAGE Object-relational TYPE "PURCHASEORDER_T" DESCRIBE purchaseorder_t PURCHASEORDER_T is NOT FINAL Name Null? Type -------------------- ----- -------------------------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T REFERENCE VARCHAR2(30 CHAR) ACTION_COLLECTION ACTIONS_T REJECT REJECTION_T REQUESTOR VARCHAR2(128 CHAR) EMAIL VARCHAR2(10 CHAR) COSTCENTER VARCHAR2(4 CHAR) SHIPPINGINSTRUCTIONS SHIPPING_INSTRUCTIONS_T SPECIALINSTRUCTIONS VARCHAR2(2048 CHAR) LINEITEM_COLLECTION LINEITEMS_T Notes CLOB DESCRIBE lineitems_t LINEITEMS_T is NOT FINAL Name Null? Type -------------------- ----- -------------------------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T LINEITEM_VARRAY LINEITEM_V DESCRIBE lineitem_v LINEITEM_V VARRAY(2147483647) OF LINEITEM_T LINEITEM_T is NOT FINAL Name Null? Type -------------------- ----- -------------------------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T ITEMNUMBER NUMBER(38) DESCRIPTION VARCHAR2(256 CHAR) PART PART_T DESCRIBE part_t PART_T is NOT FINAL Name Null? Type -------------------- ----- -------------------------- ID VARCHAR2(14 CHAR) QUANTITY NUMBER(12,2) UNITPRICE NUMBER(8,4) SELECT table_name, parent_table_column FROM user_nested_tables WHERE parent_table_name = 'purchaseorder'; TABLE_NAME PARENT_TABLE_COLUMN ---------- ----------------------- SYS_NTNOHV+tfSTRaDTA9FETvBJw== "XMLDATA"."LINEITEM_COLLECTION"."LINEITEM_VARRAY" SYS_NTV4bNVqQ1S4WdCIvBK5qjZA== "XMLDATA"."ACTION_COLLECTION"."ACTION_VARRAY" 2 rows selected.
A table called purchaseorder
has been created.
Types called purchaseorder_t
, lineitems_t
, lineitem_v
, lineitem_t
, and part_t
have been created. The attributes defined by these types are named according to supplied the SQLName
annotations.
The Notes
attribute defined by purchaseorder_t
has a datatype of CLOB
.
Type part_t
does not include a Positional Descriptor attribute.
Nested tables have been created to manage the collections of LineItem
and Action
elements.
Table 5-2 lists Oracle XML DB annotations that you can specify in element and attribute declarations.
Table 5-2 Annotations in Elements
Attribute | Values | Default | Description |
---|---|---|---|
|
Any SQL identifier |
Element name |
Specifies the name of the attribute within the SQL object that maps to this XML element. |
|
Any SQL type name |
Name generated from element name |
Specifies the name of the SQL type corresponding to this XML element declaration. |
|
Any SQL collection type name |
Name generated from element name |
Specifies the name of the SQL collection type corresponding to this XML element that has |
|
Any SQL username |
User registering XML schema |
Name of database user owning the type specified by |
|
Any SQL username |
User registering XML schema |
Name of database user owning the type specified by |
|
|
|
If If |
|
|
|
If If |
|
|
|
If If |
|
Any valid column storage clause |
|
Specifies the column storage clause that is inserted into the default |
|
Any valid table storage clause |
|
Specifies the |
|
Any table name |
Based on element name. |
Specifies the name of the table into which XML instances of this schema should be stored. This is most useful in cases when the XML is being inserted from APIs and protocols where table name is not specified, such as FTP and HTTP(S). |
Table 5-3 Annotations in Elements Declaring Global complexTypes
Attribute | Values | Default | Description |
---|---|---|---|
|
Any SQL type name |
Name generated from element name |
Specifies the name of the SQL type corresponding to this XML element declaration. |
|
Any SQL username |
User registering XML schema |
Name of database user owning the type specified by |
|
|
|
If If |
Table 5-4 Annotations in XML Schema Declarations
Attribute | Values | Default | Description |
---|---|---|---|
|
|
|
If If |
|
|
|
If If |
Note:
AnnotationstoreVarrayAsTable="true"
causes element collections to be persisted as rows in an index-organized table (IOT). Oracle Text does not support IOTs. Do not use this annotation if you will need to use Oracle Text indexes for text-based ora:contains
searches over a collection of elements. See "ora:contains Searches Over a Collection of Elements". To provide for searching with Oracle Text indexes:
Set genTables="false"
during schema registration.
Create the necessary tables manually, without using the clause ORGANIZATION INDEX OVERFLOW
, so the tables will be heap-organized instead of index-organized (IOT).
The registered version of an XML schema will contain a full set of XDB annotations. As was shown in Example 5-8, and Example 5-9, the location of the registered XML schema depends on whether the schema is a local or global schema.
This document can be queried to find out the values of the annotations that were supplied by the user, or added by the schema registration process. For instance, the following query shows the set of global complexType
definitions declared by the XMLSchema and the corresponding SQL object types and DOM fidelity values.
Example 5-15 Querying Metadata from a Registered XML Schema
SELECT extractValue(value(ct), '/xs:complexType/@name', 'xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"') XMLSCHEMA_TYPE_NAME, extractValue(value(ct), '/xs:complexType/@xdb:SQLType', 'xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"') SQL_TYPE_NAME, extractValue(value(ct), '/xs:complexType/@xdb:maintainDOM', 'xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"') DOM_FIDELITY FROM RESOURCE_VIEW, table( XMLSequence( extract( res, '/r:Resource/r:Contents/xs:schema/xs:complexType', 'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd" xmlns:po="http://xmlns.oracle.com/xdb/documentation/purchaseOrder" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"'))) ct WHERE equals_path( res, '/sys/schemas/SCOTT/xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd') =1; XMLSCHEMA_TYPE_NAME SQL_TYPE_NAME DOM_FIDELITY ------------------------- ----------------------- ------------ PurchaseOrderType PURCHASEORDER_T true LineItemsType LINEITEMS_T true LineItemType LINEITEM_T true PartType PART_T true ActionsType ACTIONS_T true RejectionType REJECTION_T true ShippingInstructionsType SHIPPING_INSTRUCTIONS_T true 7 rows selected.
Information regarding the SQL mapping is stored in the XML schema document. The registration process generates the SQL types, as described in "Mapping Types with DBMS_XMLSCHEMA" and adds annotations to the XML schema document to store the mapping information. Annotations are in the form of new attributes.
Example 5-16 Capturing SQL Mapping Using SQLType and SQLName Attributes
The following XML schema definition shows how SQL mapping information is captured using SQLType
and SQLName
attributes:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" version="1.0" xdb:storeVarrayAsTable="true"> <xs:element name="PurchaseOrder" type="PurchaseOrderType" xdb:defaultTable="PURCHASEORDER"/> <xs:complexType name="PurchaseOrderType" xdb:SQLType="PURCHASEORDER_T"> <xs:sequence> <xs:element name="Reference" type="ReferenceType" minOccurs="1" xdb:SQLName="REFERENCE"/> <xs:element name="Actions" type="ActionsType" xdb:SQLName="ACTIONS"/> <xs:element name="Reject" type="RejectionType" minOccurs="0" xdb:SQLName="REJECTION"/> <xs:element name="Requestor" type="RequestorType" xdb:SQLName="REQUESTOR"/> <xs:element name="User" type="UserType" minOccurs="1" xdb:SQLName="USERID"/> <xs:element name="CostCenter" type="CostCenterType" xdb:SQLName="COST_CENTER"/> <xs:element name="ShippingInstructions" type="ShippingInstructionsType" xdb:SQLName="SHIPPING_INSTRUCTIONS"/> <xs:element name="SpecialInstructions" type="SpecialInstructionsType" xdb:SQLName="SPECIAL_INSTRUCTIONS"/> <xs:element name="LineItems" type="LineItemsType" xdb:SQLName="LINEITEMS"/> <xs:element name="Notes" type="po:NotesType" xdb:SQLType="CLOB"/> </xs:sequence> </xs:complexType> <xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T"> <xs:sequence> <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded" xdb:SQLName="LINEITEM" xdb:SQLCollType="LINEITEM_V"/> </xs:sequence> </xs:complexType> <xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T"> <xs:sequence> <xs:element name="Description" type="DescriptionType" xdb:SQLName="DESCRIPTION"/> <xs:element name="Part" type="PartType" xdb:SQLName="PART"/> </xs:sequence> <xs:attribute name="ItemNumber" type="xs:integer" xdb:SQLName="ITEMNUMBER" xdb:SQLType="NUMBER"/> </xs:complexType> <xs:complexType name="PartType" xdb:SQLType="PART_T"> <xs:attribute name="Id" xdb:SQLName="PART_NUMBER" xdb:SQLType="VARCHAR2"> <xs:simpleType> <xs:restriction base="xs:string"> <xs:minLength value="10"/> <xs:maxLength value="14"/> </xs:restriction> </xs:simpleType> </xs:attribute> <xs:attribute name="Quantity" type="moneyType" xdb:SQLName="QUANTITY"/> <xs:attribute name="UnitPrice" type="quantityType" xdb:SQLName="UNITPRICE"/> </xs:complexType> ... <xs:complexType name="ActionsType" xdb:SQLType="ACTIONS_T"> <xs:sequence> <xs:element name="Action" maxOccurs="4" xdb:SQLName="ACTION" xdb:SQLCollType="ACTION_V"> <xs:complexType xdb:SQLType="ACTION_T"> <xs:sequence> <xs:element name="User" type="UserType" xdb:SQLName="ACTIONED_BY"/> <xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_ACTIONED"/> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> <xs:complexType name="RejectionType" xdb:SQLType="REJECTION_T"> <xs:all> <xs:element name="User" type="UserType" minOccurs="0" xdb:SQLName="REJECTED_BY"/> <xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_REJECTED"/> <xs:element name="Comments" type="CommentsType" minOccurs="0" xdb:SQLName="REASON_REJECTED"/> </xs:all> </xs:complexType> <xs:complexType name="ShippingInstructionsType" xdb:SQLType="SHIPPING_INSTRUCTIONS_T"> <xs:sequence> <xs:element name="name" type="NameType" minOccurs="0" xdb:SQLName="SHIP_TO_NAME"/> <xs:element name="address" type="AddressType" minOccurs="0" xdb:SQLName="SHIP_TO_ADDRESS"/> <xs:element name="telephone" type="TelephoneType" minOccurs="0" xdb:SQLName="SHIP_TO_PHONE"/> </xs:sequence> </xs:complexType> ... </xs:schema>
Figure 5-2 shows how Oracle XML DB creates XML schema-based XMLType
tables using an XML document and mapping specified in an XML schema.
Figure 5-2 How Oracle XML DB Maps XML Schema-Based XMLType Tables
An XMLType
table is first created and depending on how the storage is specified in the XML schema, the XML document is mapped and stored either as a CLOB
value in one XMLType
column, or stored object-relationally and spread out across several columns in the table.
Use PL/SQL package DBMS_XMLSCHEMA
to map types for attributes and elements.
An attribute declaration can have its type specified in terms of one of the following:
Primitive type
Global simpleType
, declared within this XML schema or in an external XML schema
Reference to global attribute (ref=".."
), declared within this XML schema or in an external XML schema
Local simpleType
In all cases, the SQL type and associated information (length and precision) as well as the memory mapping information, are derived from the simpleType
on which the attribute is based.
You can explicitly specify a SQLType
value in the input XML schema document. In this case, your specified type is validated. This allows for the following specific forms of overrides:
If the default type is a STRING
, then you can override it with any of the following: CHAR
, VARCHAR
, or CLOB
.
If the default type is RAW
, then you can override it with RAW
or BLOB
.
An element declaration can specify its type in terms of one of the following:
Any of the ways for specifying type for an attribute declaration. See "Setting Attribute Mapping Type Information" .
Global complexType
, specified within this XML schema document or in an external XML schema.
Reference to a global element (ref="..."
), which could itself be within this XML schema document or in an external XML schema.
Local complexType
.
An element based on a complexType
is, by default, mapped to an object type containing attributes corresponding to each of the sub-elements and attributes. However, you can override this mapping by explicitly specifying a value for SQLType
attribute in the input XML schema. The following values for SQLType
are permitted in this case:
VARCHAR2
RAW
CLOB
BLOB
These represent storage of the XML in a text or unexploded form in the database.
For example, to override the SQLType
from VARCHAR2
to CLOB
declare the XDB namespace as follows:
xmlns:xdb="http://xmlns.oracle.com/xdb"
and then use xdb:SQLType="CLOB"
.
The following special cases are handled:
If a cycle is detected when processing the complexType
values that are used to declare elements and the elements declared within the complexType
, the SQLInline
attribute is forced to be false
and the correct SQL mapping is set to REF
XMLType
.
If maxOccurs > 1
, a varray type may be created.
If SQLInline
="true"
, then a varray type is created whose element type is the SQL type previously determined.
Cardinality of the varray is determined based on the value of maxOccurs
attribute.
The name of the varray type is either explicitly specified by the user using SQLCollType
attribute or obtained by mangling the element name.
If SQLInline="false"
, then the SQL type is set to XDB.XDB$XMLTYPE_REF_LIST_T
, a predefined type representing an array of REF
values to XMLType
.
If the element is a global element, or if SQLInline="false"
, then the system creates a default table. The name of the default table is specified by you or derived by mangling the element name.
See Also:
Chapter 7, "XML Schema Storage and Query: Advanced" for more information about mappingsimpleType
values and complexType
values to SQL.This section describes how XML schema definitions map XML Schema simpleType
to SQL object types. Figure 5-3 shows an example of this.
Table 5-5 through Table 5-8 list the default mapping of XML Schema simpleType
to SQL, as specified in the XML Schema definition. For example:
An XML primitive type is mapped to the closest SQL datatype. For example, DECIMAL
, POSITIVEINTEGER
, and FLOAT
are all mapped to SQL NUMBER
.
An XML enumeration type is mapped to an object type with a single RAW(
n
)
attribute. The value of n
is determined by the number of possible values in the enumeration declaration.
An XML list or a union datatype is mapped to a string (VARCHAR2
or CLOB
) datatype in SQL.
Figure 5-3 Mapping simpleType: XML Strings to SQL VARCHAR2 or CLOBs
Table 5-5 Mapping XML String Datatypes to SQL
XML Primitive Type | Length or MaxLength Facet | Default Mapping | Compatible Datatype |
---|---|---|---|
|
n |
|
|
|
- |
|
|
Table 5-6 Mapping XML Binary Datatypes (hexBinary/base64Binary) to SQL
XML Primitive Type | Length or MaxLength Facet | Default Mapping | Compatible Datatype |
---|---|---|---|
|
n |
|
|
|
- |
|
|
Table 5-7 Default Mapping of Numeric XML Primitive Types to SQL
XML Simple Type | Default Oracle DataType | totalDigits (m), fractionDigits(n) Specified | Compatible Datatypes |
---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Table 5-8 Mapping XML Date Datatypes to SQL
XML Primitive Type | Default Mapping | Compatible Datatypes |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
none |
Table 5-9 Default Mapping of Other XML Primitive Datatypes to SQL
XML Simple Type | Default Oracle DataType | Compatible Datatypes |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- |
NVARCHAR and NCHAR SQLType Values are Not Supported
Oracle XML DB does not support NVARCHAR
or NCHAR
as a SQLType
when registering an XML schema. In other words in the XML schema .xsd
file you cannot specify that an element should be of type NVARCHAR
or NCHAR
. Also, if you provide your own type you should not use these datatypes.
If the XML schema specifies the datatype to be a string with a maxLength
value of less than 4000, then it is mapped to a VARCHAR2
attribute of the specified length. However, if maxLength
is not specified in the XML schema, then it can only be mapped to a LOB. This is sub-optimal when most of the string values are small and only a small fraction of them are large enough to need a LOB.
The following XML Schema types allow for an optional time-zone indicator as part of their literal values.
xsd:dateTime
xsd:time
xsd:date
xsd:gYear
xsd:gMonth
xsd:gDay
xsd:gYearMonth
xsd:gMonthDay
By default, the schema registration maps xsd:dateTime
and xsd:time
to SQL TIMESTAMP
and all the other datatypes to SQL DATE
. The SQL TIMESTAMP
and DATE
types do not permit the time-zone indicator.
However, if the application needs to work with time-zone indicators, then the schema should explicitly specify the SQL type to be TIMESTAMP WITH TIME ZONE
, using the xdb:SQLType
attribute. This ensures that values containing time-zone indicators can be stored and retrieved correctly.
Example:
<element name="dob" type="xsd:dateTime" xdb:SQLType="TIMESTAMP WITH TIME ZONE"/> <attribute name="endofquarter" type="xsd:gMonthDay" xdb:SQLType="TIMESTAMP WITH TIME ZONE"/>
XML Schema allows the time-zone component to be specified as Z
to indicate UTC time zone. When a value with a trailing Z
is stored in a TIMESTAMP WITH TIME ZONE
column, the time zone is actually stored as +00:00
. Thus, the retrieved value contains the trailing +00:00
and not the original Z
.
For example, if the value in the input XML document is 1973-02-12T13:44:32Z
, the output will look like 1973-02-12T13:44:32.000000+00:00
.
Using XML Schema, a complexType
is mapped to a SQL object type as follows:
XML attributes declared within the complexType
are mapped to object attributes. The simpleType
defining the XML attribute determines the SQL datatype of the corresponding attribute.
XML elements declared within the complexType
are also mapped to object attributes. The datatype of the object attribute is determined by the simpleType
or complexType
defining the XML element.
If the XML element is declared with attribute maxOccurs
> 1, then it is mapped to a collection
attribute in SQL. The collection could be a varray value (default) or nested table if the maintainOrder
attribute is set to false. Further, the default storage of the varray value is in Ordered Collections in Tables (OCTs) instead of LOBs. You can choose LOB storage by setting the storeAsLob
attribute to true.
When you have an element based on a global complexType
, the SQLType
and SQLSchema
attributes must be specified for the complexType
declaration. In addition you can optionally include the same SQLType
and SQLSchema
attributes within the element declaration.
The reason is that if you do not specify the SQLType
for the global complexType
, Oracle XML DB creates a SQLType
with an internally generated name. The elements that reference this global type cannot then have a different value for SQLType
. In other words, the following code is fine:
<xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T"> <xs:sequence> <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded" xdb:SQLName="LINEITEM" xdb:SQLCollType="LINEITEM_V"/> </xs:sequence> </xs:complexType> <xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T"> <xs:sequence> <xs:element name="Description" type="DescriptionType" xdb:SQLName="DESCRIPTION"/> <xs:element name="Part" type="PartType" xdb:SQLName="PART"/> </xs:sequence> <xs:attribute name="ItemNumber" type="xs:integer" xdb:SQLName="ITEMNUMBER" xdb:SQLType="NUMBER"/> </xs:complexType> <xs:complexType name="PartType" xdb:SQLType="PART_T"> <xs:attribute name="Id" xdb:SQLName="PART_NUMBER" xdb:SQLType="VARCHAR2"> <xs:simpleType> <xs:restriction base="xs:string"> <xs:minLength value="10"/> <xs:maxLength value="14"/> </xs:restriction> </xs:simpleType> </xs:attribute> <xs:attribute name="Quantity" type="moneyType" xdb:SQLName="QUANTITY"/> <xs:attribute name="UnitPrice" type="quantityType" xdb:SQLName="UNITPRICE"/> </xs:complexType>
Note:
As always:SQL is case-insensitive, but names in SQL code are implicitly uppercase, unless you enclose them in double-quotes.
XML is case-sensitive. You must refer to SQL names in XML code using the correct case: uppercase SQL names must be written as uppercase.
For example, if you create a table named my_table
in SQL without using double-quotes, then you must refer to it in XML as "MY_TABLE
".