19 XMLType Views

This chapter describes how to create and use XMLType views. It contains these topics:

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, CLOB, and binary XML storage available for 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 SQL/XML functions XMLCast and XMLQuery.

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.

XPath rewrite of queries over XMLType views is enabled for both XML schema-based and non-schema-based XMLType views. XPath rewrite is described in Chapter 8, "XPath Rewrite for Structured Storage".

To create an XML schema-based XMLType view, first register your XML schema. If the view is an object view, that is, if it is constructed using an object type, then the XML schema should have annotations that represent the bidirectional 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.

See Also:

You can create XMLType views in any of the following ways:

  • Based on SQL/XML publishing functions, such as XMLElement, XMLForest, XMLConcat, and XMLAgg. SQL/XML publishing functions can be used to construct both non-schema-based XMLType views and XML schema-based XMLType views. This enables construction of XMLType view from the underlying relational tables directly without physically migrating those relational legacy data into XML. However, to construct XML schema-based XMLType view, the XML schema must be registered and the XML value generated by SQL/XML publishing functions must be constrained to the XML schema.

  • Based on object types, object views, and Oracle SQL function sys_XMLGen. Non-schema-based XMLType views can be constructed using object types, object views, and function sys_XMLGen and XML schema-based XMLType view can be constructed using object types and object views. This enables the construction of the XMLType view from underlying relational or object relational tables directly without physically migrating the relational or object relational legacy data into XML. Creating non-schema-based XMLType view requires the use of sys_XMLGen over existing object types or object views. Creating XML-schema-based XMLType view requires to annotate the XML schema with a mapping to existing object types or to generate the XML schema from the existing object types.

  • Directly from an XMLType table.

Creating XMLType Views: Syntax

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

Figure 19-1 Creating XMLType Views Clause: Syntax

Description of Figure 19-1 follows
Description of "Figure 19-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. You can create a non-schema-based XMLType view in either of these ways:

Creating Non-Schema-Based XMLType Views using SQL/XML Publishing Functions

Example 19-1 shows how to create an XMLType view using SQL/XML function XMLELement.

Example 19-1 Creating an XMLType View using XMLELEMENT

CREATE OR REPLACE VIEW emp_view OF XMLType
  WITH OBJECT ID (XMLCast(XMLQuery('/Emp/@empno'
                                   PASSING OBJECT_VALUE RETURNING CONTENT)
                          AS BINARY_DOUBLE)) AS
  SELECT XMLElement("Emp",
                    XMLAttributes(employee_id AS "empno"),
                    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 empno="100"><name>Steven King</name><hiredate>2003-06-17</hiredate></Emp> 
<Emp empno="101"><name>Neena Kochhar</name><hiredate>2005-09-21</hiredate></Emp> 
<Emp empno="102"><name>Lex De Haan</name><hiredate>2001-01-13</hiredate></Emp>

Existing data in relational tables or views can be exposed as XML this way. If a view is generated using a SQL/XML publishing function, then queries that access that view using XPath expressions can often be rewritten. These optimized queries can then directly access the underlying relational columns. See Chapter 8, "XPath Rewrite for Structured Storage" 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.

Creating Non-Schema-Based XMLType Views using Object Types and SYS_XMLGEN

You can also create an XMLType view using object types and Oracle SQL function sys_XMLGen. Function sys_XMLGen accepts as argument an instance of an object type, and it generates a corresponding instance of XMLType. The query in Example 19-2 uses sys_XMLGen and produces the same result as the query of Example 19-1.

Example 19-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 (XMLCast(XMLQuery('/Emp/@empno'
                                   PASSING OBJECT_VALUE RETURNING CONTENT)
                          AS BINARY_DOUBLE)) 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-03</HIREDATE> 
</EMP> 
 
<?xml version="1.0"?> 
<EMP empno="101"> 
  <FNAME>Neena</FNAME> 
  <LNAME>Kochhar</LNAME> 
  <HIREDATE>21-SEP-05</HIREDATE> 
</EMP> 
 
<?xml version="1.0"?> 
<EMP empno="102"> 
  <FNAME>Lex</FNAME> 
  <LNAME>De Haan</LNAME> 
  <HIREDATE>13-JAN-01</HIREDATE> 
</EMP>

Existing relational or object-relational data can be exposed as XML data using this mechanism.

Creating XML Schema-Based XMLType Views

XML schema-based XMLType views are views whose data is constrained to conform to an XML schema. You can create an XML schema-based XMLType view in either of these ways:

Creating XML Schema-Based XMLType Views using SQL/XML Publishing Functions

You can use SQL/XML publishing 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":

  1. Create and register the XML schema document that contains the necessary XML structures. You do not need to annotate the XML schema to define the mapping between XML types and SQL object types.

  2. Use SQL/XML publishing functions to create an XMLType view that conforms to the XML schema.

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

Example 19-3 Registering XML Schema emp_simple.xsd

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
   SCHEMAURL => 'http://www.oracle.com/emp_simple.xsd',
   SCHEMADOC => '<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" minOccurs = "0"/> 
                         <element name = "Name"
                                  type = "string" minOccurs = "0"/> 
                         <element name = "Job"
                                  type = "string" minOccurs = "0"/> 
                         <element name = "Manager"
                                  type = "positiveInteger" minOccurs = "0"/> 
                         <element name = "HireDate"
                                  type = "date" minOccurs = "0"/> 
                         <element name = "Salary"
                                  type = "positiveInteger" minOccurs = "0"/> 
                         <element name = "Commission"
                                  type = "positiveInteger" minOccurs = "0"/> 
                         <element name = "Dept"> 
                           <complexType> 
                             <sequence> 
                               <element name = "DeptNo"
                                        type = "positiveInteger" minOccurs = "0"/> 
                               <element name = "DeptName"
                                        type = "string" minOccurs = "0"/> 
                               <element name = "Location"
                                        type = "positiveInteger" minOccurs = "0"/> 
                             </sequence> 
                           </complexType> 
                         </element> 
                       </sequence> 
                     </complexType> 
                   </element> 
                 </schema>',
   LOCAL     => TRUE,
   GENTYPES  => TRUE);
