18 XMLType Views

This chapter describes how to create and use XMLType views.

This chapter contains these topics:

What Are XMLType Views?
Creating Non-Schema-Based XMLType Views
Creating XML Schema-Based XMLType Views
Creating XMLType Views From XMLType Tables
Referencing XMLType View Objects Using REF()
DML (Data Manipulation Language) on XMLType Views
XPath Rewrite on XMLType Views
Generating XML Schema-Based XML Without Creating Views

What Are XMLType Views?

XMLType views wrap existing relational and object-relational data in XML formats. The major advantages of using XMLType views are:

You can exploit Oracle XML DB XML features that use XML schema functionality without having to migrate your base legacy data.
With XMLType views, you can experiment with various other forms of storage, besides the object-relational or CLOB storage alternatives available to XMLType tables.

XMLType views are similar to object views. Each row of an XMLType view corresponds to an XMLType instance. The object identifier for uniquely identifying each row in the view can be created using a function such as extract with getNumberVal() applied to the XMLType result. It is recommended that you use SQL function extract rather than XMLType method extract() in the OBJECT IDENTIFIER clause.

Throughout this chapter XML schema refers to the W3C XML Schema 1.0 recommendation, http://www.w3.org/XML/Schema.

There are two types of XMLType views:

Non-schema-based XMLType views. These views do not confirm to a particular XML schema.
XML schema-based XMLType views. As with XMLType tables, XMLType views that conform to a particular XML schema are called XML schema-based XMLType views. These provide stronger typing than non-schema-based XMLType views.

Optimization of queries over XMLType views are enabled for both XML schema-based and non-schema-based XMLType views. This is known as XPath rewrite, and is described in the section, "XPath Rewrite on XMLType Views".

To create an XML schema-based XMLType view, first register your XML schema. If the XML schema-based XMLType view is constructed using an object type -- object view, then the XML schema should have annotations that represent the bi-directional mapping from XML to SQL object types. XMLType views conforming to this registered XML schema can then be created by providing an underlying query that constructs instances of the appropriate SQL object type.

Creating XMLType Views: Syntax

Figure 18-1 shows the CREATE VIEW clause for creating XMLType views. See Oracle Database SQL Reference for details on the CREATE VIEW syntax.

Figure 18-1 Creating XMLType Views Clause: Syntax

Description of "Figure 18-1 Creating XMLType Views Clause: Syntax"

Creating Non-Schema-Based XMLType Views

Non-schema-based XMLType views are XMLType views whose resultant XML value is not constrained to be a particular element in a registered XML schema. There are two main ways to create non-schema-based XMLType views:

Using SQL/XML generation functions, such as XMLElement, XMLForest, XMLConcat, XMLAgg, and XMLColAttVal. Here you create the XMLType view using simple SQL/XML generation functions, without creating object types. Creating XMLType views using SQL/XML functions is simple as you do not have to create object types or object views.

See Also:
Chapter 16, "Generating XML Data from the Database", for details on SQL/XML generation functions
Using object types and object views with SQL function sys_XMLGen. Here you create the XMLType view using object types with sys_XMLGen. This way of creating XMLType views is convenient when you already have an object-relational schema, such as object types, views, and tables, and want to map it directly to XML without the overhead of creating XML schema.
See Also:
"Using Object Types and Views to Create XML Schema-Based XMLType Views"

Using SQL/XML Generation Functions to Create Non-Schema-Based XMLType Views

Example 18-1 illustrates how to create an XMLType view using the SQL/XML function XMLELement().

Example 18-1 Creating an XMLType View Using XMLELEMENT

The following statement creates an XMLType view using SQL function XMLElement:

CREATE OR REPLACE VIEW emp_view OF XMLType WITH OBJECT ID
  (extract(OBJECT_VALUE, '/Emp/@empno').getnumberval())
  AS SELECT XMLElement("Emp", 
                       XMLAttributes(employee_id),
                       XMLForest(e.first_name ||' '|| e.last_name AS "name",
                                 e.hire_date AS "hiredate"))
       AS "result"
       FROM employees e
       WHERE salary > 15000;
          
SELECT * FROM emp_view;
 
SYS_NC_ROWINFO$
-------------------------------------------------------------------------------------
<Emp EMPLOYEE_ID="100"><name>Steven King</name><hiredate>1987-06-17</hiredate></Emp> 
<Emp EMPLOYEE_ID="101"><name>Neena Kochhar</name><hiredate>1989-09-21</hiredate></Emp> 
<Emp EMPLOYEE_ID="102"><name>Lex De Haan</name><hiredate>1993-01-13</hiredate></Emp>

The empno attribute in the document will be used as the unique identifier for each row. As the result of the XPath rewrite operation, the XPath /Emp/@empno can refer directly to the empno column.

Existing data in relational tables or views can be exposed as XML using this mechanism. If a view is generated using a SQL/XML generation function, then queries that access the view with XPath expressions can often be optimized (rewritten). The optimized queries can then directly access the underlying relational columns. See "XPath Rewrite on XMLType Views" for details.

You can perform DML operations on these XMLType views, but, in general, you must write instead-of triggers to handle the DML operation.

Using Object Types with SYS_XMLGEN to Create Non-Schema-Based XMLType Views

You can also create XMLType views using SQL function sys_XMLGen with object types. Function sys_XMLGen inputs object type and generates an XMLType. Here is an equivalent query that produces the same query results using sys_XMLGen:

Example 18-2 Creating an XMLType View Using Object Types and SYS_XMLGEN

CREATE TYPE emp_t AS OBJECT ("@empno"  NUMBER(6), 
                             fname     VARCHAR2(20),
                             lname     VARCHAR2(25), 
                             hiredate  DATE);
/
CREATE OR REPLACE VIEW employee_view OF XMLType
  WITH OBJECT ID (extract(OBJECT_VALUE, '/Emp/@empno').getnumberval()) AS
  SELECT sys_XMLGen(emp_t(e.employee_id, e.first_name, e.last_name, e.hire_date),
                    XMLFormat('EMP'))
       FROM employees e
       WHERE salary > 15000;
 
SELECT * FROM employee_view;
 
SYS_NC_ROWINFO$
--------------------------------------------------------
<?xml version="1.0"?
<EMP empno="100"> 
  <FNAME>Steven</FNAME> 
  <LNAME>King</LNAME> 
  <HIREDATE>17-JUN-87</HIREDATE> 
</EMP> 
 
<?xml version="1.0"?> 
<EMP empno="101"> 
  <FNAME>Neena</FNAME> 
  <LNAME>Kochhar</LNAME> 
  <HIREDATE>21-SEP-89</HIREDATE> 
</EMP> 
 
<?xml version="1.0"?> 
<EMP empno="102"> 
  <FNAME>Lex</FNAME> 
  <LNAME>De Haan</LNAME> 
  <HIREDATE>13-JAN-93</HIREDATE> 
</EMP>

Existing data in relational or object-relational tables or views can be exposed as XML using this mechanism. In addition, queries using SQL functions extract, extractValue, and existsNode that involve simple XPath traversal over views generated by function sys_XMLGen, are candidates for XPath rewrite. XPath rewrite facilitates direct access to underlying object attributes or relational columns.

Creating XML Schema-Based XMLType Views

XML schema-based XMLType views are XMLType views whose resultant XML value is constrained to be a particular element in a registered XML schema. There are two main ways to create XML schema-based XMLType views:

Using SQL/XML generation functions, such as XMLElement, XMLForest, XMLConcat, XMLAgg and XMLColAttVal: Here you create the XMLType view using simple XML generation functions, without needing to create any object types. This mechanism is simple as you do not have to create any object types or object views.

See Also:
"Using SQL/XML Generation Functions to Create XML Schema-Based XMLType Views"
Using object types and or object views. Here you create the XMLType view either using object types or from object views. This mechanism for creating XMLType views is convenient when you already have an object-relational schema and want to map it directly to XML.

See Also:
"Using Object Types and Views to Create XML Schema-Based XMLType Views"

