11
XMLType Views

This chapter describes how to create and use XMLType views. It contains the following sections:

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 the new Oracle XML DB XML features that use XMLSchema 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 an expression such as extract() on the XMLType value.

Similar to XMLType tables, XMLType views can conform to an XML schema. This provides stronger typing and enables optimization of queries over these views.

To use XMLType views with XML schemas, you must first register your XML schema with annotations that represent the bi-directional mapping from XML to SQL object types. An XMLType view 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:

This chapter describes the two main ways you can create XMLType views:

Based on XML generation functions
Based on object types

Creating Non-Schema-Based XMLType Views

You can create a view of XMLType or a view with one or more XMLType columns, by using the SQL XML generation functions, particularly those that comply with the emerging SQLX standards.

See Also:

Chapter 10, "Generating XML Data from the Database", for details on SQLX generation functions.

Example 11-1 XMLType View: Creating XMLType View Using XMLElement() Function

The following statement creates an XMLType view using XMLElement() generation function:

DROP TABLE employees;
CREATE TABLE employees
(empno number(4), fname varchar2(20), lname varchar2(20), hire date, salary 
number(6));

INSERT INTO employees VALUES
(2100, 'John', 'Smith', Date'2000-05-24', 30000);

INSERT INTO employees VALUES
(2200, 'Mary', 'Martin', Date'1996-02-01', 30000);

CREATE OR REPLACE VIEW Emp_view OF XMLTYPE  WITH OBJECT ID
     (EXTRACT(sys_nc_rowinfo$,'/Emp/@empno').getnumberval())
       AS SELECT XMLELEMENT("Emp", XMLAttributes(empno),
            XMLForest(e.fname ||' '|| e.lname AS "name",
                       e.hire AS "hiredate")) AS "result"
          FROM employees e
          WHERE salary > 20000;

A query against the XMLType view returns the following employee data in XML format:

SELECT * FROM Emp_view;
 <Emp empno="2100"> 
   <name>John Smith</name>
   <hiredate>2000-05-24</hiredate>    
 </Emp>

 <Emp empno="2200"> 
  <name>Mary Martin</name>
  <hiredate>1996-02-01</hiredate>
 </Emp>

empno attribute in the document should become the unique identifier for each row. SYS_NC_ROWINFO$ is a virtual column that references the row XMLType instance.

Note:

In prior releases, the object identifier clause only supported attributes of the object type of the view to be specified. In this release, this has been enhanced to support any expression returning a scalar value.

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

XMLType Views can also be created using SYS_XMLGEN. An equivalent query that produces the same query results using SYS_XMLGEN is as follows:

CREATE TYPE Emp_t AS OBJECT  ("@empno" number(4), fname varchar2(2000),
lname     varchar2(2000), hiredate date);

CREATE VIEW employee_view OF XMLTYPE WITH OBJECT ID
     (EXTRACT(sys_nc_rowinfo$,'/Emp/@empno').getnumberval())
        AS SELECT SYS_XMLGEN(emp_t(empno, fname, lname, hire),
XMLFORMAT('EMP'))
          FROM employees e
          WHERE salary > 20000;

Existing data in relational or object-relational tables or views can be exposed as XML using this mechanism. In addition, queries involving simple XPath traversal over SYS_XMLGEN views are candidates for query rewrite to directly access the object attributes.

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 functions, such as XMLAgg, XMLElement, XMLForest, and so on. Here you create the XMLType view using simple XML generation functions, without the need for creating any object types. This mechanism of creating XMLType views using SQL/XML functions is more simple as you do not have to create any object types or object views. The use of object types with XML schemas however, would also give you the advantage of using Query Rewrite functionality.

See Also:
"Creating XML Schema-Based XMLType Views Using SQL/XML Generation Functions".
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 more convenient when you already have an object-relational schema and want to map it directly to XML. Also, since the view is based on XML schema, it derives several performance (memory and access) optimizations.