END;

Example 19-3 assumes that you have an XML schema emp_simple.xsd that contains XML structures defining an employee. It registers the XML schema with the target location http://www.oracle.com/emp_simple.xsd.

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

Example 19-4 Creating an XMLType View using SQL/XML Publishing Functions

CREATE OR REPLACE VIEW emp_simple_xml OF XMLType
  XMLSCHEMA "http://www.oracle.com/emp_simple.xsd" ELEMENT "Employee"
   WITH OBJECT ID (XMLCast(XMLQuery('/Employee/EmployeeId/text()'
                                    PASSING OBJECT_VALUE
                                    RETURNING CONTENT)
                           AS BINARY_DOUBLE)) 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 19-4, function XMLElement creates XML element Employee. Function XMLForest creates the children of element Employee. 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 call to XMLForest creates the children of element department, which is a child of element Employee.

By default, the XML generation functions create a non-schema-based XML instance. However, when the schema location is specified, using attribute xsi:schemaLocation or xsi:noNamespaceSchemaLocation, Oracle XML DB generates XML schema-based XML data. For XMLType views, as long as the names of the elements and attributes match those in the XML schema, the XML data is converted implicitly into a valid XML schema-based document. Any errors in the generated XML data are caught later, when operations such as validation or extraction operations are performed on the XML instance.

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

Example 19-5 Querying an XMLType View

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>2003-09-17</HireDate>
  <Salary>4400</Salary>
  <Dept>
    <DeptNo>10</Deptno>
    <DeptName>Administration</DeptName>
    <Location>1700</Location>
  </Dept>
</Employee>

Using Namespaces with SQL/XML Publishing Functions

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

The query in Example 19-6 creates XML instances that have the correct namespace, prefixes, and target schema location. It can be used as the query in the definition of view emp_simple_xml.

Example 19-6 Using Namespace Prefixes with SQL/XML Publishing Functions

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;