Using SQL/XML Generation Functions to Create XML Schema-Based XMLType Views

You can use SQL/XML generation functions to create XML schema-based XMLType views in a similar way as for the non-schema-based case described in section "Creating Non-Schema-Based XMLType Views". To create XML schema-based XMLType views perform these steps:

Create and register the XML schema document that contains the necessary XML structures. Note that since the XMLType view is constructed using SQL/XML generation functions, you do not need to annotate the XML schema to present the bidirectional mapping from XML to SQL object types.
Create an XMLType view conforming to the XML schema by using SQL/XML functions.

These two steps are illustrated in Example 18-3 and Example 18-4, respectively.

Example 18-3 Registering XML Schema emp_simple.xsd

Assume that you have an XML schema emp_simple.xsd that contains XML structures defining an employee. This example shows how to register the XML schema and identify it using a URL.

BEGIN
  DBMS_XMLSCHEMA.registerSchema('http://www.oracle.com/emp_simple.xsd',
    '<schema xmlns="http://www.w3.org/2001/XMLSchema"
             targetNamespace="http://www.oracle.com/emp_simple.xsd" version="1.0"
             xmlns:xdb="http://xmlns.oracle.com/xdb" 
             elementFormDefault="qualified"> 
       <element name = "Employee"> 
         <complexType> 
           <sequence> 
             <element name = "EmployeeId" type = "positiveInteger"/> 
             <element name = "Name" type = "string"/> 
             <element name = "Job" type = "string"/> 
             <element name = "Manager" type = "positiveInteger"/> 
             <element name = "HireDate" type = "date"/> 
             <element name = "Salary" type = "positiveInteger"/> 
             <element name = "Commission" type = "positiveInteger"/> 
             <element name = "Dept"> 
               <complexType> 
                 <sequence> 
                   <element name = "DeptNo" type = "positiveInteger" /> 
                   <element name = "DeptName" type = "string"/> 
                   <element name = "Location" type = "positiveInteger"/> 
                 </sequence> 
               </complexType> 
             </element> 
           </sequence> 
         </complexType> 
       </element> 
     </schema>',
    TRUE, 
    TRUE, 
    FALSE);
END;

This registers the XML schema with the target location:

http://www.oracle.com/emp_simple.xsd

You can create an XML schema-based XMLType view using SQL/XML functions. The resultant XML must conform to the XML schema specified for the view.

When using SQL/XML functions to generate XML schema-based content, you must specify the appropriate namespace information for all the elements and also indicate the location of the schema using the xsi:schemaLocation attribute. These can be specified using the XMLAttributes clause.

Example 18-4 Creating an XMLType View Using SQL/XML Functions

CREATE OR REPLACE VIEW emp_simple_xml OF XMLType 
  XMLSCHEMA "http://www.oracle.com/emp_simple.xsd" ELEMENT "Employee"
   WITH OBJECT ID (extract(OBJECT_VALUE,   
                           '/Employee/EmployeeId/text()').getnumberval()) AS 
   SELECT 
     XMLElement("Employee",
                XMLAttributes(
                  'http://www.oracle.com/emp_simple.xsd' AS "xmlns",
                  'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi",
                  'http://www.oracle.com/emp_simple.xsd
                   http://www.oracle.com/emp_simple.xsd'
                  AS "xsi:schemaLocation"),
                XMLForest(e.employee_id     AS "EmployeeId",
                          e.last_name       AS "Name",
                          e.job_id          AS "Job",
                          e.manager_id      AS "Manager",
                          e.hire_date       AS "HireDate",
                          e.salary          AS "Salary",
                          e.commission_pct  AS "Commission",
                          XMLForest(
                            d.department_id     AS "DeptNo",
                            d.department_name  AS "DeptName", 
                            d.location_id      AS "Location") AS "Dept"))
     FROM employees e, departments d 
     WHERE e.department_id = d.department_id;

In Example 18-4, XMLElement creates the Employee XML element and the inner XMLForest function call creates the children of the Employee element. The XMLAttributes clause inside XMLElement constructs the required XML namespace and schema location attributes, so that the XML data that is generated conforms to the XML schema of the view. The innermost XMLForest function call creates the department XML element that is nested inside the Employee element.

The XML generation functions generate a non-schema-based XML instance, by default. However, when the schema location is specified, using attribute xsi:schemaLocation or xsi:noNamespaceSchemaLocation, Oracle XML DB generates XML schema-based XML. In the case of XMLType views, as long as the names of the elements and attributes match those in the XML schema, the XML is converted implicitly into a valid XML schema-based document. Any errors in the generated XML data are caught when further operations, such as validate or extract operations, are performed on the XML instance.

Example 18-5 Querying an XMLType View

This example queries the XMLType view, returning an XML result from the employees and departments tables. The result of the query is shown here pretty-printed here, for clarity.

SELECT OBJECT_VALUE AS RESULT FROM emp_simple_xml WHERE ROWNUM < 2;

RESULT
---------------------------------------------------------------------
<Employee xmlns="http://www.oracle.com/emp_simple.xsd"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="http://www.oracle.com/emp_simple.xsd 
                              http://www.oracle.com/emp_simple.xsd">
  <EmployeeId>200</EmployeeId>
  <Name>Whalen</Name>
  <Job>AD_ASST</Job>
  <Manager>101</Manager>
  <HireDate>1987-09-17</HireDate>
  <Salary>4400</Salary>
  <Dept>
    <DeptNo>10</Deptno>
    <DeptName>Administration</DeptName>
    <Location>1700</Location>
  </Dept>
</Employee>

Using Namespaces With SQL/XML Functions

If you have complex XML schemas involving namespaces, you must use the partially escaped mapping provided in the SQL/XML functions and create elements with appropriate namespaces and prefixes.

Example 18-6 Using Namespace Prefixes in XMLType Views

SELECT  XMLElement("ipo:Employee", 
          XMLAttributes('http://www.oracle.com/emp_simple.xsd' AS "xmlns:ipo", 
                        'http://www.oracle.com/emp_simple.xsd 
                         http://www.oracle.com/emp_simple.xsd' AS "xmlns:xsi"),
            XMLForest(e.employee_id AS "ipo:EmployeeId", 
                      e.last_name   AS "ipo:Name",  
                      e.job_id      AS "ipo:Job",
                      e.manager_id  AS "ipo:Manager",
                      TO_CHAR(e.hire_date,'YYYY-MM-DD') AS "ipo:HireDate", 
                      e.salary  AS "ipo:Salary",
                      e.commission_pct AS "ipo:Commission",
                 XMLForest(d.department_id   AS "ipo:DeptNo",
                           d.department_name AS "ipo:DeptName", d.location_id
       AS "ipo:Location") AS "ipo:Dept"))
       FROM employees e, departments d 
       WHERE e.department_id = d.department_id
             AND d.department_id = 20;
BEGIN
  -- Delete schema if it already exists (else error)
  DBMS_XMLSCHEMA.deleteSchema('emp-noname.xsd', 4); 
END;

This SQL query creates the XML instances with the correct namespace, prefixes, and target schema location, and can be used as the query in the emp_simple_xml view definition. The instance created by this query looks like the following:

result
----------
<ipo:Employee
xmlns:ipo="http://www.oracle.com/emp_simple.xsd"
 xmlns:xsi="http://www.oracle.com/emp_simple.xsd
 http://www.oracle.com/emp_simple.xsd">
<ipo:EmployeeId>201</ipo:EmployeeId><ipo:Name>Hartstein</ipo:Name>
<ipo:Job>MK_MAN</ipo:Job><ipo:Manager>100</ipo:Manager>
<ipo:HireDate>1996-02-17</ipo:HireDate><ipo:Salary>13000</ipo:Salary>
<ipo:Dept><ipo:DeptNo>20</ipo:DeptNo><ipo:DeptName>Marketing</ipo:DeptName>
<ipo:Location>1800</ipo:Location></ipo:Dept></ipo:Employee>
<ipo:Employee xmlns:ipo="http://www.oracle.com/emp_simple.xsd"
 xmlns:xsi="http://www.oracle.com/emp_simple.xsd 
 http://www.oracle.com/emp_simple.xsd"><ipo:EmployeeId>202</ipo:EmployeeId>
<ipo:Name>Fay</ipo:Name><ipo:Job>MK_REP</ipo:Job><ipo:Manager>201</ipo:Manager>
<ipo:HireDate>1997-08-17</ipo:HireDate><ipo:Salary>6000</ipo:Salary>
<ipo:Dept><ipo:DeptNo>20</ipo:Dept
No><ipo:DeptName>Marketing</ipo:DeptName><ipo:Location>1800</ipo:Location>
</ipo:Dept>
</ipo:Employee>

If the XML schema had no target namespace, then you could use the xsi:noNamespaceSchemaLocation attribute to denote that. For example, consider the following XML schema that is registered at location: "emp-noname.xsd":

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    'emp-noname.xsd',
    '<schema xmlns="http://www.w3.org/2001/XMLSchema"
             xmlns:xdb="http://xmlns.oracle.com/xdb"> 
       <element name = "Employee"> 
         <complexType> 
           <sequence> 
             <element name = "EmployeeId" type = "positiveInteger"/> 
             <element name = "Name" type = "string"/> 
             <element name = "Job" type = "string"/> 
             <element name = "Manager" type = "positiveInteger"/> 
             <element name = "HireDate" type = "date"/> 
             <element name = "Salary" type = "positiveInteger"/> 
             <element name = "Commission" type = "positiveInteger"/> 
             <element name = "Dept"> 
               <complexType> 
                 <sequence> 
                   <element name = "DeptNo" type = "positiveInteger" /> 
                   <element name = "DeptName" type = "string"/> 
                   <element name = "Location" type = "positiveInteger"/> 
                 </sequence> 
               </complexType> 
             </element> 
           </sequence> 
         </complexType> 
       </element> 
     </schema>',
    TRUE,
    TRUE,
    FALSE);