See Also:
"Creating XMLType Views Using Object Types and Views".

Creating XML Schema-Based XMLType Views Using SQL/XML Generation Functions

SQL/XML generation functions can be used to create XML schema-based XMLType views similar to the non-schema based case explained in the previous section. To create XML schema-based XMLType views, perform the following steps:

Create and register the XML schema document that contains the necessary XML structures.

Create an XMLType view conforming to that XML schema by using SQL/XML functions.

Note:

In this release, XPath predicates over these SQL/XML views are not rewritten. Consequently, queries expressed over such views using predicates such as extract, existsNode,... are evaluated functionally over all the rows of the view. If queriability of the view is important, consider using the object-relational approach instead.

Example 11-2 Creating XML Schema-Based XMLType Views using SQL/XML Functions

Step 1. Register XML Schema, emp_simple.xsd

Assume that you have an XML schema emp_simple.xsd that contains XML structures defining an employee. First register the XML schema and identify it using a URL:

BEGIN
 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 = "string"/> 
          </sequence> 
       </complexType> 
    </element> 
   </sequence> 
  </complexType> 
 </element> 
</schema>', TRUE, TRUE, FALSE);
END;
/

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

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

Step 2. Create XMLType View Using SQL/XML Functions

You can now create an XML schema-based XMLType view using SQL/XML functions. You can also use XMLTransform() or other SQL function that generate XML.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. All these can be specified using the XMLAttributes clause.

CREATE OR REPLACE VIEW emp_simple_xml OF XMLTYPE 
     XMLSCHEMA "http://www.oracle.com/emp_simple.xsd" ELEMENT "Employee"
     WITH OBJECT ID (extract(sys_nc_rowinfo$,   
                             '/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.empno AS "EmployeeId", 
                  e.ename AS "Name",  
                 e.job   AS "Job",
                 e.mgr   AS "Manager",
                 to_char(e.hiredate,'SYYYY-MM-DD') AS "HireDate", 
                 e.sal  AS "Salary",
                 e.comm AS "Commission",
                 XMLForest(d.deptno AS "DeptNo",
                           d.dname  AS "DeptName", 
                           d.loc    AS "Location") AS "Dept"))
     FROM emp e, dept d 
     WHERE e.deptno = d.deptno;

In the preceding example, the XMLElement() function creates the Employee XML element and the inner XMLForest() creates the kids of the employee element. The XMLAttributes clause inside the XMLElement() constructs the required XML namespace and schema location attributes so that the XML generated conforms to the view's XML schema.The innermost XMLForest() function creates the department XML element that is nested inside the Employee element.

The XML generation function simply generates a non-XML schema-based XML instance. However, in the case of XMLType views, as long as the names of the elements and attributes match those in the XML schema, Oracle converts this XML implicitly into a well-formed and valid XML schema-based document. Any errors in the generated XML will be caught when further operations, such as validate or extract,... are performed on the XML instance.

The expression:

to_char(e.hiredate,'SYYYY-MM-DD') AS "HireDate"

is needed to convert dates in SQL date format to the XML Schema format, since the default Globalization Support date format for converting date to string may be different.

You can now query the view and get the XML result from the employee and department relational tables:

SQL> select value(p) as result from emp_simple_xml p 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>7782</EmployeeId>
   <Name>CLARK</Name>
   <Job>MANAGER</Job>
   <Manager>7839</Manager>
   <HireDate>1981-06-09</HireDate>
   <Salary>2450</Salary>
  <Dept>
    <DeptNo>10</DeptNo>
    <DeptName>ACCOUNTING</DeptName>
    <Location>NEW YORK</Location>
  </Dept>
</Employee>

Using Namespaces with SQL/XML Functions

If you have complicated XML schemas involving multiple namespaces, you need to use the partially escaped mapping provided in the SQL functions and create elements with appropriate namespaces and prefixes.