XMLELEMENT("IPO:EMPLOYEE",XMLATTRIBUTES('HTTP://WWW.ORACLE.COM/EMP_SIMPLE.XSD'AS
--------------------------------------------------------------------------------
<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>2004-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>2005-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 attribute xsi:noNamespaceSchemaLocation to indicate that. Example 19-7 shows such an XML schema.

Example 19-7 XML Schema with No Target Namespace

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'emp-noname.xsd',
    SCHEMADOC => '<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>',
    LOCAL     => TRUE,
    GENTYPES  => TRUE);
END;

Example 19-8 creates a view that conforms to the XML schema in Example 19-7. The XMLAttributes clause creates an XML element that contains the noNamespace schema location attribute.

Example 19-8 Creating a View for an XML Schema with No Target Namespace

CREATE OR REPLACE VIEW emp_xml OF XMLType
     XMLSCHEMA "emp-noname.xsd" ELEMENT "Employee"
     WITH OBJECT ID (XMLCast(XMLQuery('/Employee/EmployeeId/text()'
                                      PASSING OBJECT_VALUE
                                      RETURNING CONTENT)
                             AS BINARY_DOUBLE)) 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;

Example 19-9 creates view dept_xml, which conforms to XML schema dept.xsd.

Example 19-9 Using SQL/XML Functions in XML 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(
    SCHEMAURL => 'http://www.oracle.com/dept.xsd',
    SCHEMADOC => '<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>',
    LOCAL     => TRUE,
    GENTYPES  => FALSE);
  END;
/

CREATE OR REPLACE VIEW dept_xml OF XMLType
  XMLSCHEMA "http://www.oracle.com/dept.xsd" ELEMENT "Department"
  WITH OBJECT ID (XMLCast(XMLQuery('/Department/DeptNo'
                                   PASSING OBJECT_VALUE RETURNING CONTENT)
                          AS BINARY_DOUBLE)) 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;

This is the XMLType instance that results:

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>2003-09-17</Hiredate>
    <Salary>4400</Salary>
  </Employee>
</Department>

Creating XML Schema-Based XMLType Views using Object Types or Object Views

To create an XML schema-based XMLType view from object types or object views, do the following:

  1. Create the object types, if they do not yet exist.

  2. Create and then register the XML schema, annotating it to define the mapping between XML types and SQL object types and attributes.

    You can annotate the XML schema before registering it. You typically do this when you wrap existing data to create an XMLType view.

    See:

    Chapter 7, "XML Schema Storage and Query: Basic"

    You can use PL/SQL functions DBMS_XMLSchema.generateSchema and DBMS_XMLSchema.generateSchemas to generate the default XML mapping for specified object types. The generated XML schema has the requisite annotations SQLType, SQLSchema, and so on. When such an XML schema document is registered, the following validation can occur:

    • SQLType for attributes or elements based on simpleType. The SQL type must be compatible with the XML type of the corresponding XMLType data. For example, an XML string data type can be mapped only to a VARCHAR2 or a Large Object (LOB) data type.

    • SQLType specified for elements based on complexType. This is either a LOB or an object type whose structure must be compatible with the declaration of the complexType, that is, the object type must have the correct number of attributes with the correct data types.

  3. Create the XMLType view, specifying the XML schema URL and the root element name. The query defining the view first constructs the object instances and then converts them to XML.

    1. Create an object view.

    2. Create an XMLType view over the object view.

The following sections present examples of creating XML schema-based XMLType views using object types or object views. They are based on relational tables that contain employee and department data.

The same relational data is used to create each of two XMLType views. In the employee view, emp_xml, the XML document describes an employee, with the employee's department as nested information. In the department view, dept_xml, the XML data describes a department, with the department's employees as nested information.

Creating XMLType Employee View, with Nested Department Information

This section describes how to create XMLType view emp_xml based on object views. For the last step, there are two alternatives:

Step 1. Create Object Types

Example 19-10 creates the object types used in the other steps.

Example 19-10 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 and Register XML Schema emp_complex.xsd

You can create an XML schema manually, or you can use package DBMS_XMLSCHEMA to generate an XML schema automatically from existing object types, as shown in Example 19-11.

Example 19-11 Generating an XML Schema with DBMS_XMLSCHEMA.GENERATESCHEMA

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

Example 19-11 generates the XML schema for type emp_t. You can supply various arguments to PL/SQL function DBMS_XMLSCHEMA.generateSchemas, to add namespaces, and so on. You can also edit the XML schema to change the default mappings that are generated. Function generateSchemas generates a list of XML schemas, one for each SQL database schema that is referenced by the object type and its object attributes.

Example 19-12 shows how to register XML schema emp_complex.xsd, which specifies how XML elements and attributes are mapped to corresponding object attributes in the object types (the xdb:SQLType annotations).

Example 19-12 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(
    SCHEMAURL => 'http://www.oracle.com/emp_complex.xsd', 
    SCHEMADOC => '<?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>', 
    LOCAL     => TRUE, 
    GENTYPES  => FALSE);
END;
/

Example 19-12 registers the XML schema using the target location http://www.oracle.com/emp_complex.xsd.

Step 3a. Create XMLType View emp_xml using Object Type emp_t

Example 19-13 creates an XMLType view using object type emp_t.

Example 19-13 Creating XMLType View emp_xml

CREATE OR REPLACE VIEW emp_xml OF XMLType 
  XMLSCHEMA "http://www.oracle.com/emp_complex.xsd"
  ELEMENT "Employee" 
    WITH OBJECT ID (XMLCast(XMLQuery('/Employee/EMPNO'
                                     PASSING OBJECT_VALUE RETURNING CONTENT)
                            AS BINARY_DOUBLE)) 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;

Example 19-13 uses SQL/XML function XMLCast in the OBJECT ID clause to convert the XML employee number to SQL data type BINARY_DOUBLE.

Step 3b. Create XMLType View emp_xml using Object View emp_v

Example 19-14 creates an XMLType view based on an object view.

Example 19-14 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;

Creating XMLType Department View, with Nested Employee Information

This section describes how to create XMLType view dept_xml. Each department in this view contains nested employee information. For the last step, there are two alternatives:

Step 1. Create Object Types

Example 19-15 creates the object types used in the other steps.

Example 19-15 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 (see Example 19-11). Example 19-16 registers the XML schema dept_complex.xsd.

Example 19-16 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(
    SCHEMAURL => 'http://www.oracle.com/dept_complex.xsd',
    SCHEMADOC => '<?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>', 
    LOCAL     => TRUE, 
    GENTYPES  => FALSE);
END;
/
Step 3a. Create XMLType View dept_xml using Object Type dept_t

Example 19-17 creates XMLType view dept_xml using object type dept_t.

Example 19-17 Creating XMLType View dept_xml using Object Type dept_t

CREATE OR REPLACE VIEW dept_xml OF XMLType
  XMLSCHEMA "http://www.oracle.com/dept_complex.xsd" ELEMENT "Department"
  WITH OBJECT ID (XMLCast(XMLQuery('/Department/DEPTNO'
                                   PASSING OBJECT_VALUE RETURNING CONTENT)
                          AS BINARY_DOUBLE)) 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 View dept_xml using Relational Data Directly

Alternatively, you can use SQL/XML publishing functions to create XMLType view dept_xml from the relational tables without using object type dept_t. Example 19-18 illustrates this.

Example 19-18 Creating XMLType View dept_xml using Relational Data Directly

CREATE OR REPLACE VIEW dept_xml OF XMLType
  XMLSCHEMA "http://www.oracle.com/dept_complex.xsd" ELEMENT "Department"
  WITH OBJECT ID (XMLCast(XMLQuery('/Department/DEPTNO'
                                   PASSING OBJECT_VALUE RETURNING CONTENT)
                          AS BINARY_DOUBLE)) 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:

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 data or to restrict the rows returned.

Example 19-19 creates an XMLType view by restricting the rows included from an underlying XMLType table. It uses XML schema dept_complex.xsd to create the underlying table — see "Creating XMLType Department View, with Nested Employee Information".

Example 19-19 Creating an XMLType View by Restricting Rows from an XMLType 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 XMLCast(XMLQuery('/Department/LOC'
                                  PASSING OBJECT_VALUE RETURNING CONTENT)
                         AS VARCHAR2(20))
                 = 'DALLAS'; 

CREATE OR REPLACE VIEW dallas_dept_view OF XMLType

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

Example 19-20 shows how you can create an XMLType view by transforming XML data using a style sheet.

Example 19-20 Creating an XMLType View by Transforming an XMLType Table

CREATE OR REPLACE VIEW hr_po_tab OF XMLType 
  ELEMENT "PurchaseOrder" WITH OBJECT ID DEFAULT AS
  SELECT XMLtransform(OBJECT_VALUE, x.col1)
    FROM purchaseorder p, xsl_tab x;

Referencing XMLType View Objects using SQL Function REF

You can reference an XMLType view object using SQL function ref:

SELECT ref(d) FROM dept_xml_tab d;

An XMLType view reference 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 they can be used in SQL queries. These REFs act the same as REFs to object views.

DML (Data Manipulation Language) on XMLType Views

A given XMLType view might not be implicitly updatable. In that case you must write INSTEAD-OF TRIGGERS to handle all data manipulation (DML). One way to determine whether a given XMLType view is implicitly updatable is to query the view to see whether it is based on an object view or an object constructor that is itself inherently updatable. Example 19-21 illustrates this.

Example 19-21 Determining Whether an XMLType View is Implicitly Updatable

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(
    SCHEMAURL => 'http://www.oracle.com/dept_t.xsd',
    SCHEMADOC => '<?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>', 
    LOCAL     => TRUE, 
    GENTYPES  => FALSE);
END;
/

CREATE OR REPLACE VIEW dept_xml of XMLType
  XMLSchema "http://www.oracle.com/dept_t.xsd" element "Department"
  WITH OBJECT ID (XMLCast(XMLQuery('/Department/DEPTNO'
                                   PASSING OBJECT_VALUE RETURNING CONTENT)
                          AS BINARY_DOUBLE)) 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 XMLExists('/Department[DEPTNO=300]' PASSING OBJECT_VALUE);