END;

The following statement creates a view that conforms to this XML schema:

CREATE OR REPLACE VIEW emp_xml OF XMLType 
     XMLSCHEMA "emp-noname.xsd" ELEMENT "Employee"  
     WITH OBJECT ID (extract(OBJECT_VALUE,   
                             '/Employee/EmployeeId/text()').getnumberval()) AS 
     SELECT XMLElement(
       "Employee",
       XMLAttributes('http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi",
                     'emp-noname.xsd' AS "xsi:noNamespaceSchemaLocation"),   
       XMLForest(e.employee_id    AS "EmployeeId", 
                 e.last_name      AS "Name",  
                 e.job_id         AS "Job",
                 e.manager_id     AS "Manager",
                 e.hire_date      AS "HireDate", 
                 e.salary         AS "Salary",
                 e.commission_pct AS "Commission",
                 XMLForest(d.department_id     AS "DeptNo",
                            d.department_name  AS "DeptName", 
                            d.location_id      AS "Location") AS "Dept"))
       FROM employees e, departments d 
       WHERE e.department_id = d.department_id;

The XMLAttributes clause creates an XML element that contains the noNamespace schema location attribute.

Example 18-7 Using SQL/XML Generation Functions in Schema-Based XMLType Views

BEGIN
  -- Delete schema if it already exists (else error)
  DBMS_XMLSCHEMA.deleteSchema('http://www.oracle.com/dept.xsd', 4);
END;
/
BEGIN
DBMS_XMLSCHEMA.registerSchema(
  'http://www.oracle.com/dept.xsd',
  '<schema xmlns="http://www.w3.org/2001/XMLSchema"
           targetNamespace="http://www.oracle.com/dept.xsd" version="1.0" 
           xmlns:xdb="http://xmlns.oracle.com/xdb"
           elementFormDefault="qualified"> 
     <element name = "Department"> 
       <complexType> 
         <sequence> 
           <element name = "DeptNo" type = "positiveInteger"/> 
           <element name = "DeptName" type = "string"/> 
           <element name = "Location" type = "positiveInteger"/> 
           <element name = "Employee" maxOccurs = "unbounded"> 
             <complexType> 
               <sequence> 
                 <element name = "EmployeeId" type = "positiveInteger"/> 
                 <element name = "Name" type = "string"/> 
                 <element name = "Job" type = "string"/> 
                 <element name = "Manager" type = "positiveInteger"/> 
                 <element name = "HireDate" type = "date"/> 
                 <element name = "Salary" type = "positiveInteger"/> 
                 <element name = "Commission" type = "positiveInteger"/> 
               </sequence> 
             </complexType> 
           </element> 
         </sequence> 
       </complexType> 
     </element> 
   </schema>',
  TRUE,
  FALSE,
  FALSE);