Example 11-3 Using Namespace Prefixes in XMLType Views

For example the SQL query:

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.empno AS "ipo:EmployeeId", 
                      e.ename AS "ipo:Name",  
                      e.job   AS "ipo:Job"       ,
                      e.mgr   AS "ipo:Manager",
                      to_char(e.hiredate,'SYYYY-MM-DD') AS "ipo:HireDate", 
                      e.sal  AS "ipo:Salary",
                      e.comm AS "ipo:Commission",
                      XMLForest(d.deptno AS "ipo:DeptNo",
                                d.dname  AS "ipo:DeptName", 
                                d.loc    AS "ipo:Location") AS "ipo:Dept"))
  FROM emp e, dept d 
  WHERE e.deptno = d.deptno;

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:

<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>7782</ipo:EmployeeId>
  <ipo:Name>CLARK</ipo:Name>
  <ipo:Job>MANAGER</ipo:Job>
  <ipo:Manager>7839</ipo:Manager>
  <ipo:HireDate>1981-06-09</ipo:HireDate>
  <ipo:Salary>2450</ipo:Salary>
  <ipo:Dept>
    <ipo:DeptNo>10</ipo:DeptNo>
    <ipo:DeptName>ACCOUNTING</ipo:DeptName>
    <ipo:Location>NEW YORK</ipo:Location>
  </ipo:Dept>
</ipo:Employee>

If the XML schema had no target namespace you can 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.deleteSchema('emp-noname.xsd', 4); 
END;
/

BEGIN
 dbms_xmlschema.registerSchema('emp-noname.xsd', 
  '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:xdb="http://xmlns.oracle.com/xdb">
  <xs:element name = "Employee" xdb:defaultTable="EMP37_TAB">
    <xs:complexType>
     <xs:sequence>
      <xs:element name = "EmployeeId" type = "xs:positiveInteger"/>
      <xs:element name = "FirstName" type = "xs:string"/>
      <xs:element name = "LastName" type = "xs:string"/>
      <xs:element name = "Salary" type = "xs:positiveInteger"/>
     </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>');
END;
/

The following CREATE OR REPLACE VIEW 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(sys_nc_rowinfo$,   
                            '/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.empno AS "EmployeeId", 
                  e.ename AS "Name",  
                  e.job   AS "Job" , 
                  e.mgr   AS "Manager",
                  to_char(e.hiredate,'SYYYY-MM-DD') AS "HireDate", 
                    e.sal  AS "Salary",
                  e.comm AS "Commission",
                  XMLForest(d.deptno AS "DeptNo",
                            d.dname  AS "DeptName", 
                            d.loc    AS "Location") AS "Dept"))
     FROM emp e, dept d 
     WHERE e.deptno = d.deptno;

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

Creating XMLType Views Using Object Types and Views

To wrap relational data with strongly-typed XML, using the object view approach, perform these 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. See Chapter 5, "Structured Mapping of XMLType".
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 steps:
1. Create an object view
2. Create an XMLType view over the object view

Consider the following examples based on the canonical employee -department relational tables and XML views of this data:

Example 11-4 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 these steps:

Step 1. Create Object Types

CREATE OR REPLACE TYPE dept_t AS OBJECT 
      ( 
       DEPTNO        NUMBER(2), 
       DNAME         VARCHAR2(14), 
       LOC           VARCHAR2(13) 
      ); 
/ 
CREATE OR REPLACE TYPE emp_t AS OBJECT 
      ( 
       EMPNO         NUMBER(4), 
       ENAME         VARCHAR2(10), 
       JOB           VARCHAR2(9), 
       MGR           NUMBER(4), 
       HIREDATE      DATE, 
       SAL           NUMBER(7,2), 
       COMM          NUMBER(7,2), 
       DEPT          DEPT_T 
      ); 
/

Step 2. Create or Generate XMLSchema, emp.xsd

