The simplest way to create an XMLType storage structure in Oracle XML DB is by registering an appropriate XML schema against the database. As a part of the registration process, Oracle automatically creates the storage for a particular set of XML documents, based on the information provided by the schema.
NOTE: An XML schema can be thought of as the metadata describing a certain class of XML documents. So, an XML document conforming to a particular XML schema can be considered as an instance of this XML schema.
You might want to use an XML schema for:
However, before you can use an XML schema, you have to create and then register it against the database. Both these tasks can be accomplished in one step with the registerschema procedure from the DBMS_XMLSCHEMA PL/SQL package. For example, to register an XML schema to which the following employee XML document conforms:
You might issue the following statements:
As you can see, the DBMS_XMLSCHEMA.registerschema procedure takes two arguments. The first one is the string representing the name under which you want to register the schema against the database, and the other one is the document containing the schema itself.
In this example, the root element of the XML schema includes two namespace declarations, namely the XML schema namespace declaration and Oracle XML DB namespace declaration. To denote these namespaces, you use prefixes: xs and xdb respectively.
By including the XML schema namespace declaration, you obtain the ability to use the elements and attributes defined in this namespace, as well as the data types defined by the XML Schema language. For example, in the above example you specify the positiveInteger XML Schema language data type for the id attribute of the EMPLOYEE element.
The Oracle XML DB namespace lets you use annotations in the schema. For example, you use the xdb:defaultTable annotation to tell Oracle to use the specified table name when generating an XMLType table that will be used for storing XML documents conforming to the schema, rather than using a system-generated name for that table. In this particular example, you specify EMPLOYEES as the name for this table.
Another interesting annotation used in this XML schema is the xdb:columnProps. In this example, you use this annotation to defi ne a primary key on the EMPLOYEE element's id attribute mapped to the EMPNO attribute of the EMPLOYEE_T SQL object type.
By including the xdb:SQLName annotation you make sure that the name of the generated SQL object type will be EMPLOYEE_T.
Finally, note the use of the flags passed to the DBMS_XMLSCHEMA.registerschema procedure:
TRUE, TRUE, FALSE, TRUE
The above flags indicate the following (in the same order as they appear in the listing):
After registering the schema, you might want to look at the database object generated during the registration. The following listing contains the SQL statements that you might issue from SQL*Plus to make sure that Oracle generated the objects annotated in the schema. For convenience, the listing also contains the output produced.
As you can see, Oracle generated the employee_t object type and employees XMLType table based on this object type, as a part of the XML schema registration process. Note that the names of the generated objects have been defined in the schema. If you recall, you set the value of the xdb:SQLName attribute of global element EMPLOYEE to EMPLOYEE_T, and the xdb:defaultTable attribute to EMPLOYEES.
NOTE: It's interesting to note that the names of database objects generated during the XML schema registration process are case sensitive. However, since SQL is case insensitive, you can refer to these objects in SQL disregarding the case of their names. The names of XML elements and attributes specified in an XML schema are also case sensitive. However, unlike SQL, XML is case-sensitive, which means you must refer to XML elements and attributes in XML code using the case with which they were defined in the XML schema.
PHP Oracle Training Tutorials