END;
/
CREATE OR REPLACE VIEW dept_xml OF XMLType
  XMLSCHEMA "http://www.oracle.com/dept.xsd" ELEMENT "Department"
  WITH OBJECT ID (extract(OBJECT_VALUE, '/Department/DeptNo').getNumberVal()) AS
         SELECT  XMLElement(
           "Department",
           XMLAttributes(
             'http://www.oracle.com/emp.xsd' AS "xmlns" ,
             'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi",
             'http://www.oracle.com/dept.xsd
              http://www.oracle.com/dept.xsd' AS "xsi:schemaLocation"),
           XMLForest(d.department_id "DeptNo",
                     d.department_name "DeptName",
                     d.location_id "Location"),
           (SELECT Xmlagg(XMLElement("Employee",
                               XMLForest(e.employee_id  "EmployeeId",
                                    e.last_name "Name",
                                    e.job_id "Job",
                                    e.manager_id "Manager",
               to_char(e.hire_date,'YYYY-MM-DD') "Hiredate",
                                    e.salary "Salary",
                                    e.commission_pct "Commission")))
                 FROM employees e
                 WHERE e.department_id = d.department_id))
     FROM departments d;

SELECT OBJECT_VALUE AS result FROM dept_xml WHERE ROWNUM < 2;

RESULT
----------------------------------------------------------------
<Department
    xmlns="http://www.oracle.com/emp.xsd"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.oracle.com/dept.xsd 
                        http://www.oracle.com/dept.xsd">
  <DeptNo>10</DeptNo>
  <DeptName>Administration</DeptName>
  <Location>1700</Location>
  <Employee>
    <EmployeeId>200</EmployeeId>
    <Name>Whalen</Name>
    <Job>AD_ASST</Job>
    <Manager>101</Manager>
    <Hiredate>1987-09-17</Hiredate>
    <Salary>4400</Salary>
  </Employee>
</Department>

Using Object Types and Views to Create XML Schema-Based XMLType Views

To wrap relational or object-relational data with strongly-typed XML using the object view approach, perform the following steps:

Create object types.
Create (or generate) and then register an XML schema document that contains the XML structures, along with its mapping to the SQL object types and attributes. The XML schema can be generated from the existing object types and must be annotated to contain the bidirectional mapping from XML to the object types.

You can fill in the optional Oracle XML DB attributes before registering the XML schema. In this case, Oracle validates the extra information to ensure that the specified values for the Oracle XML DB attributes are compatible with the rest of the XML schema declarations. This form of XML schema registration typically happens when wrapping existing data using XMLType views.

See:
Chapter 5, "XML Schema Storage and Query: Basic" for more details on this process

You can use PL/SQL functions DBMS_XMLSchema .generateSchema and generateSchemas to generate the default XML mapping for specified object types. The generated XML schema document has the SQLType, SQLSchema, and so on, attributes filled in. When these XML schema documents are then registered, the following validation forms can occur:
- SQLType for attributes or elements based on simpleType. This is compatible with the corresponding XMLType. For example, an XML string datatype can only be mapped to VARCHAR2 or a Large Object (LOB) datatype.
- SQLType specified for elements based on complexType. This is either a LOB or an object type whose structure is compatible with the declaration of the complexType, that is, the object type has the right number of attributes with the right datatypes.
Create the XMLType view and specify the XML schema URL and the root element name. The underlying view query first constructs the object instances and then converts them to XML. This step can also be done in two parts:
1. Create an object view.
2. Create an XMLType view over the object view.

For examples, see the following sections, which are based on the employee and department relational tables and XML views of this data:

"Creating Schema-Based XMLType Views Over Object Views"
"Wrapping Relational Department Data with Nested Employee Data as XML"

Creating Schema-Based XMLType Views Over Object Views

For the first example view, to wrap the relational employee data with nested department information as XML, follow Step 1 through Step 4b.

Step 1. Create Object Types

Example 18-8 creates the object types for the views.

Example 18-8 Creating Object Types for Schema-Based XMLType Views

CREATE TYPE dept_t AS OBJECT 
      (deptno        NUMBER(4), 
       dname         VARCHAR2(30), 
       loc           NUMBER(4)); 
/ 

CREATE TYPE emp_t AS OBJECT 
      (empno         NUMBER(6), 
       ename         VARCHAR2(25), 
       job           VARCHAR2(10), 
       mgr           NUMBER(6), 
       hiredate      DATE, 
       sal           NUMBER(8,2), 
       comm          NUMBER(2,2), 
       dept          dept_t ); 
/

Step 2. Create or Generate XMLSchema, emp.xsd

You can create an XML schema manually or use package DBMS_XMLSCHEMA to generate it automatically from the existing object types, as shown in Example 18-9.

Example 18-9 Generating an XML Schema with DBMS_XMLSCHEMA.GENERATESCHEMA

SELECT DBMS_XMLSCHEMA.generateSchema('HR','EMP_T') AS result FROM DUAL;

This generates the XML schema for the employee type. You can supply various arguments to this function to add namespaces, and so on. You can also edit the XML schema to change the various default mappings that were generated. Function DBMS_XMLSCHEMA.generateSchemas generates a list of XML schemas, one for each SQL database schema referenced by the object type and its attributes, embedded at any level.

Step 3. Register XML Schema, emp_complex.xsd

XML schema, emp_complex.xsd also specifies how the XML elements and attributes are mapped to their corresponding attributes in the object types. Example 18-10 shows how to register XML schema emp_complex.xsd. See also the xdb:SQLType annotation Example 18-10.

Example 18-10 Registering XML Schema emp_complex.xsd

BEGIN
  -- Delete schema if it already exists (else error)
  DBMS_XMLSCHEMA.deleteSchema('http://www.oracle.com/emp_complex.xsd', 4);
END;
/

COMMIT;
 
BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    'http://www.oracle.com/emp_complex.xsd', 
    '<?xml version="1.0"?>
    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                xmlns:xdb="http://xmlns.oracle.com/xdb" 
                xsi:schemaLocation="http://xmlns.oracle.com/xdb 
                                    http://xmlns.oracle.com/xdb/XDBSchema.xsd">
      <xsd:element name="Employee" type="EMP_TType" xdb:SQLType="EMP_T" 
                   xdb:SQLSchema="HR"/>
      <xsd:complexType name="EMP_TType" xdb:SQLType="EMP_T" xdb:SQLSchema="HR" 
                       xdb:maintainDOM="false">
        <xsd:sequence>
          <xsd:element name="EMPNO" type="xsd:double" xdb:SQLName="EMPNO" 
                       xdb:SQLType="NUMBER"/>
          <xsd:element name="ENAME" xdb:SQLName="ENAME" xdb:SQLType="VARCHAR2">
            <xsd:simpleType>
              <xsd:restriction base="xsd:string">
                <xsd:maxLength value="25"/>
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
          <xsd:element name="JOB" xdb:SQLName="JOB" xdb:SQLType="VARCHAR2">
            <xsd:simpleType>
              <xsd:restriction base="xsd:string">
                <xsd:maxLength value="10"/>
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
          <xsd:element name="MGR" type="xsd:double" xdb:SQLName="MGR" 
                       xdb:SQLType="NUMBER"/>
          <xsd:element name="HIREDATE" type="xsd:date" xdb:SQLName="HIREDATE" 
                       xdb:SQLType="DATE"/>
          <xsd:element name="SAL" type="xsd:double" xdb:SQLName="SAL" 
                       xdb:SQLType="NUMBER"/>
          <xsd:element name="COMM" type="xsd:double" xdb:SQLName="COMM" 
                       xdb:SQLType="NUMBER"/>
          <xsd:element name="DEPT" type="DEPT_TType" xdb:SQLName="DEPT" 
                       xdb:SQLSchema="HR" xdb:SQLType="DEPT_T"/>
        </xsd:sequence>
      </xsd:complexType>
      <xsd:complexType name="DEPT_TType" xdb:SQLType="DEPT_T" xdb:SQLSchema="HR"
                       xdb:maintainDOM="false">
        <xsd:sequence>
          <xsd:element name="DEPTNO" type="xsd:double" xdb:SQLName="DEPTNO" 
                       xdb:SQLType="NUMBER"/>
          <xsd:element name="DNAME" xdb:SQLName="DNAME" xdb:SQLType="VARCHAR2">
            <xsd:simpleType>
              <xsd:restriction base="xsd:string">
                <xsd:maxLength value="30"/>
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
         <xsd:element name="LOC" type="xsd:double" xdb:SQLName="LOC" 
                       xdb:SQLType="NUMBER"/>
        </xsd:sequence>
      </xsd:complexType>
    </xsd:schema>', 
   TRUE, 
   FALSE, 
   FALSE);
END;
/

The preceding statement registers the XML schema with the target location:

"http://www.oracle.com/emp_complex.xsd"

Step 4a. Using the One-Step Process

With the one-step process, you must create an XMLType view on the relational tables as shown in Example 18-11.

Example 18-11 Creating an XMLType View

CREATE OR REPLACE VIEW emp_xml OF XMLType 
  XMLSCHEMA "http://www.oracle.com/emp_complex.xsd"
  ELEMENT "Employee" 
    WITH OBJECT ID (extractValue(OBJECT_VALUE, '/Employee/EMPNO')) AS 
  SELECT emp_t(e.employee_id, e.last_name, e.job_id, e.manager_id, e.hire_date, 
               e.salary, e.commission_pct,
               dept_t(d.department_id, d.department_name, d.location_id)) 
    FROM employees e, departments d 
    WHERE e.department_id = d.department_id;

This example uses SQL function extractValue in the OBJECT ID clause because extractValue can automatically calculate the appropriate SQL datatype mapping — in this case a SQL NUMBER — using the XML schema information. It is recommended that you use SQL function extractValue rather than XMLType method extractValue().

Step 4b. Using the Two-Step Process by First Creating an Object View

In the two-step process, you first create an object view, then create an XMLType view on the object view, as shown in Example 18-12.

Example 18-12 Creating an Object View and an XMLType View on the Object View

CREATE OR REPLACE VIEW emp_v OF emp_t WITH OBJECT ID (empno) AS 
  SELECT emp_t(e.employee_id, e.last_name, e.job_id, e.manager_id, e.hire_date,
               e.salary, e.commission_pct,
               dept_t(d.department_id, d.department_name, d.location_id)) 
    FROM employees e, departments d 
    WHERE e.department_id = d.department_id;
 
CREATE OR REPLACE VIEW emp_xml OF XMLType 
  XMLSCHEMA "http://www.oracle.com/emp_complex.xsd" ELEMENT "Employee"
  WITH OBJECT ID DEFAULT
  AS SELECT VALUE(p) FROM emp_v p;

Wrapping Relational Department Data with Nested Employee Data as XML

For the second example view, to wrap the relational department data with nested employee information as XML, follow Step 1 through Step 3b.

Step 1. Create Object Types

The first step is to create the object types needed, as shown in Example 18-13.

Example 18-13 Creating Object Types