You can create the XML schema by hand or you can use the DBMS_XMLSchema package to generate the XML schema automatically from the existing object types. For example:

SELECT DBMS_XMLSchema.generateSchema('SCOTT','EMP_T') AS result FROM DUAL;

generates the XML schema for the employee type. You can supply various arguments to this function to add namespaces, and so on. You can further edit the XML schema to change the various default mappings that were generated. generateSchemas() function in the package generates a list of XML schemas one for each different SQL database schema referenced by the object type and its attributes.

Step 3. Register XML Schema, emp.xsd

XML schema, emp.xsd also specifies how the XML elements and attributes are mapped to their corresponding attributes in the object types, as follows:

BEGIN
 dbms_xmlschema.deleteSchema('http://www.oracle.com/emp.xsd', 4);
END;
/
BEGIN
 dbms_xmlschema.registerSchema('http://www.oracle.com/emp.xsd',
'<schema xmlns="http://www.w3.org/2001/XMLSchema"
 targetNamespace="http://www.oracle.com/emp.xsd" version="1.0"
 xmlns:xdb="http://xmlns.oracle.com/xdb"
 elementFormDefault="qualified"> 
 <element name = "Employee" xdb:SQLType="EMP_T" xdb:SQLSchema="SCOTT"> 
  <complexType> 
   <sequence> 
    <element name = "EmployeeId" type = "positiveInteger" xdb:SQLName="EMPNO"
                                                         xdb:SQLType="NUMBER"/> 
    <element name = "Name" type = "string" xdb:SQLName="ENAME" 
                                                         
xdb:SQLType="VARCHAR2"/> 
    <element name = "Job" type = "string" xdb:SQLName="JOB"  
xdb:SQLType="VARCHAR2"/> 
    <element name = "Manager" type = "positiveInteger" xdb:SQLName="MGR"
                                                         xdb:SQLType="NUMBER"/> 
    <element name = "HireDate" type = "date" xdb:SQLName="HIREDATE" 
                                                         xdb:SQLType="DATE"/> 
    <element name = "Salary" type = "positiveInteger" xdb:SQLName="SAL"
                                                         xdb:SQLType="NUMBER"/> 
    <element name = "Commission" type = "positiveInteger" xdb:SQLName="COMM"
                                                         xdb:SQLType="NUMBER"/> 
    <element name = "Dept" xdb:SQLName="DEPT" xdb:SQLType="DEPT_T" 
xdb:SQLSchema="SCOTT"> 
       <complexType> 
          <sequence> 
            <element name = "DeptNo" type = "positiveInteger" 
xdb:SQLName="DEPTNO"
                                                         xdb:SQLType="NUMBER"/> 
            <element name = "DeptName" type = "string" xdb:SQLName="DNAME"
                                                         
xdb:SQLType="VARCHAR2"/> 
            <element name = "Location" type = "string" xdb:SQLName="LOC"
                                                         
xdb:SQLType="VARCHAR2"/> 
          </sequence> 
       </complexType> 
    </element> 
   </sequence> 
  </complexType> 
 </element> 
</schema>', TRUE, FALSE, FALSE);
END;
/

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

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

Step 4a. Create XMLType View Using the One-Step Process

With the one-step process you must create an XMLType view on the relational tables as follows:

CREATE OR REPLACE VIEW emp_xml OF XMLTYPE 
     XMLSCHEMA "http://www.oracle.com/emp.xsd" ELEMENT "Employee"
     WITH OBJECT ID (ExtractValue(sys_nc_rowinfo$, '/Employee/EmployeeId')) AS 
     SELECT emp_t(e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm,
                  dept_t(d.deptno, d.dname, d.loc)) 
     FROM emp e, dept d 
     WHERE e.deptno = d.deptno;

This example uses the extractValue() SQL function here in the OBJECT ID clause, since extractValue() can automatically figure out the appropriate SQL datatype mapping (in this case a SQL Number) using the XML schema information.

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

In the two step process, you first create an object-relational view, then create an XMLType view on the object-relational view, as follows:

CREATE OR REPLACE VIEW emp_v OF emp_t WITH OBJECT ID (empno) AS 
     SELECT emp_t(e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm,
                  dept_t(d.deptno, d.dname, d.loc)) 
     FROM emp e, dept d 
     WHERE e.deptno = d.deptno;

-- Create the employee XMLType view over the emp_v object view
CREATE OR REPLACE VIEW emp_xml OF XMLTYPE 
     XMLSCHEMA "http://www.oracle.com/emp.xsd" ELEMENT "Employee"
     WITH OBJECT ID DEFAULT
 AS SELECT VALUE(p) FROM emp_v p;

Example 11-5 XMLType View: View 2, 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 these steps:

Step 1. Create Object Types

DROP TYPE emp_t FORCE;
DROP TYPE dept_t FORCE;
CREATE OR REPLACE TYPE emp_t AS OBJECT
      ( 
        EMPNO        NUMBER(4), 
        ENAME        VARCHAR2(10), 
        JOB          VARCHAR2(9), 
        MGR          NUMBER(4), 
        HIREDATE     DATE, 
        SAL          NUMBER(7,2), 
        COMM         NUMBER(7,2) 
       ); 
/ 
CREATE OR REPLACE TYPE emplist_t AS TABLE OF emp_t; 
/ 
CREATE OR REPLACE TYPE dept_t AS OBJECT 
       ( 
        DEPTNO       NUMBER(2), 
        DNAME        VARCHAR2(14), 
        LOC          VARCHAR2(13), 
        EMPS         EMPLIST_T 
       );
/

Step 2. Register XML Schema, dept.xsd

You can either use a pre-existing XML schema or you can generate an XML schema from the object type using the DBMS_XMLSchema.generateSchema(s) functions:

BEGIN
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" xdb:SQLType="DEPT_T" xdb:SQLSchema="SCOTT"> 
    <complexType> 
      <sequence> 
        <element name = "DeptNo" type = "positiveInteger" xdb:SQLName="DEPTNO"
                                          xdb:SQLType="NUMBER"/> 
        <element name = "DeptName" type = "string" xdb:SQLName="DNAME"
                                          xdb:SQLType="VARCHAR2"/> 
        <element name = "Location" type = "string" xdb:SQLName="LOC"
                                          xdb:SQLType="VARCHAR2"/> 
        <element name = "Employee" maxOccurs = "unbounded" xdb:SQLName = "EMPS"
                                          xdb:SQLType="EMPLIST_T" 
xdb:SQLSchema="SCOTT"> 
         <complexType> 
           <sequence> 
             <element name = "EmployeeId" type = "positiveInteger"
                                          xdb:SQLName="EMPNO" 
xdb:SQLType="NUMBER"/> 
             <element name = "Name" type = "string" xdb:SQLName="ENAME"
                                          xdb:SQLType="VARCHAR2"/> 
             <element name = "Job" type = "string" xdb:SQLName="JOB"
                                          xdb:SQLType="VARCHAR2"/> 
             <element name = "Manager" type = "positiveInteger" 
xdb:SQLName="MGR"
                                          xdb:SQLType="NUMBER"/> 
             <element name = "HireDate" type = "date" xdb:SQLName="HIREDATE"
                                          xdb:SQLType="DATE"/> 
             <element name = "Salary" type = "positiveInteger" xdb:SQLName="SAL"
                                          xdb:SQLType="NUMBER"/> 
             <element name = "Commission" type = "positiveInteger"
                                          xdb:SQLName="COMM" 
xdb:SQLType="NUMBER"/> 
          </sequence> 
         </complexType> 
        </element> 
      </sequence> 
    </complexType> 
  </element> 
</schema>', TRUE, FALSE, FALSE);
END;
/

Step 3a. Create XMLType Views on Relational Tables

Create the dept_xml XMLType view from the department object type as follows:

CREATE OR REPLACE VIEW dept_xml OF XMLTYPE
    XMLSCHEMA "http://www.oracle.com/dept.xsd" ELEMENT "Department"
    WITH OBJECT ID (EXTRACTVALUE(sys_nc_rowinfo$, '/Department/DeptNo')) AS
       SELECT dept_t(d.deptno, d.dname, d.loc, 
               cast(multiset(
                  SELECT emp_t(e.empno, e.ename, e.job, e.mgr, e.hiredate,
                         e.sal,e.comm) FROM emp e
                         WHERE e.deptno = d.deptno)
                         AS emplist_t)) 
                  FROM dept d;

Step 3b. Create XMLType Views on Relational Tables using SQL functions

You can also create the dept_xml XMLType view from the relational tables without using the object type definitions.

CREATE OR REPLACE VIEW dept_xml OF XMLTYPE
    XMLSCHEMA "http://www.oracle.com/dept.xsd" ELEMENT "Department"
    WITH OBJECT ID (EXTRACT(sys_nc_rowinfo$, 
'/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.deptno "DeptNo", 
                       d.dname "DeptName", 
                       d.loc "Location"),
             (SELECT XMLAGG(XMLElement("Employee",
                               XMLForest(e.empno "EmployeeId", 
                                    e.ename "Name", 
                                    e.job "Job", 
                                    e.mgr "Manager", 
                                    to_char(e.hiredate,'SYYYY-MM-DD') 
                                         "Hiredate"),
                                    e.sal "Salary",
                                    e.comm "Commission"))
                 FROM emp e
                 WHERE e.deptno = d.deptno))
       FROM dept 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, perhaps to transform the XML or to restrict the rows returned by using some predicates.

Example 11-6 Creating an XMLType View by Restricting rows From an XMLType Table

Here is an example of creating a XMLType view by restricting the rows returned from an underlying XMLType table. We use the dept.xsd schema described in the previous section to create the underlying table.

DROP TABLE dept_xml_tab;

CREATE TABLE dept_xml_tab OF XMLTYPE 
    XMLSCHEMA "http://www.oracle.com/dept.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 VALUE(p) FROM dept_xml_tab p 
           WHERE Extractvalue(value(p), '/Department/Location') = 'DALLAS';

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

Example 11-7 Creating an XMLType View by Transforming an XMLType Table

You can create an XMLType view by transforming the XML data using a stylesheet. For example, consider the creation of XMLType table po_tab. Refer to Example 6-1, "Transforming an XMLType Instance Using XMLTransform() and DBUriType to Get the XSL Stylesheet" for an xmltransform() example:

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(value(p),xdburitype('/home/SCOTT/xsl/po2.xsl').getxml())
    FROM po_tab p;

Referencing XMLType View Objects Using REF()

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

SELECT REF(p) FROM dept_xml p;

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

On a system-generated OID -- for views on XMLType tables or object views
On a 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 behave 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 11-8 Identifying When a View is Implicitly Updatable

For example, if the XMLType view query is based on an object view or an object constructor that is itself inherently updatable:

DROP TYPE dept_t force;
CREATE OR REPLACE TYPE dept_t AS OBJECT 
      ( 
       DEPTNO        NUMBER(2), 
       DNAME         VARCHAR2(14), 
       LOC           VARCHAR2(13) 
      ); 
/ 

BEGIN
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" xdb:SQLType="DEPT_T" xdb:SQLSchema="SCOTT"> 
    <complexType> 
      <sequence> 
        <element name = "DeptNo" type = "positiveInteger" xdb:SQLName="DEPTNO"
                                          xdb:SQLType="NUMBER"/> 
        <element name = "DeptName" type = "string" xdb:SQLName="DNAME"
                                          xdb:SQLType="VARCHAR2"/> 
        <element name = "Location" type = "string" xdb:SQLName="LOC"
                                          xdb:SQLType="VARCHAR2"/> 
      </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 (sys_nc_rowinfo$.extract('/Department/DeptNo').getnumberval()) as
select dept_t(d.deptno, d.dname, d.loc) from dept 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.xsd">
 <DeptNo>50</DeptNo>
 <DeptName>EDP</DeptName>
 <Location>NEW YORK</Location>
</Department>'));

UPDATE dept_xml d 
     SET d.sys_nc_rowinfo$ = updateXML(d.sys_nc_rowinfo$, 
'/Department/DeptNo/text()', 60)
     WHERE existsNode(d.sys_nc_rowinfo$, '/Department[DeptNo=50]') = 1;

Query Rewrite on XMLType Views

For Query Rewrites, XMLType views are the same as regular XMLType table columns. Hence, extract() or existsNode() operations on view columns, get rewritten into underlying relational accesses for better performance.

In this release, XPath predicates over these SQL/XML views are not rewritten. Consequently, queries expressed over such view using predicates such as extract, existsNode,... would be evaluated functionally over all the rows of the view. If queriability of the view is important, consider using the object-relational approach, instead of the SQL/XML functions.

Query Rewrite on XML Schema-Based Views

For example consider the following:

Example 11-9 Query Rewrite on XMLType Views: Query-Rewrite on XML Schema-Based Views

XCREATE OR REPLACE VIEW dept_ov OF dept_t
     WITH OBJECT ID (deptno) as
     SELECT d.deptno, d.dname, d.loc, cast(multiset(
        SELECT emp_t(e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm)
               FROM emp e
               WHERE e.deptno = d.deptno)
               AS emplist_t)
        FROM dept d; 

CREATE OR REPLACE VIEW dept_xml OF XMLTYPE
     WITH OBJECT ID (EXTRACT(sys_nc_rowinfo$, '/ROW/DEPTNO').getNumberVal()) AS
     SELECT sys_xmlgen(value(p)) FROM dept_ov p;

A query to select department numbers that have at least one employee making a salary more than $200000:

SELECT EXTRACTVALUE(value(x),'/ROW/DEPTNO')
  FROM dept_xml x
  WHERE EXISTSNODE(value(x), '/ROW/EMPS/EMP_T[SAL > 200]') = 1;

becomes:

ELECT d.deptno
  FROM dept d 
  WHERE EXISTS (SELECT NULL FROM emp e WHERE e.deptno = d.deptno 
                AND e.sal > 200);

Query Rewrite on Non-Schema-Based XMLType Views

Consider the following example:

Example 11-10 Query Rewrite on Non-Schema-Based Views

Non-schema-based XMLType views can be created on existing relational and object-relational tables and views. This provides users with an XML view of the underlying data.

Existing relational data can be transformed into XMLType views by creating appropriate types, and doing a SYS_XMLGEN at the top-level.For example, the data in the emp table can be exposed as follows:

CREATE TYPE Emp_t AS OBJECT  (
  EMPNO NUMBER(4),
  ENAME VARCHAR2(10),
  JOB   VARCHAR2(9),
  MGR   NUMBER(4),
  HIREDATE DATE,
  SAL   NUMBER(7,2),
  COMM  NUMBER(7,2));

CREATE VIEW employee_xml OF XMLTYPE
  WITH OBJECT  ID
        (SYS_NC_ROWINFO$.EXTRACT('/ROW/EMPNO/text()').getnumberval()) AS
    SELECT SYS_XMLGEN(
        emp_t(e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm))
    FROM emp e;

A major advantage of non-schema-based views is that existing object views can be easily transformed into XMLType views without any additional DDLs. For example, consider a database which 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.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm)
    FROM emp e;

