Creating XML Schema-Based XMLType Views

While the Using XML Schemas section earlier in this chapter focuses on how the XML Schema feature of Oracle XML DB can be used to create an XML schema-based storage structure, this section discusses how XML schema functionality might be used when working with existing relational data, without having to change the physical structure of that data.

NOTE: Creating an XML schema-based XMLType view is the most common way to take advantage of XML schema functionality when dealing with data stored relationally.

However, before you create an XML schema-based XMLType view, you must have the appropriate XML schema created and registered against the database. By executing the statement shown overleaf, you create and register the emp.xsd XML schema on which you will then create an XMLType view.

As you can see from the listing, the EMPLOYEE element, which is the root element of the employee XML document described by this schema, is mapped to the EMP_T SQL object type. This object type will be automatically generated during schema registration as long as you set the DBMS_XMLSCHEMA.registerschema's fourth parameter, which is actually called GENTYPES, to TRUE.

At the same time, you set the sixth (GENTABLES) parameter to FALSE, thus instructing Oracle not to create any tables during schema registration. This makes sense in this case because you are going to map between this XML schema and an existing relational table later, with the help of an XMLType view. After the PL/SQL block shown in the listing has been successfully executed, you might issue the DESC SQL command in order to make sure that the EMP_T object type was generated:

DESC EMP_T

This should return the following result:

EMP_T is NOT FINAL Name Null? Type ----------------------------------------- -------- ----------------- EMPNO NUMBER(38) ENAME VARCHAR2(4000 CHAR) SALARY NUMBER

Since DOM fidelity is not required when it comes to wrapping relational data in XML, you set the attribute maintainDOM to FALSE. As a result, the EMP_T type, unlike the EMPLOYEE_T type created as discussed in the Using XML Schemas section earlier, doesn't contain the SYS_XDBPD$ attribute.

The XML schema defined in the listing contains an example of how to add a constraint to an element described in the schema, restricting its content to values matching a set of conditions. In particular, you restrict the value of node SALARY in all employee XML documents conforming to the schema to be less than 100 000. To achieve this, you use a maxExclusive element under the restriction element defined in turn under the simpleType element for the SALARY element.

The following listing shows how to set up an XML schema-based XMLType view based on the hr.employees relational table. The view created here conforms to the employee XML schema created as discussed at the beginning of this section.

In the above listing, you start by creating relational table emps based on the hr.employees table. For simplicity, you include only three columns in the newly created table, while loading all the rows from hr.employees.

By specifying employee.xsd in the XMLSCHEMA clause and EMPLOYEE in the ELEMENT clause of the CREATE VIEW statement, you constrain a resultant row object in the view to be an instance of the element EMPLOYEE defi ned in the emp.xsd XML schema.

Since row objects in the empSch_v XMLType object view are synthesized from relational data, you must explicitly choose a set of unique identifiers to be used as object identifiers. In this example, in the WITH clause you specify the id attribute of the EMPLOYEE element as the object identifier because it is unique within the view row objects.

In the select list of the view, you explicitly convert the data retrieved from the relational table emps to the EMP_T SQL object type specifi ed for the EMPLOYEE element in the emp.xsd XML schema.

First Page: Using XMLType Views