CREATE TYPE emp_t AS OBJECT (empno         NUMBER(6),
                             ename         VARCHAR2(25), 
                             job           VARCHAR2(10), 
                             mgr           NUMBER(6), 
                             hiredate      DATE, 
                             sal           NUMBER(8,2), 
                             comm          NUMBER(2,2)); /
 
CREATE OR REPLACE TYPE emplist_t AS TABLE OF emp_t; 
/ 
 
CREATE TYPE dept_t AS OBJECT (deptno        NUMBER(4),
                              dname         VARCHAR2(30), 
                              loc           NUMBER(4),
                              emps          emplist_t); 
/

Step 2. Register XML Schema, dept_complex.xsd

You can either use a pre-existing XML schema or generate an XML schema from the object type with function DBMS_XMLSCHEMA.generateSchema or DBMS_XMLSCHEMA.generateSchemas. Example 18-14 shows how to register the XML schema dept_complex.xsd.

Example 18-14 Registering XML Schema dept_complex.xsd

BEGIN
  -- Delete schema if it already exists (else error)
  DBMS_XMLSCHEMA.deleteSchema('http://www.oracle.com/dept_complex.xsd', 4);
END;
/

BEGIN
 DBMS_XMLSCHEMA.registerSchema('http://www.oracle.com/dept_complex.xsd',
   '<?xml version="1.0"?>
   <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
               xmlns:xdb="http://xmlns.oracle.com/xdb"            
               xsi:schemaLocation="http://xmlns.oracle.com/xdb 
                                   http://xmlns.oracle.com/xdb/XDBSchema.xsd">
     <xsd:element name="Department" type="DEPT_TType" xdb:SQLType="DEPT_T"  
                  xdb:SQLSchema="HR"/>
     <xsd:complexType name="DEPT_TType" xdb:SQLType="DEPT_T" xdb:SQLSchema="HR" 
                      xdb:maintainDOM="false">
       <xsd:sequence>
         <xsd:element name="DEPTNO" type="xsd:double" xdb:SQLName="DEPTNO" 
                      xdb:SQLType="NUMBER"/>
         <xsd:element name="DNAME" xdb:SQLName="DNAME" xdb:SQLType="VARCHAR2">
           <xsd:simpleType>
             <xsd:restriction base="xsd:string">
               <xsd:maxLength value="30"/>
             </xsd:restriction>
           </xsd:simpleType>
         </xsd:element>
         <xsd:element name="LOC" type="xsd:double" xdb:SQLName="LOC" 
                      xdb:SQLType="NUMBER"/>
         <xsd:element name="EMPS" type="EMP_TType" maxOccurs="unbounded" 
                      minOccurs="0" xdb:SQLName="EMPS" 
                      xdb:SQLCollType="EMPLIST_T" xdb:SQLType="EMP_T"  
                      xdb:SQLSchema="HR" xdb:SQLCollSchema="HR"/>
       </xsd:sequence>
     </xsd:complexType>
     <xsd:complexType name="EMP_TType" xdb:SQLType="EMP_T" xdb:SQLSchema="HR"
                      xdb:maintainDOM="false">
       <xsd:sequence>
         <xsd:element name="EMPNO" type="xsd:double" xdb:SQLName="EMPNO" 
                      xdb:SQLType="NUMBER"/>
         <xsd:element name="ENAME" xdb:SQLName="ENAME" xdb:SQLType="VARCHAR2">
           <xsd:simpleType>
             <xsd:restriction base="xsd:string">
               <xsd:maxLength value="25"/>
             </xsd:restriction>
           </xsd:simpleType>
         </xsd:element>
         <xsd:element name="JOB" xdb:SQLName="JOB" xdb:SQLType="VARCHAR2">
           <xsd:simpleType>
             <xsd:restriction base="xsd:string">
               <xsd:maxLength value="10"/>
             </xsd:restriction>
           </xsd:simpleType>
         </xsd:element>
         <xsd:element name="MGR" type="xsd:double" xdb:SQLName="MGR" 
                      xdb:SQLType="NUMBER"/>
         <xsd:element name="HIREDATE" type="xsd:date" xdb:SQLName="HIREDATE" 
                      xdb:SQLType="DATE"/>
         <xsd:element name="SAL" type="xsd:double" xdb:SQLName="SAL" 
                      xdb:SQLType="NUMBER"/>
         <xsd:element name="COMM" type="xsd:double" xdb:SQLName="COMM"   
                      xdb:SQLType="NUMBER"/>
       </xsd:sequence>
     </xsd:complexType>
   </xsd:schema>', 
  TRUE, 
  FALSE, 
  FALSE);
END;
/

Step 3a. Create XMLType Views on Relational Tables

The next step is to create the dept_xml XMLType view from the department object type, as shown in Example 18-15.

Example 18-15 Creating XMLType Views on Relational Tables

CREATE OR REPLACE VIEW dept_xml OF XMLType
  XMLSChema "http://www.oracle.com/dept_complex.xsd" ELEMENT "Department"
  WITH OBJECT ID (extractValue(OBJECT_VALUE, '/Department/DEPTNO')) AS
  SELECT dept_t(d.department_id, d.department_name, d.location_id,
                CAST(MULTISET(SELECT emp_t(e.employee_id, e.last_name, e.job_id,
                                           e.manager_id, e.hire_date,
                                           e.salary, e.commission_pct) 
                                FROM employees e
                                WHERE e.department_id = d.department_id) 
                       AS emplist_t))
    FROM departments d;

Step 3b. Create XMLType Views Using SQL/XML Functions

You can also create the dept_xml XMLType view from the relational tables without using the object type definitions, that is, using SQL/XML generation functions. Example 18-16 demonstrates this.

Example 18-16 Creating XMLType Views Using SQL/XML Functions

CREATE OR REPLACE VIEW dept_xml OF XMLType
  XMLSCHEMA "http://www.oracle.com/dept_complex.xsd" ELEMENT "Department"
  WITH OBJECT ID (extract(OBJECT_VALUE, '/Department/DEPTNO').getNumberVal()) AS
  SELECT  
    XMLElement(
      "Department",
      XMLAttributes('http://www.oracle.com/dept_complex.xsd' AS "xmlns",
                    'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi",
                    'http://www.oracle.com/dept_complex.xsd 
                     http://www.oracle.com/dept_complex.xsd' 
                      AS "xsi:schemaLocation"),
      XMLForest(d.department_id "DeptNo", d.department_name "DeptName",
                d.location_id "Location"),
      (SELECT XMLAgg(XMLElement("Employee",
                                XMLForest(e.employee_id "EmployeeId", 
                                          e.last_name "Name", 
                                          e.job_id "Job", 
                                          e.manager_id "Manager", 
                                          e.hire_date "Hiredate",
                                          e.salary "Salary",
                                          e.commission_pct "Commission")))
                      FROM employees e WHERE e.department_id = d.department_id))
    FROM departments d;

Note:

The XML schema and element information must be specified at the view level because the SELECT list could arbitrarily construct XML of a different XML schema from the underlying table.

Creating XMLType Views From XMLType Tables

An XMLType view can be created on an XMLType table, for example, to transform the XML or to restrict the rows returned by using some predicates.

Example 18-17 Creating an XMLType View by Restricting Rows From an XMLType Table

This is an example of creating an XMLType view by restricting the rows returned from an underlying XMLType table. This example uses the dept_complex.xsd XML schema, described in section "Wrapping Relational Department Data with Nested Employee Data as XML", to create the underlying table.

CREATE TABLE dept_xml_tab OF XMLType 
    XMLSchema "http://www.oracle.com/dept_complex.xsd" ELEMENT "Department"
    NESTED TABLE xmldata."EMPS" STORE AS dept_xml_tab_tab1;
 
CREATE OR REPLACE VIEW dallas_dept_view OF XMLType 
    XMLSchema "http://www.oracle.com/dept.xsd" ELEMENT "Department"
        AS SELECT OBJECT_VALUE FROM dept_xml_tab 
           WHERE extractValue(OBJECT_VALUE, '/Department/Location') = 'DALLAS';