-- Creating a non-schema-based XMLType views 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
  (SYS_NC_ROWINFO$.EXTRACT('/ROW/EMPNO/text()').getnumberval()) AS
        SELECT SYS_XMLGEN(value(x)) from employee_ov x;

-- Certain kinds of queries on SYS_XMLGEN views are rewritten to

access the object attributes directly. Simple XPath traversals with existsNode(), extractValue(), and extract() are candidates for rewrite. See Chapter 5, "Structured Mapping of XMLType", "Query Rewrite with XML Schema-Based Structured Storage", for details on query rewrite.

Note:

Query rewrite only happens with SYS_XMLGEN. Queries over views based on other functions are not rewritten.

For example, a query such as the following:

SELECT EXTRACT(VALUE(x), '/ROW/EMPNO') FROM employee_ov_xml x
WHERE  EXTRACTVALUE(value(x), '/ROW/ENAME') = 'SMITH';

is rewritten to:

SELECT SYS_XMLGEN(empno)
FROM emp e
WHERE e.ename = 'SMITH';

Ad-Hoc Generation of XML Schema-Based XML

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-XML schema-based XMLType. However, there are several scenarios where you may want to avoid the view creation step, but still need to construct XML schema-based XML.

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

createXML()
SYS_XMLGEN()
SYS_XMLAGG()

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

