| Oracle® XML DB Developer's Guide 10g Release 1 (10.1) Part Number B10790-01 |
|
|
View PDF |
| Oracle® XML DB Developer's Guide 10g Release 1 (10.1) Part Number B10790-01 |
|
|
View PDF |
This chapter describes how to create and use XMLType views.
This chapter contains these topics:
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 an expression such as extract() with getNumberVal() on the XMLType value . Oracle recommends that you use the extract() operator rather than the member function 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.
XMLType views can be constructed in the following ways:
Based on SQL/XML generation functions, such as XMLElement(), XMLForest(), XMLConcat(), XMLAgg() and Oracle Database extension function XMLColAttVal(). SQL/XML generation 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 functions must be constrained to the XML schema.
Based on object types, object views and the SYS_XMLGEN() function. Non-schema-based XMLType views can be constructed using object types, object views, and SYS_XMLGEN() function 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() function 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.
XML schema-based XMLType views can also be constructed directly from an XMLType table.
Figure 16-1 shows the CREATE VIEW clause for creating XMLType views. See Oracle Database SQL Reference for details on the CREATE VIEW syntax.
Figure 16-1 Creating XMLType Views Clause: Syntax
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 15, " Generating XML Data from the Database", for details on SQL/XML generation functions |
Using object types and object views with SYS_XMLGEN() function. Here you create the XMLType view using object types with SYS_XMLGEN() function. This method for 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.
Example 16-1 illustrates how to create an XMLType view using the SQL/XML function XMLELement().
Example 16-1 Creating an XMLType View Using the XMLElement() Function
The following statement creates an XMLType view using the XMLElement() generation function:
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, /Emp/@empno can refer directly to the empno column. An attribute is a property of an element that consists of a name and value separated by an equals sign and contained within the start-tags after the element name. In <Price units='USD'>5</Price>, units is the attribute and USD is its value, which must be in single or double quotes.
Elements may have many attributes but their retrieval order is not defined.
Existing data in relational tables or views can be exposed as XML using this mechanism. In addition, queries using extract(), extractValue() and existsNode() involving simple XPath traversal over views generated by SQL/XML generation functions are candidates for XPath rewrite to directly access the underlying relational columns or expressions based on those 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.
You can also create XMLType views using SYS_XMLGEN() with object types. SYS_XMLGEN inputs object type and generates an XMLType. Here is an equivalent query that produces the same query results using SYS_XMLGEN:
Example 16-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>1987-06-17</HIREDATE>
</EMP>
<?xml version="1.0"?>
<EMP empno="101">
<FNAME>Neena</FNAME>
<LNAME>Kochhar</LNAME>
<HIREDATE>1989-09-21</HIREDATE>
</EMP>
<?xml version="1.0"?>
<EMP empno="102">
<FNAME>Lex</FNAME>
<LNAME>De Haan</LNAME>
<HIREDATE>1993-01-13</HIREDATE>
</EMP>
Existing data in relational or object-relational tables or views can be exposed as XML using this mechanism. In addition, queries using extract(), extractValue() and existsNode() operators that involve simple XPath traversal over views generated by SYS_XMLGEN(), are candidates for XPath rewrite. XPath rewrite facilitates direct access to underlying object attributes or relational columns.
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.
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.
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.
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.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.
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 the preceding example, XMLElement() created the Employee XML element and the inner XMLForest() function created the children of the Employee element. The XMLAttributes clause inside XMLElement() constructed the required XML namespace and schema location attributes so that the XML generated conforms to the XML schema of the view. The innermost XMLForest() function created the department XML element that is nested inside the Employee element.
The XML generation function normally generates a non-XML schema-based XML instance. However, when the schema information is specified using attributes xsi:schemaLocation or xsi:noNamespaceSchemaLocation, Oracle XML DB generates 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, Oracle Database converts the XML implicitly into a well-formed and valid XML schema-based document. Any errors in the generated XML are caught when further operations, such as validate or extract, are performed on the XML instance.
You can now query the view and get the XML result from the employees and departments relational tables with NLS_DATE_FORMAT setting to 'SYYYY-MM-DD':
SQL> ALTER SESSION SET NLS_DATE_FORMAT='SYYYY-MM-DD'; 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>100</EmployeeId><Name>King</Name> <Job>AD_PRES</Job><HireDate>1987-06-17</Hi reDate><Salary>24000</Salary><Dept><DeptNo>90</DeptNo> <DeptName>Executive</DeptName><Location>1700</Location></Dept></Employee>
If you have complicated 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 16-3 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 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.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 16-4 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;
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:
SELECT value(p) AS result FROM dept_xml p 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>
To wrap relational or object-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, " XML Schema Storage and Query: The Basics".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.
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 a VARCHAR2 or Large Object (LOB).
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:
Create an object view.
Create an XMLType view over the object view.
Consider the following examples based on the employee -department relational tables and XML views of this data:
Example 16-5 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:
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 );
/
You can either create the XML schema manually or use the DBMS_XMLSchema package to generate the XML schema automatically from the existing object types as follows:
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. The generateSchemas() function in the DBMS_XMLSchema package generates a list of XML schemas one for each SQL database schema referenced by the object type and its attributes, embedded at any level.
XML schema, emp_complex.xsd also specifies how the XML elements and attributes are mapped to their corresponding attributes in the object types, as follows. See also the xdb:SQLType annotation in the following example:
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"
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_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 the extractValue() SQL function here 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. Oracle Corporation recommends that you use the extractValue() operator rather than the extractValue() member function.
In the two-step process, first create an object view, then create an XMLType view on the object view, as follows:
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;
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);
/
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
-- 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;
/
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_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;
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.
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 theSELECT list could arbitrarily construct XML of a different XML schema from the underlying table. |
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 16-7 Creating an XMLType View by Restricting Rows From an XMLType Table
Here 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 Example 16-6, 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 value(p) FROM dept_xml_tab p
WHERE extractValue(value(p), '/Department/Location') = 'DALLAS';
Here, dallas_dept_view restricts the XMLType table rows to those departments whose location is Dallas.
Example 16-8 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 8-1, "Transforming an XMLType Instance Using XMLTransform() and DBUriType to Get the XSL Style Sheet" 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;
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.
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 16-9 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 rewrites for XMLType views constructed using object types, object views, and SYS_XMLGEN() function or XMLType tables, are the same as that of regular XMLType table columns. Hence, extract(), existsNode(), or extractValue() operators 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. Hence, extract(), existsNode(), or extractValue() operators on view columns get rewritten into underlying relational accesses for better performance.
This section describes XML schema-based and non-schema-based XPath rewrites on XMLType views constructed with SQL/XML functions.
Example 16-10 illustrates XPath rewrites on non-schema-based XMLType views.
Example 16-10 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 extractValue() operator to select from EMP_VIEW
SELECT EXTRACTVALUE(VALUE(e), '/Emp/name'),
EXTRACTVALUE(VALUE(e), '/Emp/hiredate')FROM Emp_view e;
becomes:
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() operator is rewritten down to the relational column access as defined in the EMP_VIEW view.
Querying with extractValue() operator followed by getNumberVal() to select from EMP_VIEW
SELECT (EXTRACT(VALUE(e), '/Emp/@empno').getnumberval()) FROM Emp_view e;
becomes:
SELECT e.employee_id
FROM employees e
WHERE e.salary > 15000;
The rewritten query is a simple relational query. The extract() operator followed by getNumberVal() is rewritten down to the relational column access as defined in the EMP_VIEW view
Querying with existsNode() operator to select from EMP_VIEW:
SELECT EXTRACTVALUE(VALUE(e), '/Emp/name'),
EXTRACTVALUE(VALUE(e), '/Emp/hiredate')
FROM Emp_view e WHERE EXISTSNODE(VALUE(e), '/Emp[@empno=101]') = 1;
becomes:
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 existsNode() operator is rewritten down to the predicate over relational columns as defined in EMP_VIEW view.
If there is an index created on the EMPLOYEES.EMPNO column, then the query optimizer may use the index to speed up the query.
Querying with existsNode() operator to select from EMP_VIEW
SELECT EXTRACTVALUE(VALUE(e), '/Emp/name'),
EXTRACTVALUE(VALUE(e), '/Emp/hiredate'),
EXTRACTVALUE(VALUE(e), '/Emp/@empno')
FROM Emp_view e
WHERE EXISTSNODE(VALUE(e),'/Emp[name="Steven King" or @empno = 101] ') = 1;
becomes:
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 existsNode() operator is rewritten down to the predicate over relational columns as defined in EMP_VIEW view.
Querying with extract() operator to select from EMP_VIEW
SELECT EXTRACT(VALUE(e), '/Emp/name'),
EXTRACT(VALUE(e), '/Emp/hiredate')
FROM Emp_view e;
becomes:
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() operator is rewritten to expressions over relational columns.
|
Note: Since the view usesXMLForest() to formulate name and hiredate elements, the rewritten query uses equivalent CASE expression to be consistent with XMLForest() semantics. |
Example 16-11 illustrates an XPath rewrite on XML-schema-based XMLType view constructed with a SQL/XML function.
Example 16-11 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 extractValue() XML operator to select from emp_xml:
SELECT EXTRACTVALUE(VALUE(E), '/Employee/EmployeeId') as "a1", EXTRACTVALUE(VALUE(E), '/Employee/Name') as "b1", EXTRACTVALUE(VALUE(E), '/Employee/Job') as "c1", EXTRACTVALUE(VALUE(E), '/Employee/Manager') as "d1", EXTRACTVALUE(VALUE(E), '/Employee/HireDate') as "e1", EXTRACTVALUE(VALUE(E), '/Employee/Salary') as "f1", EXTRACTVALUE(VALUE(E), '/Employee/Commission') as "g1" FROM emp_xml e WHERE EXISTSNODE(VALUE(e), '/Employee/Dept[Location = 1700]') = 1;
becomes:
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 existsNode() operator is rewritten down to the predicate over relational columns as defined in the EMP_VIEW view:
Querying with existsNode() operator to select from emp_xml
SELECT EXTRACTVALUE(VALUE(e), '/Employee/EmployeeId') as "a1",
EXTRACTVALUE(VALUE(e), '/Employee/Dept/DeptNo') as "b1",
EXTRACTVALUE(VALUE(e), '/Employee/Dept/DeptName') as "c1",
EXTRACTVALUE(VALUE(e), '/Employee/Dept/Location') as "d1"
FROM emp_xml e
WHERE EXISTSNODE(VALUE(e), '/Employee/Dept[Location = 1700 and
DeptName="Finance"]') = 1;
becomes a simple relational query using the XPath rewrite mechanism. The XPath predicate in existsNode() operator is rewritten down to the predicate over relational columns as defined in the EMP_VIEW 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;
The following sections describe XPath rewrite on XMLType views using object types, views, and SYS_XMLGEN().
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 16-12 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(value(o)) FROM dept_ov o;
Querying department numbers that have at least one employee making a salary more than $15000
SELECT extractValue(value(x), '/ROW/DEPTNO') FROM dept_xml x WHERE existsNode(value(x), '/ROW/EMPS/EMP_T[sal > 15000]') = 1;
becomes:
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 16-13 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 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.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 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
(object_value.EXTRACT('/ROW/EMPNO/text()').getnumberval()) AS
SELECT SYS_XMLGEN(value(x)) from employee_ov x;
Queries on SYS_XMLGEN views are rewritten to access the object attributes directly if they meet certain conditions. Simple XPath traversals with existsNode(), extractValue(), and extract() are candidates for rewrite. See Chapter 6, " XML Schema Storage and Query: Advanced Topics", for details on XPath rewrite. 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(e.employee_id) FROM employees e WHERE e.last_name = 'Smith';
Example 16-14 illustrates XPath rewrite on an XML-schema-based XMLType view using an object type.
Example 16-14 XML-Schema-Based Views Constructed Using Object Types
This example uses the same object types andXML Schema (emp_complex.xsd) as Example 16-5.
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 extractValue() operator:
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;
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;
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 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';
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 CREATE VIEW step, but still must 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()
Example 16-15 Generating XML Schema-Based XML Without Creating Views
This example uses the same type and XML Schema definitions as Example 16-6. 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.