Here, dallas_dept_view restricts the XMLType table rows to those departments whose location is Dallas.

Example 18-18 Creating an XMLType View by Transforming an XMLType Table

You can create an XMLType view by transforming the XML data using a style sheet. For example, consider the creation of XMLType table po_tab. Refer to Example 9-2, "Using XMLTRANSFORM and DBURITYPE to Retrieve a Style Sheet" for an example that uses XMLtransform:

DROP TABLE po_tab;

CREATE TABLE po_tab OF XMLType
XMLSCHEMA "ipo.xsd" ELEMENT "PurchaseOrder";

You can then create a view of the table as follows:

CREATE OR REPLACE VIEW hr_po_tab OF XMLType XMLSCHEMA "hrpo.xsd" 
  ELEMENT "PurchaseOrder" 
  WITH OBJECT ID DEFAULT
  AS SELECT XMLtransform(OBJECT_VALUE,  
                         XDBURIType('/home/SCOTT/xsl/po2.xsl').getxml())
       FROM po_tab;

Referencing XMLType View Objects Using REF()

You can reference an XMLType view object using the REF() syntax:

SELECT REF(p) FROM dept_xml_tab p;

XMLType view reference REF() is based on one of the following object IDs:

System-generated OID — for views on XMLType tables or object views
Primary key based OID -- for views with OBJECT ID expressions

These REFs can be used to fetch OCIXMLType instances in the OCI Object cache or can be used inside SQL queries. These REFs act in the same way as REFs to object views.

DML (Data Manipulation Language) on XMLType Views

An XMLType view may not be inherently updatable. This means that you have to write INSTEAD-OF TRIGGERS to handle all data manipulation (DML). You can identify cases where the view is implicitly updatable, by analyzing the underlying view query.

Example 18-19 Identifying When a View is Implicitly Updatable

One way to identify when an XMLType view is implicitly updatable is to use an XMLType view query to determine if the view is based on an object view or an object constructor that is itself inherently updatable, as follows:

CREATE TYPE dept_t AS OBJECT 
      (deptno NUMBER(4), 
       dname VARCHAR2(30), 
       loc NUMBER(4)); 
/ 
 
BEGIN
  -- Delete schema if it already exists (else error)
  DBMS_XMLSCHEMA.deleteSchema('http://www.oracle.com/dept.xsd', 4);
END;
/

COMMIT;
 
BEGIN
  DBMS_XMLSCHEMA.registerSchema('http://www.oracle.com/dept_t.xsd',
    '<?xml version="1.0"?>
     <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                 xmlns:xdb="http://xmlns.oracle.com/xdb" 
                 xsi:schemaLocation="http://xmlns.oracle.com/xdb 
                                     http://xmlns.oracle.com/xdb/XDBSchema.xsd">
     <xsd:element name="Department" type="DEPT_TType" xdb:SQLType="DEPT_T" 
                    xdb:SQLSchema="HR"/>
     <xsd:complexType name="DEPT_TType" xdb:SQLType="DEPT_T" xdb:SQLSchema="HR" 
                        xdb:maintainDOM="false">
         <xsd:sequence>
           <xsd:element name="DEPTNO" type="xsd:double" xdb:SQLName="DEPTNO" 
                        xdb:SQLType="NUMBER"/>
           <xsd:element name="DNAME" xdb:SQLName="DNAME" xdb:SQLType="VARCHAR2">
             <xsd:simpleType>
               <xsd:restriction base="xsd:string">
                 <xsd:maxLength value="30"/>
               </xsd:restriction>
             </xsd:simpleType>
           </xsd:element>
           <xsd:element name="LOC" type="xsd:double" xdb:SQLName="LOC" 
                xdb:SQLType="NUMBER"/>
         </xsd:sequence>
       </xsd:complexType>
     </xsd:schema>', 
    TRUE, 
    FALSE, 
    FALSE);
END;
/

CREATE OR REPLACE VIEW dept_xml of XMLType
  XMLSchema "http://www.oracle.com/dept_t.xsd" element "Department" 
  WITH OBJECT ID (OBJECT_VALUE.extract('/Department/DEPTNO').getnumberval()) AS
  SELECT dept_t(d.department_id, d.department_name, d.location_id) 
    FROM departments d; 
 