If the XML schema information is specified, the resulting XML is created to be XML schema-based:

SELECT XMLTYPE.createXML(dept_t(d.deptno, d.dname, d.loc, 
    CAST(MULTISET(SELECT emp_t(e.empno, e.ename, e.job, e.mgr,
                e.hiredate, e.sal, e.comm) 
    FROM emp e WHERE e.deptno = d.deptno) AS emplist_t),
              'http://www.oracle.com/dept.xsd', 'Department') 
               FROM dept d;

Validating User-Specified Information

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, "Structured Mapping of XMLType" for more details on this process

You can use the DBMS_XMLSchema generateSchema() and generateSchemas() functions 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 VARCHAR2s or Large Objects (LOBs).
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.

11 XMLType Views

What Are XMLType Views?

Creating Non-Schema-Based XMLType Views

Example 11-1 XMLType View: Creating XMLType View Using XMLElement() Function

Creating XML Schema-Based XMLType Views

Creating XML Schema-Based XMLType Views Using SQL/XML Generation Functions

Example 11-2 Creating XML Schema-Based XMLType Views using SQL/XML Functions

Step 1. Register XML Schema, emp_simple.xsd

Step 2. Create XMLType View Using SQL/XML Functions

Using Namespaces with SQL/XML Functions

Example 11-3 Using Namespace Prefixes in XMLType Views

Creating XMLType Views Using Object Types and Views

Example 11-4 Creating Schema-Based XMLType Views over object views

Step 1. Create Object Types

Step 2. Create or Generate XMLSchema, emp.xsd

Step 3. Register XML Schema, emp.xsd

Step 4a. Create XMLType View Using the One-Step Process

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

Example 11-5 XMLType View: View 2, Wrapping Relational Department Data with Nested Employee Data as XML

Step 1. Create Object Types

Step 2. Register XML Schema, dept.xsd

Step 3a. Create XMLType Views on Relational Tables

Step 3b. Create XMLType Views on Relational Tables using SQL functions

Creating XMLType Views From XMLType Tables

Example 11-6 Creating an XMLType View by Restricting rows From an XMLType Table

Example 11-7 Creating an XMLType View by Transforming an XMLType Table

Referencing XMLType View Objects Using REF()

DML (Data Manipulation Language) on XMLType Views

Example 11-8 Identifying When a View is Implicitly Updatable

Query Rewrite on XMLType Views

Query Rewrite on XML Schema-Based Views

Example 11-9 Query Rewrite on XMLType Views: Query-Rewrite on XML Schema-Based Views

Query Rewrite on Non-Schema-Based XMLType Views

Example 11-10 Query Rewrite on Non-Schema-Based Views

Ad-Hoc Generation of XML Schema-Based XML

Validating User-Specified Information

11
XMLType Views