INSERT INTO dept_xml 
  VALUES (
    XMLType.createXML(
      '<Department 
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
         xsi:noNamespaceSchemaLocation="http://www.oracle.com/dept_t.xsd" >
         <DEPTNO>300</DEPTNO>
         <DNAME>Processing</DNAME>
         <LOC>1700</LOC>
       </Department>'));
 
UPDATE dept_xml d 
 SET d.OBJECT_VALUE = updateXML(d.OBJECT_VALUE, '/Department/DNAME/text()',  
                                'Shipping')
   WHERE existsNode(d.OBJECT_VALUE, '/Department[DEPTNO=300]') = 1;

XPath Rewrite on XMLType Views

XPath rewrites for XMLType views constructed using XMLType tables or object types, object views, and SQL function sys_XMLGen are the same as for regular XMLType table columns. Hence, SQL functions extract, existsNode, and extractValue on view columns get rewritten into underlying relational or object-relational accesses for better performance.

XPath rewrites for XMLType views constructed using the SQL/XML generation functions are also supported. Functions extract, existsNode, and extractValue on view columns get rewritten into underlying relational accesses for better performance.

Views Constructed With SQL/XML Generation Functions

This section describes XML schema-based and non-schema-based XPath rewrites on XMLType views constructed with SQL/XML functions.

XPath Rewrite on Non-Schema-Based Views Constructed With SQL/XML

Example 18-20 illustrates XPath rewrites on non-schema-based XMLType views.

Example 18-20 Non-Schema-Based Views Constructed Using SQL/XML

CREATE OR REPLACE VIEW emp_view OF XMLType
  WITH OBJECT ID (extract(OBJECT_VALUE, '/Emp/@empno').getnumberval())
  AS SELECT XMLElement("Emp", XMLAttributes(employee_id),
                       XMLForest(e.first_name ||' '|| e.last_name AS "name",
                                 e.hire_date AS "hiredate")) AS "result"
       FROM employees e
       WHERE salary > 15000;

Querying with SQL function extractValue to select from emp_view:
```
SELECT extractValue(OBJECT_VALUE, '/Emp/name'), 
       extractValue(OBJECT_VALUE, '/Emp/hiredate'
  FROM emp_view;
```
This query becomes something like the following:
```
SELECT e.first_name ||' '|| e.last_name, e.hire_date FROM employees e
   WHERE e.salary > 15000;
```
The rewritten query is a simple relational query. The extractValue expression is rewritten down to the relational column access as defined in view emp_view.
Querying with SQL function extractValue followed by method getNumberVal() to select from emp_view:
```
SELECT (extract(OBJECT_VALUE, '/Emp/@empno').getnumberval()) FROM emp_view;
```
This query becomes something like the following:
```
SELECT e.employee_id FROM employees e WHERE e.salary > 15000;  
```
The rewritten query is a simple relational query. The extract expression followed by getNumberVal() is rewritten down to the relational column access as defined in view emp_view.

Querying with SQL function existsNode to select from view emp_view:

SELECT extractValue(OBJECT_VALUE, '/Emp/name'),
       extractValue(OBJECT_VALUE, '/Emp/hiredate')
  FROM emp_view WHERE existsNode(OBJECT_VALUE, '/Emp[@empno=101]') = 1;

This query becomes something like the following:

SELECT e.first_name ||' '|| e.last_name, e.hire_date 
  FROM employees e
  WHERE e.employee_id = 101 AND e.salary > 15000;

The rewritten query is a simple relational query. The XPath predicate in the existsNode expression is rewritten down to the predicate over relational columns as defined in view emp_view.

If there is an index created on column employees.empno, then the query optimizer can use the index to speed up the query.

Querying with existsNode to select from view emp_view:

SELECT extractValue(OBJECT_VALUE, '/Emp/name'),
       extractValue(OBJECT_VALUE, '/Emp/hiredate'),
       extractValue(OBJECT_VALUE, '/Emp/@empno')
  FROM emp_view
  WHERE existsNode(OBJECT_VALUE, '/Emp[name="Steven King" or @empno = 101] ')
        = 1;

This query becomes something like the following:

SELECT e.first_name ||' '|| e.last_name, e.hire_date, e.employee_id
  FROM employees e
  WHERE (e.first_name ||' '|| e.last_name = 'Steven King' 
         OR e.employee_id = 101)
    AND e.salary > 15000;

The rewritten query is a simple relational query. The XPath predicate in the existsNode expression is rewritten down to the predicate over relational columns as defined in view emp_view.

Querying with extract to select from view emp_view:

SELECT extract(OBJECT_VALUE, '/Emp/name'),
       extract(OBJECT_VALUE, '/Emp/hiredate')
  FROM emp_view;

This query becomes something like the following:

SELECT CASE WHEN e.first_name ||' '|| e.last_name IS NOT NULL THEN 
       XMLElement("name",e.first_name ||' '|| e.last_name) ELSE NULL END,
       CASE WHEN e.hire_date IS NOT NULL 
            THEN XMLElement("hiredate", e.hire_date) 
       ELSE NULL END 
  FROM employees e WHERE e.salary > 15000;

The rewritten query is a simple relational query. The extract expression is rewritten to expressions over relational columns.

Note:

Since the view uses SQL function XMLForest to formulate name and hiredate elements, the rewritten query uses equivalent CASE expression to be consistent with XMLForest semantics.

XPath Rewrite on Schema-Based Views Constructed With SQL/XML

Example 18-21 illustrates an XPath rewrite on XML-schema-based XMLType view constructed with a SQL/XML function.

Example 18-21 XML-Schema-Based Views Constructed With SQL/XML

BEGIN
  -- Delete schema if it already exists (else error)
  DBMS_XMLSCHEMA.deleteSchema('http://www.oracle.com/emp_simple.xsd', 4);
END;
/
 
BEGIN
 DBMS_XMLSCHEMA.registerSchema(
   'http://www.oracle.com/emp_simple.xsd',
   '<schema 
        xmlns="http://www.w3.org/2001/XMLSchema"
        targetNamespace="http://www.oracle.com/emp_simple.xsd" version="1.0"
        xmlns:xdb="http://xmlns.oracle.com/xdb" 
        elementFormDefault="qualified"> 
      <element name = "Employee"> 
        <complexType> 
          <sequence> 
            <element name = "EmployeeId" type = "positiveInteger"/> 
            <element name = "Name" type = "string"/> 
            <element name = "Job" type = "string"/> 
            <element name = "Manager" type = "positiveInteger"/> 
            <element name = "HireDate" type = "date"/> 
            <element name = "Salary" type = "positiveInteger"/> 
            <element name = "Commission" type = "positiveInteger"/> 
            <element name = "Dept"> 
              <complexType> 
                <sequence> 
                  <element name = "DeptNo" type = "positiveInteger" /> 
                  <element name = "DeptName" type = "string"/> 
                  <element name = "Location" type = "positiveInteger"/> 
                </sequence> 
              </complexType> 
            </element> 
          </sequence> 
        </complexType> 
      </element> 
    </schema>', 
   TRUE, 
   TRUE, 
   FALSE);
END;
/
 
CREATE OR REPLACE VIEW emp_xml OF XMLType 
  XMLSCHEMA "http://www.oracle.com/emp_simple.xsd" ELEMENT "Employee"
  WITH OBJECT ID (extract(OBJECT_VALUE,   
                  '/Employee/EmployeeId/text()').getnumberval()) AS 
  SELECT
    XMLElement(
      "Employee",
      XMLAttributes('http://www.oracle.com/emp_simple.xsd' AS "xmlns",
                    'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi",
                    'http://www.oracle.com/emp_simple.xsd
                     http://www.oracle.com/emp_simple.xsd'
                       AS "xsi:schemaLocation"),
      XMLForest(e.employee_id     AS "EmployeeId", 
                e.last_name       AS "Name",  
                e.job_id          AS "Job",
                e.manager_id      AS "Manager",
                e.hire_date       AS "HireDate", 
                e.salary          AS "Salary",
                e.commission_pct  AS "Commission",
                XMLForest(d.department_id    AS "DeptNo",
                          d.department_name  AS "DeptName", 
                          d.location_id      AS "Location") AS "Dept"))
   FROM employees e, departments d 
   WHERE e.department_id = d.department_id;

A query using the SQL function extractValue to select from emp_xml:

SELECT
 extractValue(OBJECT_VALUE, '/Employee/EmployeeId') AS "a1", 
 extractValue(OBJECT_VALUE, '/Employee/Name') AS "b1",  
 extractValue(OBJECT_VALUE, '/Employee/Job') AS "c1",  
 extractValue(OBJECT_VALUE, '/Employee/Manager') AS "d1",  
 extractValue(OBJECT_VALUE, '/Employee/HireDate') AS "e1",  
 extractValue(OBJECT_VALUE, '/Employee/Salary') AS "f1",  
 extractValue(OBJECT_VALUE, '/Employee/Commission') AS "g1"
FROM emp_xml
WHERE existsNode(OBJECT_VALUE, '/Employee/Dept[Location = 1700]') = 1;

This query becomes something like the following:

SELECT e.employee_id a1, e.last_name b1, e.job_id c1, e.manager_id d1, 
       e.hire_date e1,
       e.salary f1, e.commission_pct g1
  FROM employees e, departments d 
  WHERE e.department_id = d.department_id AND d.location_id = 1700;

The rewritten query is a simple relational query. The XPath predicate in the existsNode expression is rewritten down to the predicate over relational columns as defined in view emp_view:

Querying with SQL function existsNode to select from view emp_xml:

SELECT extractValue(OBJECT_VALUE, '/Employee/EmployeeId') as "a1",
       extractValue(OBJECT_VALUE, '/Employee/Dept/DeptNo') as "b1",
       extractValue(OBJECT_VALUE, '/Employee/Dept/DeptName') as "c1",
       extractValue(OBJECT_VALUE, '/Employee/Dept/Location') as "d1"
  FROM emp_xml
  WHERE existsNode(OBJECT_VALUE, '/Employee/Dept[Location = 1700 
    AND DeptName="Finance"]') = 1;

This query becomes a simple relational query using the XPath rewrite mechanism. The XPath predicate in the existsNode expression is rewritten down to the predicate over relational columns as defined in view emp_view:

SELECT e.employee_id a1, d.department_id b1, d.department_name c1,
       d.location_id d1
  FROM employees e, departments d 
  WHERE (d.location_id = 1700 AND d.department_name = 'Finance')
    AND e.department_id = d.department_id;

Views Using Object Types, Object Views, and SYS_XMLGEN

The following sections describe XPath rewrite on XMLType views using object types, views, and SQL function sys_XMLGen.

Non-Schema-Based XMLType Views Using Object Types or Object Views

Non-schema-based XMLType views can be created on existing relational and object-relational tables with object types and object views. This provides users with an XML view of the underlying data.Existing relational data can be transformed into XMLType views by creating appropriate object types, and doing a sys_XMLGen at the top-level.

Example 18-22 Non-Schema-Based Views Constructed Using SYS_XMLGEN

CREATE TYPE emp_t AS OBJECT (empno         NUMBER(6),
                             ename         VARCHAR2(25), 
                             job           VARCHAR2(10), 
                             mgr           NUMBER(6), 
                             hiredate      DATE, 
                             sal           NUMBER(8,2), 
                             comm          NUMBER(2,2)); 
/
 
CREATE TYPE emplist_t AS TABLE OF emp_t; 
/ 
 
CREATE TYPE dept_t AS OBJECT (deptno       NUMBER(4),
                              dname        VARCHAR2(30), 
                              loc          NUMBER(4),
                              emps         emplist_t); 
/ 
 
CREATE OR REPLACE VIEW dept_ov OF dept_t
  WITH OBJECT ID (deptno) AS
  SELECT d.department_id, d.department_name, d.location_id, 
         CAST(MULTISET(
           SELECT emp_t(e.employee_id, e.last_name, e.job_id, e.manager_id,
                        e.hire_date, e.salary, e.commission_pct)
             FROM employees e
             WHERE e.department_id = d.department_id)
             AS emplist_t)
    FROM departments d; 
 
CREATE OR REPLACE VIEW dept_xml OF XMLType
  WITH OBJECT ID (extract(OBJECT_VALUE, '/ROW/DEPTNO').getNumberVal()) AS
  SELECT sys_XMLGen(OBJECT_VALUE) FROM dept_ov;

Querying department numbers that have at least one employee making a salary more than $15000:

SELECT extractValue(OBJECT_VALUE, '/ROW/DEPTNO')
  FROM dept_xml
  WHERE existsNode(OBJECT_VALUE, '/ROW/EMPS/EMP_T[sal > 15000]') = 1;

This query becomes something like the following:

SELECT d.department_id
  FROM departments d 
  WHERE exists(SELECT NULL FROM employees e 
                 WHERE e.department_id = d.department_id 
                   AND e.salary > 15000);

Example 18-23 Non-Schema-Based Views Constructed Using SYS_XMLGEN on an Object View

For example, the data in the emp table can be exposed as follows:

CREATE TYPE emp_t AS OBJECT 
      (empno         NUMBER(6), 
       ename         VARCHAR2(25), 
       job           VARCHAR2(10), 
       mgr           NUMBER(6), 
       hiredate      DATE, 
       sal           NUMBER(8,2), 
       comm          NUMBER(2,2)); 
/
 
CREATE VIEW employee_xml OF XMLType
  WITH OBJECT  ID (OBJECT_VALUE.extract('/ROW/EMPNO/text()').getnumberval()) AS
    SELECT sys_XMLGen(emp_t(e.employee_id, e.last_name, e.job_id, e.manager_id,
                            e.hire_date, e.salary, e.commission_pct))
      FROM employees e;

A major advantage of non-schema-based views is that existing object views can be easily transformed into XMLType views without any additional DDL statements. For example, consider a database that contains the object view employee_ov with the following definition:

CREATE VIEW employee_ov OF emp_t
  WITH OBJECT ID (empno) AS
  SELECT emp_t(e.employee_id, e.last_name, e.job_id, e.manager_id,
               e.hire_date, e.salary, e.commission_pct)
    FROM employees e;

Creating a non-schema-based XMLType view can be achieved by simply calling sys_XMLGen over the top-level object column. No additional types need to be created.

CREATE OR REPLACE VIEW employee_ov_xml OF XMLType
  WITH OBJECT ID (OBJECT_VALUE.extract('/ROW/EMPNO/text()').getnumberval()) AS
  SELECT sys_XMLGen(OBJECT_VALUE) FROM employee_ov;

Queries on sys_XMLGen views are rewritten to access the object attributes directly if they meet certain conditions. Simple XPath traversals with SQL functions existsNode, extractValue, and extract are candidates for rewrite. See Chapter 6, "XPath Rewrite", for details on XPath rewrite. For example, a query such as the following:

SELECT extract(OBJECT_VALUE, '/ROW/EMPNO')
  FROM employee_ov_xml
  WHERE extractValue(OBJECT_VALUE, '/ROW/ENAME') = 'Smith';

This query is rewritten to something like the following:

SELECT sys_XMLGen(e.employee_id)
  FROM employees e
  WHERE e.last_name = 'Smith';

XML-Schema-Based Views Using Object Types or Object Views

Example 18-24 illustrates XPath rewrite on an XML-schema-based XMLType view using an object type.

Example 18-24 XML-Schema-Based Views Constructed Using Object Types

This example uses the same object types andXML schema (emp_complex.xsd) as in section "Creating Schema-Based XMLType Views Over Object Views".

CREATE VIEW xmlv_adts OF XMLType
   XMLSchema "http://www.oracle.com/emp_complex.xsd" ELEMENT "Employee"
   WITH OBJECT OID (
          OBJECT_VALUE.extract(
            '/Employee/EmployeeId/text()').getNumberVal()) AS
          SELECT emp_t(e.employee_id, e.last_name, e.job_id, e.manager_id, 
                       e.hire_date, e.salary, e.commission_pct,
                       dept_t(d.department_id, d.department_name, d.location_id))
            FROM employees e, departments d
            WHERE e.department_id = d.department_id;

A query using SQL function extractValue:

SELECT extractValue(OBJECT_VALUE, '/Employee/EMPNO') "EmpID ",
       extractValue(OBJECT_VALUE, '/Employee/ENAME') "Ename ",
       extractValue(OBJECT_VALUE, '/Employee/JOB') "Job ",
       extractValue(OBJECT_VALUE, '/Employee/MGR') "Manager ",
       extractValue(OBJECT_VALUE, '/Employee/HIREDATE') "HireDate ",
       extractValue(OBJECT_VALUE, '/Employee/SAL') "Salary ",
       extractValue(OBJECT_VALUE, '/Employee/COMM') "Commission ",
       extractValue(OBJECT_VALUE, '/Employee/DEPT/DEPTNO') "Deptno ",
       extractValue(OBJECT_VALUE, '/Employee/DEPT/DNAME') "Deptname ",
       extractValue(OBJECT_VALUE, '/Employee/DEPT/LOC') "Location "
  FROM xmlv_adts
  WHERE existsNode(OBJECT_VALUE, '/Employee[SAL > 15000]') = 1;

This query becomes:

SELECT e.employee_id "EmpID ", e.last_name "Ename ", e.job_id "Job ", 
       e.manager_id "Manager ", e.hire_date "HireDate ", e.salary "Salary ",
       e.commission_pct "Commission ", d.department_id "Deptno ",
       d.department_name "Deptname ", d.location_id "Location "
  FROM employees e, departments d
  WHERE e.department_id = d.department_id AND e.salary > 15000;

XPath Rewrite Event Trace

You can disable XPath rewrite for views constructed using a SQL/XML function by using the following event flag:

ALTER SESSION SET EVENTS '19027 trace name context forever, level 64';

You can disable XPath rewrite for view constructed using object types, object views, and SQL function sys_XMLGen by using the following event flag:

ALTER SESSION SET EVENTS '19027 trace name context forever, level 1';

You can trace why XPath rewrite does not happen by using the following event flag. The trace message is printed in the tracefile.

ALTER SESSION SET EVENTS '19027 trace name context forever, level 8192';

Generating XML Schema-Based XML Without Creating Views

In the preceding examples, the CREATE VIEW statement specified the XML schema URL and element name, whereas the underlying view query simply constructed a non-schema-based XMLType. However, there are several scenarios where you may want to avoid the CREATE VIEW step, but still must construct XML schema-based XML.

To achieve this, you can use the following XML-generation SQL functions to optionally accept an XML schema URL and element name:

createXML
sys_XMLGen
sys_XMLAgg

See Also:
Chapter 16, "Generating XML Data from the Database"

Example 18-25 Generating XML Schema-Based XML Without Creating Views

This example uses the same type and XML schema definitions as in section "Wrapping Relational Department Data with Nested Employee Data as XML". With those definitions, createXML creates XML that is XML schema-based.

SELECT (XMLTYPE.createXML(
          dept_t(d.department_id, d.department_name, d.location_id, 
              CAST(MULTISET(SELECT emp_t(e.employee_id, e.last_name, e.job_id, 
               e.manager_id, e.hire_date, e.salary,
               e.commission_pct) 
                          FROM employees e 
                          WHERE e.department_id = d.department_id)
                      AS emplist_t)),
          'http://www.oracle.com/dept_complex.xsd', 'Department'))
  FROM departments d;

As XMLType has an automatic constructor, XMLTYPE.createXML could in fact be replaced by just XMLTYPE here.