| Oracle® XML DB Developer's Guide 10g Release 2 (10.2) Part Number B14259-02 |
|
|
PDF · Mobi · ePub |
| Oracle® XML DB Developer's Guide 10g Release 2 (10.2) Part Number B14259-02 |
|
|
PDF · Mobi · ePub |
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 a function such as extract with getNumberVal() applied to the XMLType result. It is recommended that you use SQL function extract rather than XMLType method extract() in the OBJECT IDENTIFIER clause.
Throughout this chapter XML schema refers to the W3C XML Schema 1.0 recommendation, http://www.w3.org/XML/Schema.
There are two types of XMLType views:
Non-schema-based XMLType views. These views do not confirm to a particular XML schema.
XML schema-based XMLType views. As with XMLType tables, XMLType views that conform to a particular XML schema are called XML schema-based XMLType views. These provide stronger typing than non-schema-based XMLType views.
Optimization of queries over XMLType views are enabled for both XML schema-based and non-schema-based XMLType views. This is known as XPath rewrite, and is described in the section, "XPath Rewrite on XMLType Views".
To create an XML schema-based XMLType view, first register your XML schema. If the XML schema-based XMLType view is constructed using an object type -- object view, then the XML schema should have annotations that represent the bi-directional mapping from XML to SQL object types. XMLType views conforming to this registered XML schema can then be created by providing an underlying query that constructs instances of the appropriate SQL object type.
See Also:
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 SQL function sys_XMLGen. Non-schema-based XMLType views can be constructed using object types, object views, and function sys_XMLGen and XML schema-based XMLType view can be constructed using object types and object views. This enables the construction of the XMLType view from underlying relational or object relational tables directly without physically migrating the relational or object relational legacy data into XML. Creating non-schema-based XMLType view requires the use of sys_XMLGen over existing object types or object views. Creating XML-schema-based XMLType view requires to annotate the XML schema with a mapping to existing object types or to generate the XML schema from the existing object types.
XML schema-based XMLType views can also be constructed directly from an XMLType table.
Figure 18-1 shows the CREATE VIEW clause for creating XMLType views. See Oracle Database SQL Reference for details on the CREATE VIEW syntax.
Figure 18-1 Creating XMLType Views Clause: Syntax
Non-schema-based XMLType views are XMLType views whose resultant XML value is not constrained to be a particular element in a registered XML schema. There are two main ways to create non-schema-based XMLType views:
Using SQL/XML generation functions, such as XMLElement, XMLForest, XMLConcat, XMLAgg, and XMLColAttVal. Here you create the XMLType view using simple SQL/XML generation functions, without creating object types. Creating XMLType views using SQL/XML functions is simple as you do not have to create object types or object views.
|
See Also: Chapter 16, "Generating XML Data from the Database", for details on SQL/XML generation functions |
Using object types and object views with SQL function sys_XMLGen. Here you create the XMLType view using object types with sys_XMLGen. This way of creating XMLType views is convenient when you already have an object-relational schema, such as object types, views, and tables, and want to map it directly to XML without the overhead of creating XML schema.
Example 18-1 illustrates how to create an XMLType view using the SQL/XML function XMLELement().
Example 18-1 Creating an XMLType View Using XMLELEMENT
The following statement creates an XMLType view using SQL function XMLElement:
CREATE OR REPLACE VIEW emp_view OF XMLType WITH OBJECT ID (extract(OBJECT_VALUE, '/Emp/@empno').getnumberval()) AS SELECT XMLElement("Emp", XMLAttributes(employee_id), XMLForest(e.first_name ||' '|| e.last_name AS "name", e.hire_date AS "hiredate")) AS "result" FROM employees e WHERE salary > 15000; SELECT * FROM emp_view; SYS_NC_ROWINFO$ ------------------------------------------------------------------------------------- <Emp EMPLOYEE_ID="100"><name>Steven King</name><hiredate>1987-06-17</hiredate></Emp> <Emp EMPLOYEE_ID="101"><name>Neena Kochhar</name><hiredate>1989-09-21</hiredate></Emp> <Emp EMPLOYEE_ID="102"><name>Lex De Haan</name><hiredate>1993-01-13</hiredate></Emp>
The empno attribute in the document will be used as the unique identifier for each row. As the result of the XPath rewrite operation, the XPath /Emp/@empno can refer directly to the empno column.
Existing data in relational tables or views can be exposed as XML using this mechanism. If a view is generated using a SQL/XML generation function, then queries that access the view with XPath expressions can often be optimized (rewritten). The optimized queries can then directly access the underlying relational columns. See "XPath Rewrite on XMLType Views" for details.
You can perform DML operations on these XMLType views, but, in general, you must write instead-of triggers to handle the DML operation.
You can also create XMLType views using SQL function sys_XMLGen with object types. Function sys_XMLGen inputs object type and generates an XMLType. Here is an equivalent query that produces the same query results using sys_XMLGen:
Example 18-2 Creating an XMLType View Using Object Types and SYS_XMLGEN
CREATE TYPE emp_t AS OBJECT ("@empno" NUMBER(6),
fname VARCHAR2(20),
lname VARCHAR2(25),
hiredate DATE);
/
CREATE OR REPLACE VIEW employee_view OF XMLType
WITH OBJECT ID (extract(OBJECT_VALUE, '/Emp/@empno').getnumberval()) AS
SELECT sys_XMLGen(emp_t(e.employee_id, e.first_name, e.last_name, e.hire_date),
XMLFormat('EMP'))
FROM employees e
WHERE salary > 15000;
SELECT * FROM employee_view;
SYS_NC_ROWINFO$
--------------------------------------------------------
<?xml version="1.0"?
<EMP empno="100">
<FNAME>Steven</FNAME>
<LNAME>King</LNAME>
<HIREDATE>17-JUN-87</HIREDATE>
</EMP>
<?xml version="1.0"?>
<EMP empno="101">
<FNAME>Neena</FNAME>
<LNAME>Kochhar</LNAME>
<HIREDATE>21-SEP-89</HIREDATE>
</EMP>
<?xml version="1.0"?>
<EMP empno="102">
<FNAME>Lex</FNAME>
<LNAME>De Haan</LNAME>
<HIREDATE>13-JAN-93</HIREDATE>
</EMP>
Existing data in relational or object-relational tables or views can be exposed as XML using this mechanism. In addition, queries using SQL functions extract, extractValue, and existsNode that involve simple XPath traversal over views generated by function sys_XMLGen, are candidates for XPath rewrite. XPath rewrite facilitates direct access to underlying object attributes or relational columns.
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.
These two steps are illustrated in Example 18-3 and Example 18-4, respectively.
Example 18-3 Registering XML Schema emp_simple.xsd
Assume that you have an XML schema emp_simple.xsd that contains XML structures defining an employee. This example shows how to register the XML schema and identify it using a URL.
BEGIN
DBMS_XMLSCHEMA.registerSchema('http://www.oracle.com/emp_simple.xsd',
'<schema xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.oracle.com/emp_simple.xsd" version="1.0"
xmlns:xdb="http://xmlns.oracle.com/xdb"
elementFormDefault="qualified">
<element name = "Employee">
<complexType>
<sequence>
<element name = "EmployeeId" type = "positiveInteger"/>
<element name = "Name" type = "string"/>
<element name = "Job" type = "string"/>
<element name = "Manager" type = "positiveInteger"/>
<element name = "HireDate" type = "date"/>
<element name = "Salary" type = "positiveInteger"/>
<element name = "Commission" type = "positiveInteger"/>
<element name = "Dept">
<complexType>
<sequence>
<element name = "DeptNo" type = "positiveInteger" />
<element name = "DeptName" type = "string"/>
<element name = "Location" type = "positiveInteger"/>
</sequence>
</complexType>
</element>
</sequence>
</complexType>
</element>
</schema>',
TRUE,
TRUE,
FALSE);
END;
This registers the XML schema with the target location:
http://www.oracle.com/emp_simple.xsd
You can create an XML schema-based XMLType view using SQL/XML functions. The resultant XML must conform to the XML schema specified for the view.
When using SQL/XML functions to generate XML schema-based content, you must specify the appropriate namespace information for all the elements and also indicate the location of the schema using the xsi:schemaLocation attribute. These can be specified using the XMLAttributes clause.
Example 18-4 Creating an XMLType View Using SQL/XML Functions
CREATE OR REPLACE VIEW emp_simple_xml OF XMLType
XMLSCHEMA "http://www.oracle.com/emp_simple.xsd" ELEMENT "Employee"
WITH OBJECT ID (extract(OBJECT_VALUE,
'/Employee/EmployeeId/text()').getnumberval()) AS
SELECT
XMLElement("Employee",
XMLAttributes(
'http://www.oracle.com/emp_simple.xsd' AS "xmlns",
'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi",
'http://www.oracle.com/emp_simple.xsd
http://www.oracle.com/emp_simple.xsd'
AS "xsi:schemaLocation"),
XMLForest(e.employee_id AS "EmployeeId",
e.last_name AS "Name",
e.job_id AS "Job",
e.manager_id AS "Manager",
e.hire_date AS "HireDate",
e.salary AS "Salary",
e.commission_pct AS "Commission",
XMLForest(
d.department_id AS "DeptNo",
d.department_name AS "DeptName",
d.location_id AS "Location") AS "Dept"))
FROM employees e, departments d
WHERE e.department_id = d.department_id;
In Example 18-4, XMLElement creates the Employee XML element and the inner XMLForest function call creates the children of the Employee element. The XMLAttributes clause inside XMLElement constructs the required XML namespace and schema location attributes, so that the XML data that is generated conforms to the XML schema of the view. The innermost XMLForest function call creates the department XML element that is nested inside the Employee element.
The XML generation functions generate a non-schema-based XML instance, by default. However, when the schema location is specified, using attribute xsi:schemaLocation or xsi:noNamespaceSchemaLocation, Oracle XML DB generates XML schema-based XML. In the case of XMLType views, as long as the names of the elements and attributes match those in the XML schema, the XML is converted implicitly into a valid XML schema-based document. Any errors in the generated XML data are caught when further operations, such as validate or extract operations, are performed on the XML instance.
Example 18-5 Querying an XMLType View
This example queries the XMLType view, returning an XML result from the employees and departments tables. The result of the query is shown here pretty-printed here, for clarity.
SELECT OBJECT_VALUE AS RESULT FROM emp_simple_xml WHERE ROWNUM < 2;
RESULT
---------------------------------------------------------------------
<Employee xmlns="http://www.oracle.com/emp_simple.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.oracle.com/emp_simple.xsd
http://www.oracle.com/emp_simple.xsd">
<EmployeeId>200</EmployeeId>
<Name>Whalen</Name>
<Job>AD_ASST</Job>
<Manager>101</Manager>
<HireDate>1987-09-17</HireDate>
<Salary>4400</Salary>
<Dept>
<DeptNo>10</Deptno>
<DeptName>Administration</DeptName>
<Location>1700</Location>
</Dept>
</Employee>
If you have complex XML schemas involving namespaces, you must use the partially escaped mapping provided in the SQL/XML functions and create elements with appropriate namespaces and prefixes.
Example 18-6 Using Namespace Prefixes in XMLType Views
SELECT XMLElement("ipo:Employee",
XMLAttributes('http://www.oracle.com/emp_simple.xsd' AS "xmlns:ipo",
'http://www.oracle.com/emp_simple.xsd
http://www.oracle.com/emp_simple.xsd' AS "xmlns:xsi"),
XMLForest(e.employee_id AS "ipo:EmployeeId",
e.last_name AS "ipo:Name",
e.job_id AS "ipo:Job",
e.manager_id AS "ipo:Manager",
TO_CHAR(e.hire_date,'YYYY-MM-DD') AS "ipo:HireDate",
e.salary AS "ipo:Salary",
e.commission_pct AS "ipo:Commission",
XMLForest(d.department_id AS "ipo:DeptNo",
d.department_name AS "ipo:DeptName", d.location_id
AS "ipo:Location") AS "ipo:Dept"))
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id = 20;
BEGIN
-- Delete schema if it already exists (else error)
DBMS_XMLSCHEMA.deleteSchema('emp-noname.xsd', 4);
END;
This SQL query creates the XML instances with the correct namespace, prefixes, and target schema location, and can be used as the query in the emp_simple_xml view definition. The instance created by this query looks like the following:
result ---------- <ipo:Employee xmlns:ipo="http://www.oracle.com/emp_simple.xsd" xmlns:xsi="http://www.oracle.com/emp_simple.xsd http://www.oracle.com/emp_simple.xsd"> <ipo:EmployeeId>201</ipo:EmployeeId><ipo:Name>Hartstein</ipo:Name> <ipo:Job>MK_MAN</ipo:Job><ipo:Manager>100</ipo:Manager> <ipo:HireDate>1996-02-17</ipo:HireDate><ipo:Salary>13000</ipo:Salary> <ipo:Dept><ipo:DeptNo>20</ipo:DeptNo><ipo:DeptName>Marketing</ipo:DeptName> <ipo:Location>1800</ipo:Location></ipo:Dept></ipo:Employee> <ipo:Employee xmlns:ipo="http://www.oracle.com/emp_simple.xsd" xmlns:xsi="http://www.oracle.com/emp_simple.xsd http://www.oracle.com/emp_simple.xsd"><ipo:EmployeeId>202</ipo:EmployeeId> <ipo:Name>Fay</ipo:Name><ipo:Job>MK_REP</ipo:Job><ipo:Manager>201</ipo:Manager> <ipo:HireDate>1997-08-17</ipo:HireDate><ipo:Salary>6000</ipo:Salary> <ipo:Dept><ipo:DeptNo>20</ipo:Dept No><ipo:DeptName>Marketing</ipo:DeptName><ipo:Location>1800</ipo:Location> </ipo:Dept> </ipo:Employee>
If the XML schema had no target namespace, then you could use the xsi:noNamespaceSchemaLocation attribute to denote that. For example, consider the following XML schema that is registered at location: "emp-noname.xsd":
BEGIN
DBMS_XMLSCHEMA.registerSchema(
'emp-noname.xsd',
'<schema xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb">
<element name = "Employee">
<complexType>
<sequence>
<element name = "EmployeeId" type = "positiveInteger"/>
<element name = "Name" type = "string"/>
<element name = "Job" type = "string"/>
<element name = "Manager" type = "positiveInteger"/>
<element name = "HireDate" type = "date"/>
<element name = "Salary" type = "positiveInteger"/>
<element name = "Commission" type = "positiveInteger"/>
<element name = "Dept">
<complexType>
<sequence>
<element name = "DeptNo" type = "positiveInteger" />
<element name = "DeptName" type = "string"/>
<element name = "Location" type = "positiveInteger"/>
</sequence>
</complexType>
</element>
</sequence>
</complexType>
</element>
</schema>',
TRUE,
TRUE,
FALSE);
END;
The following statement creates a view that conforms to this XML schema:
CREATE OR REPLACE VIEW emp_xml OF XMLType
XMLSCHEMA "emp-noname.xsd" ELEMENT "Employee"
WITH OBJECT ID (extract(OBJECT_VALUE,
'/Employee/EmployeeId/text()').getnumberval()) AS
SELECT XMLElement(
"Employee",
XMLAttributes('http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi",
'emp-noname.xsd' AS "xsi:noNamespaceSchemaLocation"),
XMLForest(e.employee_id AS "EmployeeId",
e.last_name AS "Name",
e.job_id AS "Job",
e.manager_id AS "Manager",
e.hire_date AS "HireDate",
e.salary AS "Salary",
e.commission_pct AS "Commission",
XMLForest(d.department_id AS "DeptNo",
d.department_name AS "DeptName",
d.location_id AS "Location") AS "Dept"))
FROM employees e, departments d
WHERE e.department_id = d.department_id;
The XMLAttributes clause creates an XML element that contains the noNamespace schema location attribute.
Example 18-7 Using SQL/XML Generation Functions in Schema-Based XMLType Views
BEGIN
-- Delete schema if it already exists (else error)
DBMS_XMLSCHEMA.deleteSchema('http://www.oracle.com/dept.xsd', 4);
END;
/
BEGIN
DBMS_XMLSCHEMA.registerSchema(
'http://www.oracle.com/dept.xsd',
'<schema xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.oracle.com/dept.xsd" version="1.0"
xmlns:xdb="http://xmlns.oracle.com/xdb"
elementFormDefault="qualified">
<element name = "Department">
<complexType>
<sequence>
<element name = "DeptNo" type = "positiveInteger"/>
<element name = "DeptName" type = "string"/>
<element name = "Location" type = "positiveInteger"/>
<element name = "Employee" maxOccurs = "unbounded">
<complexType>
<sequence>
<element name = "EmployeeId" type = "positiveInteger"/>
<element name = "Name" type = "string"/>
<element name = "Job" type = "string"/>
<element name = "Manager" type = "positiveInteger"/>
<element name = "HireDate" type = "date"/>
<element name = "Salary" type = "positiveInteger"/>
<element name = "Commission" type = "positiveInteger"/>
</sequence>
</complexType>
</element>
</sequence>
</complexType>
</element>
</schema>',
TRUE,
FALSE,
FALSE);
END;
/
CREATE OR REPLACE VIEW dept_xml OF XMLType
XMLSCHEMA "http://www.oracle.com/dept.xsd" ELEMENT "Department"
WITH OBJECT ID (extract(OBJECT_VALUE, '/Department/DeptNo').getNumberVal()) AS
SELECT XMLElement(
"Department",
XMLAttributes(
'http://www.oracle.com/emp.xsd' AS "xmlns" ,
'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi",
'http://www.oracle.com/dept.xsd
http://www.oracle.com/dept.xsd' AS "xsi:schemaLocation"),
XMLForest(d.department_id "DeptNo",
d.department_name "DeptName",
d.location_id "Location"),
(SELECT Xmlagg(XMLElement("Employee",
XMLForest(e.employee_id "EmployeeId",
e.last_name "Name",
e.job_id "Job",
e.manager_id "Manager",
to_char(e.hire_date,'YYYY-MM-DD') "Hiredate",
e.salary "Salary",
e.commission_pct "Commission")))
FROM employees e
WHERE e.department_id = d.department_id))
FROM departments d;
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 OBJECT_VALUE AS result FROM dept_xml WHERE ROWNUM < 2;
RESULT
----------------------------------------------------------------
<Department
xmlns="http://www.oracle.com/emp.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.oracle.com/dept.xsd
http://www.oracle.com/dept.xsd">
<DeptNo>10</DeptNo>
<DeptName>Administration</DeptName>
<Location>1700</Location>
<Employee>
<EmployeeId>200</EmployeeId>
<Name>Whalen</Name>
<Job>AD_ASST</Job>
<Manager>101</Manager>
<Hiredate>1987-09-17</Hiredate>
<Salary>4400</Salary>
</Employee>
</Department>
To wrap relational or object-relational data with strongly-typed XML using the object view approach, perform the following steps:
Create object types.
Create (or generate) and then register an XML schema document that contains the XML structures, along with its mapping to the SQL object types and attributes. The XML schema can be generated from the existing object types and must be annotated to contain the bidirectional mapping from XML to the object types.
You can fill in the optional Oracle XML DB attributes before registering the XML schema. In this case, Oracle validates the extra information to ensure that the specified values for the Oracle XML DB attributes are compatible with the rest of the XML schema declarations. This form of XML schema registration typically happens when wrapping existing data using XMLType views.
See:
Chapter 5, "XML Schema Storage and Query: Basic" for more details on this processYou can use PL/SQL functions DBMS_XMLSchema .generateSchema and generateSchemas to generate the default XML mapping for specified object types. The generated XML schema document has the SQLType, SQLSchema, and so on, attributes filled in. When these XML schema documents are then registered, the following validation forms can occur:
SQLType for attributes or elements based on simpleType. This is compatible with the corresponding XMLType. For example, an XML string datatype can only be mapped to VARCHAR2 or a Large Object (LOB) datatype.
SQLType specified for elements based on complexType. This is either a LOB or an object type whose structure is compatible with the declaration of the complexType, that is, the object type has the right number of attributes with the right datatypes.
Create the XMLType view and specify the XML schema URL and the root element name. The underlying view query first constructs the object instances and then converts them to XML. This step can also be done in two parts:
Create an object view.
Create an XMLType view over the object view.
For examples, see the following sections, which are based on the employee and department relational tables and XML views of this data:
For the first example view, to wrap the relational employee data with nested department information as XML, follow Step 1 through Step 4b.
Example 18-8 creates the object types for the views.
Example 18-8 Creating Object Types for Schema-Based XMLType Views
CREATE TYPE dept_t AS OBJECT
(deptno NUMBER(4),
dname VARCHAR2(30),
loc NUMBER(4));
/
CREATE TYPE emp_t AS OBJECT
(empno NUMBER(6),
ename VARCHAR2(25),
job VARCHAR2(10),
mgr NUMBER(6),
hiredate DATE,
sal NUMBER(8,2),
comm NUMBER(2,2),
dept dept_t );
/
You can create an XML schema manually or use package DBMS_XMLSCHEMA to generate it automatically from the existing object types, as shown in Example 18-9.
Example 18-9 Generating an XML Schema with DBMS_XMLSCHEMA.GENERATESCHEMA
SELECT DBMS_XMLSCHEMA.generateSchema('HR','EMP_T') AS result FROM DUAL;
This generates the XML schema for the employee type. You can supply various arguments to this function to add namespaces, and so on. You can also edit the XML schema to change the various default mappings that were generated. Function DBMS_XMLSCHEMA.generateSchemas generates a list of XML schemas, one for each SQL database schema referenced by the object type and its attributes, embedded at any level.
XML schema, emp_complex.xsd also specifies how the XML elements and attributes are mapped to their corresponding attributes in the object types. Example 18-10 shows how to register XML schema emp_complex.xsd. See also the xdb:SQLType annotation Example 18-10.
Example 18-10 Registering XML Schema emp_complex.xsd
BEGIN
-- Delete schema if it already exists (else error)
DBMS_XMLSCHEMA.deleteSchema('http://www.oracle.com/emp_complex.xsd', 4);
END;
/
COMMIT;
BEGIN
DBMS_XMLSCHEMA.registerSchema(
'http://www.oracle.com/emp_complex.xsd',
'<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xdb="http://xmlns.oracle.com/xdb"
xsi:schemaLocation="http://xmlns.oracle.com/xdb
http://xmlns.oracle.com/xdb/XDBSchema.xsd">
<xsd:element name="Employee" type="EMP_TType" xdb:SQLType="EMP_T"
xdb:SQLSchema="HR"/>
<xsd:complexType name="EMP_TType" xdb:SQLType="EMP_T" xdb:SQLSchema="HR"
xdb:maintainDOM="false">
<xsd:sequence>
<xsd:element name="EMPNO" type="xsd:double" xdb:SQLName="EMPNO"
xdb:SQLType="NUMBER"/>
<xsd:element name="ENAME" xdb:SQLName="ENAME" xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="25"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="JOB" xdb:SQLName="JOB" xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="10"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="MGR" type="xsd:double" xdb:SQLName="MGR"
xdb:SQLType="NUMBER"/>
<xsd:element name="HIREDATE" type="xsd:date" xdb:SQLName="HIREDATE"
xdb:SQLType="DATE"/>
<xsd:element name="SAL" type="xsd:double" xdb:SQLName="SAL"
xdb:SQLType="NUMBER"/>
<xsd:element name="COMM" type="xsd:double" xdb:SQLName="COMM"
xdb:SQLType="NUMBER"/>
<xsd:element name="DEPT" type="DEPT_TType" xdb:SQLName="DEPT"
xdb:SQLSchema="HR" xdb:SQLType="DEPT_T"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="DEPT_TType" xdb:SQLType="DEPT_T" xdb:SQLSchema="HR"
xdb:maintainDOM="false">
<xsd:sequence>
<xsd:element name="DEPTNO" type="xsd:double" xdb:SQLName="DEPTNO"
xdb:SQLType="NUMBER"/>
<xsd:element name="DNAME" xdb:SQLName="DNAME" xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="30"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="LOC" type="xsd:double" xdb:SQLName="LOC"
xdb:SQLType="NUMBER"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>',
TRUE,
FALSE,
FALSE);
END;
/
The preceding statement registers the XML schema with the target location:
"http://www.oracle.com/emp_complex.xsd"
With the one-step process, you must create an XMLType view on the relational tables as shown in Example 18-11.
Example 18-11 Creating an XMLType View
CREATE OR REPLACE VIEW emp_xml OF XMLType
XMLSCHEMA "http://www.oracle.com/emp_complex.xsd"
ELEMENT "Employee"
WITH OBJECT ID (extractValue(OBJECT_VALUE, '/Employee/EMPNO')) AS
SELECT emp_t(e.employee_id, e.last_name, e.job_id, e.manager_id, e.hire_date,
e.salary, e.commission_pct,
dept_t(d.department_id, d.department_name, d.location_id))
FROM employees e, departments d
WHERE e.department_id = d.department_id;
This example uses SQL function extractValue in the OBJECT ID clause because extractValue can automatically calculate the appropriate SQL datatype mapping — in this case a SQL NUMBER — using the XML schema information. It is recommended that you use SQL function extractValue rather than XMLType method extractValue().
In the two-step process, you first create an object view, then create an XMLType view on the object view, as shown in Example 18-12.
Example 18-12 Creating an Object View and an XMLType View on the Object View
CREATE OR REPLACE VIEW emp_v OF emp_t WITH OBJECT ID (empno) AS
SELECT emp_t(e.employee_id, e.last_name, e.job_id, e.manager_id, e.hire_date,
e.salary, e.commission_pct,
dept_t(d.department_id, d.department_name, d.location_id))
FROM employees e, departments d
WHERE e.department_id = d.department_id;
CREATE OR REPLACE VIEW emp_xml OF XMLType
XMLSCHEMA "http://www.oracle.com/emp_complex.xsd" ELEMENT "Employee"
WITH OBJECT ID DEFAULT
AS SELECT VALUE(p) FROM emp_v p;
For the second example view, to wrap the relational department data with nested employee information as XML, follow Step 1 through Step 3b.
The first step is to create the object types needed, as shown in Example 18-13.
Example 18-13 Creating Object Types
CREATE TYPE emp_t AS OBJECT (empno NUMBER(6),
ename VARCHAR2(25),
job VARCHAR2(10),
mgr NUMBER(6),
hiredate DATE,
sal NUMBER(8,2),
comm NUMBER(2,2)); /
CREATE OR REPLACE TYPE emplist_t AS TABLE OF emp_t;
/
CREATE TYPE dept_t AS OBJECT (deptno NUMBER(4),
dname VARCHAR2(30),
loc NUMBER(4),
emps emplist_t);
/
You can either use a pre-existing XML schema or generate an XML schema from the object type with function DBMS_XMLSCHEMA.generateSchema or DBMS_XMLSCHEMA.generateSchemas. Example 18-14 shows how to register the XML schema dept_complex.xsd.
Example 18-14 Registering XML Schema dept_complex.xsd
BEGIN
-- Delete schema if it already exists (else error)
DBMS_XMLSCHEMA.deleteSchema('http://www.oracle.com/dept_complex.xsd', 4);
END;
/
BEGIN
DBMS_XMLSCHEMA.registerSchema('http://www.oracle.com/dept_complex.xsd',
'<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xdb="http://xmlns.oracle.com/xdb"
xsi:schemaLocation="http://xmlns.oracle.com/xdb
http://xmlns.oracle.com/xdb/XDBSchema.xsd">
<xsd:element name="Department" type="DEPT_TType" xdb:SQLType="DEPT_T"
xdb:SQLSchema="HR"/>
<xsd:complexType name="DEPT_TType" xdb:SQLType="DEPT_T" xdb:SQLSchema="HR"
xdb:maintainDOM="false">
<xsd:sequence>
<xsd:element name="DEPTNO" type="xsd:double" xdb:SQLName="DEPTNO"
xdb:SQLType="NUMBER"/>
<xsd:element name="DNAME" xdb:SQLName="DNAME" xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="30"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="LOC" type="xsd:double" xdb:SQLName="LOC"
xdb:SQLType="NUMBER"/>
<xsd:element name="EMPS" type="EMP_TType" maxOccurs="unbounded"
minOccurs="0" xdb:SQLName="EMPS"
xdb:SQLCollType="EMPLIST_T" xdb:SQLType="EMP_T"
xdb:SQLSchema="HR" xdb:SQLCollSchema="HR"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="EMP_TType" xdb:SQLType="EMP_T" xdb:SQLSchema="HR"
xdb:maintainDOM="false">
<xsd:sequence>
<xsd:element name="EMPNO" type="xsd:double" xdb:SQLName="EMPNO"
xdb:SQLType="NUMBER"/>
<xsd:element name="ENAME" xdb:SQLName="ENAME" xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="25"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="JOB" xdb:SQLName="JOB" xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="10"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="MGR" type="xsd:double" xdb:SQLName="MGR"
xdb:SQLType="NUMBER"/>
<xsd:element name="HIREDATE" type="xsd:date" xdb:SQLName="HIREDATE"
xdb:SQLType="DATE"/>
<xsd:element name="SAL" type="xsd:double" xdb:SQLName="SAL"
xdb:SQLType="NUMBER"/>
<xsd:element name="COMM" type="xsd:double" xdb:SQLName="COMM"
xdb:SQLType="NUMBER"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>',
TRUE,
FALSE,
FALSE);
END;
/
The next step is to create the dept_xml XMLType view from the department object type, as shown in Example 18-15.
Example 18-15 Creating XMLType Views on Relational Tables
CREATE OR REPLACE VIEW dept_xml OF XMLType
XMLSChema "http://www.oracle.com/dept_complex.xsd" ELEMENT "Department"
WITH OBJECT ID (extractValue(OBJECT_VALUE, '/Department/DEPTNO')) AS
SELECT dept_t(d.department_id, d.department_name, d.location_id,
CAST(MULTISET(SELECT emp_t(e.employee_id, e.last_name, e.job_id,
e.manager_id, e.hire_date,
e.salary, e.commission_pct)
FROM employees e
WHERE e.department_id = d.department_id)
AS emplist_t))
FROM departments d;
You can also create the dept_xml XMLType view from the relational tables without using the object type definitions, that is, using SQL/XML generation functions. Example 18-16 demonstrates this.
Example 18-16 Creating XMLType Views Using SQL/XML Functions
CREATE OR REPLACE VIEW dept_xml OF XMLType
XMLSCHEMA "http://www.oracle.com/dept_complex.xsd" ELEMENT "Department"
WITH OBJECT ID (extract(OBJECT_VALUE, '/Department/DEPTNO').getNumberVal()) AS
SELECT
XMLElement(
"Department",
XMLAttributes('http://www.oracle.com/dept_complex.xsd' AS "xmlns",
'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi",
'http://www.oracle.com/dept_complex.xsd
http://www.oracle.com/dept_complex.xsd'
AS "xsi:schemaLocation"),
XMLForest(d.department_id "DeptNo", d.department_name "DeptName",
d.location_id "Location"),
(SELECT XMLAgg(XMLElement("Employee",
XMLForest(e.employee_id "EmployeeId",
e.last_name "Name",
e.job_id "Job",
e.manager_id "Manager",
e.hire_date "Hiredate",
e.salary "Salary",
e.commission_pct "Commission")))
FROM employees e WHERE e.department_id = d.department_id))
FROM departments d;
Note:
The XML schema and element information must be specified at the view level because 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 18-17 Creating an XMLType View by Restricting Rows From an XMLType Table
This is an example of creating an XMLType view by restricting the rows returned from an underlying XMLType table. This example uses the dept_complex.xsd XML schema, described in section "Wrapping Relational Department Data with Nested Employee Data as XML", to create the underlying table.
CREATE TABLE dept_xml_tab OF XMLType
XMLSchema "http://www.oracle.com/dept_complex.xsd" ELEMENT "Department"
NESTED TABLE xmldata."EMPS" STORE AS dept_xml_tab_tab1;
CREATE OR REPLACE VIEW dallas_dept_view OF XMLType
XMLSchema "http://www.oracle.com/dept.xsd" ELEMENT "Department"
AS SELECT OBJECT_VALUE FROM dept_xml_tab
WHERE extractValue(OBJECT_VALUE, '/Department/Location') = 'DALLAS';
Here, dallas_dept_view restricts the XMLType table rows to those departments whose location is Dallas.
Example 18-18 Creating an XMLType View by Transforming an XMLType Table
You can create an XMLType view by transforming the XML data using a style sheet. For example, consider the creation of XMLType table po_tab. Refer to Example 9-2, "Using XMLTRANSFORM and DBURITYPE to Retrieve a Style Sheet" for an example that uses XMLtransform:
DROP TABLE po_tab; CREATE TABLE po_tab OF XMLType XMLSCHEMA "ipo.xsd" ELEMENT "PurchaseOrder";
You can then create a view of the table as follows:
CREATE OR REPLACE VIEW hr_po_tab OF XMLType XMLSCHEMA "hrpo.xsd"
ELEMENT "PurchaseOrder"
WITH OBJECT ID DEFAULT
AS SELECT XMLtransform(OBJECT_VALUE,
XDBURIType('/home/SCOTT/xsl/po2.xsl').getxml())
FROM po_tab;
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 18-19 Identifying When a View is Implicitly Updatable
One way to identify when an XMLType view is implicitly updatable is to use an XMLType view query to determine if the view is based on an object view or an object constructor that is itself inherently updatable, as follows:
CREATE TYPE dept_t AS OBJECT
(deptno NUMBER(4),
dname VARCHAR2(30),
loc NUMBER(4));
/
BEGIN
-- Delete schema if it already exists (else error)
DBMS_XMLSCHEMA.deleteSchema('http://www.oracle.com/dept.xsd', 4);
END;
/
COMMIT;
BEGIN
DBMS_XMLSCHEMA.registerSchema('http://www.oracle.com/dept_t.xsd',
'<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xdb="http://xmlns.oracle.com/xdb"
xsi:schemaLocation="http://xmlns.oracle.com/xdb
http://xmlns.oracle.com/xdb/XDBSchema.xsd">
<xsd:element name="Department" type="DEPT_TType" xdb:SQLType="DEPT_T"
xdb:SQLSchema="HR"/>
<xsd:complexType name="DEPT_TType" xdb:SQLType="DEPT_T" xdb:SQLSchema="HR"
xdb:maintainDOM="false">
<xsd:sequence>
<xsd:element name="DEPTNO" type="xsd:double" xdb:SQLName="DEPTNO"
xdb:SQLType="NUMBER"/>
<xsd:element name="DNAME" xdb:SQLName="DNAME" xdb:SQLType="VARCHAR2">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="30"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="LOC" type="xsd:double" xdb:SQLName="LOC"
xdb:SQLType="NUMBER"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>',
TRUE,
FALSE,
FALSE);
END;
/
CREATE OR REPLACE VIEW dept_xml of XMLType
XMLSchema "http://www.oracle.com/dept_t.xsd" element "Department"
WITH OBJECT ID (OBJECT_VALUE.extract('/Department/DEPTNO').getnumberval()) AS
SELECT dept_t(d.department_id, d.department_name, d.location_id)
FROM departments d;
INSERT INTO dept_xml
VALUES (
XMLType.createXML(
'<Department
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://www.oracle.com/dept_t.xsd" >
<DEPTNO>300</DEPTNO>
<DNAME>Processing</DNAME>
<LOC>1700</LOC>
</Department>'));
UPDATE dept_xml d
SET d.OBJECT_VALUE = updateXML(d.OBJECT_VALUE, '/Department/DNAME/text()',
'Shipping')
WHERE existsNode(d.OBJECT_VALUE, '/Department[DEPTNO=300]') = 1;
XPath rewrites for XMLType views constructed using XMLType tables or object types, object views, and SQL function sys_XMLGen are the same as for regular XMLType table columns. Hence, SQL functions extract, existsNode, and extractValue on view columns get rewritten into underlying relational or object-relational accesses for better performance.
XPath rewrites for XMLType views constructed using the SQL/XML generation functions are also supported. Functions extract, existsNode, and extractValue on view columns get rewritten into underlying relational accesses for better performance.
See Also:
Chapter 6, "XPath Rewrite"This section describes XML schema-based and non-schema-based XPath rewrites on XMLType views constructed with SQL/XML functions.
Example 18-20 illustrates XPath rewrites on non-schema-based XMLType views.
Example 18-20 Non-Schema-Based Views Constructed Using SQL/XML
CREATE OR REPLACE VIEW emp_view OF XMLType
WITH OBJECT ID (extract(OBJECT_VALUE, '/Emp/@empno').getnumberval())
AS SELECT XMLElement("Emp", XMLAttributes(employee_id),
XMLForest(e.first_name ||' '|| e.last_name AS "name",
e.hire_date AS "hiredate")) AS "result"
FROM employees e
WHERE salary > 15000;
Querying with SQL function extractValue to select from emp_view:
SELECT extractValue(OBJECT_VALUE, '/Emp/name'),
extractValue(OBJECT_VALUE, '/Emp/hiredate'
FROM emp_view;
This query becomes something like the following:
SELECT e.first_name ||' '|| e.last_name, e.hire_date FROM employees e WHERE e.salary > 15000;
The rewritten query is a simple relational query. The extractValue expression is rewritten down to the relational column access as defined in view emp_view.
Querying with SQL function extractValue followed by method getNumberVal() to select from emp_view:
SELECT (extract(OBJECT_VALUE, '/Emp/@empno').getnumberval()) FROM emp_view;
This query becomes something like the following:
SELECT e.employee_id FROM employees e WHERE e.salary > 15000;
The rewritten query is a simple relational query. The extract expression followed by getNumberVal() is rewritten down to the relational column access as defined in view emp_view.
Querying with SQL function existsNode to select from view emp_view:
SELECT extractValue(OBJECT_VALUE, '/Emp/name'),
extractValue(OBJECT_VALUE, '/Emp/hiredate')
FROM emp_view WHERE existsNode(OBJECT_VALUE, '/Emp[@empno=101]') = 1;
This query becomes something like the following:
SELECT e.first_name ||' '|| e.last_name, e.hire_date FROM employees e WHERE e.employee_id = 101 AND e.salary > 15000;
The rewritten query is a simple relational query. The XPath predicate in the existsNode expression is rewritten down to the predicate over relational columns as defined in view emp_view.
If there is an index created on column employees.empno, then the query optimizer can use the index to speed up the query.
Querying with existsNode to select from view emp_view:
SELECT extractValue(OBJECT_VALUE, '/Emp/name'),
extractValue(OBJECT_VALUE, '/Emp/hiredate'),
extractValue(OBJECT_VALUE, '/Emp/@empno')
FROM emp_view
WHERE existsNode(OBJECT_VALUE, '/Emp[name="Steven King" or @empno = 101] ')
= 1;
This query becomes something like the following:
SELECT e.first_name ||' '|| e.last_name, e.hire_date, e.employee_id
FROM employees e
WHERE (e.first_name ||' '|| e.last_name = 'Steven King'
OR e.employee_id = 101)
AND e.salary > 15000;
The rewritten query is a simple relational query. The XPath predicate in the existsNode expression is rewritten down to the predicate over relational columns as defined in view emp_view.
Querying with extract to select from view emp_view:
SELECT extract(OBJECT_VALUE, '/Emp/name'),
extract(OBJECT_VALUE, '/Emp/hiredate')
FROM emp_view;
This query becomes something like the following:
SELECT CASE WHEN e.first_name ||' '|| e.last_name IS NOT NULL THEN
XMLElement("name",e.first_name ||' '|| e.last_name) ELSE NULL END,
CASE WHEN e.hire_date IS NOT NULL
THEN XMLElement("hiredate", e.hire_date)
ELSE NULL END
FROM employees e WHERE e.salary > 15000;
The rewritten query is a simple relational query. The extract expression is rewritten to expressions over relational columns.
Note:
Since the view uses SQL functionXMLForest to formulate name and hiredate elements, the rewritten query uses equivalent CASE expression to be consistent with XMLForest semantics.Example 18-21 illustrates an XPath rewrite on XML-schema-based XMLType view constructed with a SQL/XML function.
Example 18-21 XML-Schema-Based Views Constructed With SQL/XML
BEGIN
-- Delete schema if it already exists (else error)
DBMS_XMLSCHEMA.deleteSchema('http://www.oracle.com/emp_simple.xsd', 4);
END;
/
BEGIN
DBMS_XMLSCHEMA.registerSchema(
'http://www.oracle.com/emp_simple.xsd',
'<schema
xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.oracle.com/emp_simple.xsd" version="1.0"
xmlns:xdb="http://xmlns.oracle.com/xdb"
elementFormDefault="qualified">
<element name = "Employee">
<complexType>
<sequence>
<element name = "EmployeeId" type = "positiveInteger"/>
<element name = "Name" type = "string"/>
<element name = "Job" type = "string"/>
<element name = "Manager" type = "positiveInteger"/>
<element name = "HireDate" type = "date"/>
<element name = "Salary" type = "positiveInteger"/>
<element name = "Commission" type = "positiveInteger"/>
<element name = "Dept">
<complexType>
<sequence>
<element name = "DeptNo" type = "positiveInteger" />
<element name = "DeptName" type = "string"/>
<element name = "Location" type = "positiveInteger"/>
</sequence>
</complexType>
</element>
</sequence>
</complexType>
</element>
</schema>',
TRUE,
TRUE,
FALSE);
END;
/
CREATE OR REPLACE VIEW emp_xml OF XMLType
XMLSCHEMA "http://www.oracle.com/emp_simple.xsd" ELEMENT "Employee"
WITH OBJECT ID (extract(OBJECT_VALUE,
'/Employee/EmployeeId/text()').getnumberval()) AS
SELECT
XMLElement(
"Employee",
XMLAttributes('http://www.oracle.com/emp_simple.xsd' AS "xmlns",
'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi",
'http://www.oracle.com/emp_simple.xsd
http://www.oracle.com/emp_simple.xsd'
AS "xsi:schemaLocation"),
XMLForest(e.employee_id AS "EmployeeId",
e.last_name AS "Name",
e.job_id AS "Job",
e.manager_id AS "Manager",
e.hire_date AS "HireDate",
e.salary AS "Salary",
e.commission_pct AS "Commission",
XMLForest(d.department_id AS "DeptNo",
d.department_name AS "DeptName",
d.location_id AS "Location") AS "Dept"))
FROM employees e, departments d
WHERE e.department_id = d.department_id;
A query using the SQL function extractValue to select from emp_xml:
SELECT extractValue(OBJECT_VALUE, '/Employee/EmployeeId') AS "a1", extractValue(OBJECT_VALUE, '/Employee/Name') AS "b1", extractValue(OBJECT_VALUE, '/Employee/Job') AS "c1", extractValue(OBJECT_VALUE, '/Employee/Manager') AS "d1", extractValue(OBJECT_VALUE, '/Employee/HireDate') AS "e1", extractValue(OBJECT_VALUE, '/Employee/Salary') AS "f1", extractValue(OBJECT_VALUE, '/Employee/Commission') AS "g1" FROM emp_xml WHERE existsNode(OBJECT_VALUE, '/Employee/Dept[Location = 1700]') = 1;
This query becomes something like the following:
SELECT e.employee_id a1, e.last_name b1, e.job_id c1, e.manager_id d1,
e.hire_date e1,
e.salary f1, e.commission_pct g1
FROM employees e, departments d
WHERE e.department_id = d.department_id AND d.location_id = 1700;
The rewritten query is a simple relational query. The XPath predicate in the existsNode expression is rewritten down to the predicate over relational columns as defined in view emp_view:
Querying with SQL function existsNode to select from view emp_xml:
SELECT extractValue(OBJECT_VALUE, '/Employee/EmployeeId') as "a1",
extractValue(OBJECT_VALUE, '/Employee/Dept/DeptNo') as "b1",
extractValue(OBJECT_VALUE, '/Employee/Dept/DeptName') as "c1",
extractValue(OBJECT_VALUE, '/Employee/Dept/Location') as "d1"
FROM emp_xml
WHERE existsNode(OBJECT_VALUE, '/Employee/Dept[Location = 1700
AND DeptName="Finance"]') = 1;
This query becomes a simple relational query using the XPath rewrite mechanism. The XPath predicate in the existsNode expression is rewritten down to the predicate over relational columns as defined in view emp_view:
SELECT e.employee_id a1, d.department_id b1, d.department_name c1,
d.location_id d1
FROM employees e, departments d
WHERE (d.location_id = 1700 AND d.department_name = 'Finance')
AND e.department_id = d.department_id;
The following sections describe XPath rewrite on XMLType views using object types, views, and SQL function 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 18-22 Non-Schema-Based Views Constructed Using SYS_XMLGEN
CREATE TYPE emp_t AS OBJECT (empno NUMBER(6),
ename VARCHAR2(25),
job VARCHAR2(10),
mgr NUMBER(6),
hiredate DATE,
sal NUMBER(8,2),
comm NUMBER(2,2));
/
CREATE TYPE emplist_t AS TABLE OF emp_t;
/
CREATE TYPE dept_t AS OBJECT (deptno NUMBER(4),
dname VARCHAR2(30),
loc NUMBER(4),
emps emplist_t);
/
CREATE OR REPLACE VIEW dept_ov OF dept_t
WITH OBJECT ID (deptno) AS
SELECT d.department_id, d.department_name, d.location_id,
CAST(MULTISET(
SELECT emp_t(e.employee_id, e.last_name, e.job_id, e.manager_id,
e.hire_date, e.salary, e.commission_pct)
FROM employees e
WHERE e.department_id = d.department_id)
AS emplist_t)
FROM departments d;
CREATE OR REPLACE VIEW dept_xml OF XMLType
WITH OBJECT ID (extract(OBJECT_VALUE, '/ROW/DEPTNO').getNumberVal()) AS
SELECT sys_XMLGen(OBJECT_VALUE) FROM dept_ov;
Querying department numbers that have at least one employee making a salary more than $15000:
SELECT extractValue(OBJECT_VALUE, '/ROW/DEPTNO') FROM dept_xml WHERE existsNode(OBJECT_VALUE, '/ROW/EMPS/EMP_T[sal > 15000]') = 1;
This query becomes something like the following:
SELECT d.department_id
FROM departments d
WHERE exists(SELECT NULL FROM employees e
WHERE e.department_id = d.department_id
AND e.salary > 15000);
Example 18-23 Non-Schema-Based Views Constructed Using SYS_XMLGEN on an Object View
For example, the data in the emp table can be exposed as follows:
CREATE TYPE emp_t AS OBJECT
(empno NUMBER(6),
ename VARCHAR2(25),
job VARCHAR2(10),
mgr NUMBER(6),
hiredate DATE,
sal NUMBER(8,2),
comm NUMBER(2,2));
/
CREATE VIEW employee_xml OF XMLType
WITH OBJECT ID (OBJECT_VALUE.extract('/ROW/EMPNO/text()').getnumberval()) AS
SELECT sys_XMLGen(emp_t(e.employee_id, e.last_name, e.job_id, e.manager_id,
e.hire_date, e.salary, e.commission_pct))
FROM employees e;
A major advantage of non-schema-based views is that existing object views can be easily transformed into XMLType views without any additional DDL statements. For example, consider a database that contains the object view employee_ov with the following definition:
CREATE VIEW employee_ov OF emp_t
WITH OBJECT ID (empno) AS
SELECT emp_t(e.employee_id, e.last_name, e.job_id, e.manager_id,
e.hire_date, e.salary, e.commission_pct)
FROM employees e;
Creating a non-schema-based XMLType view can be achieved by simply calling sys_XMLGen over the top-level object column. No additional types need to be created.
CREATE OR REPLACE VIEW employee_ov_xml OF XMLType
WITH OBJECT ID (OBJECT_VALUE.extract('/ROW/EMPNO/text()').getnumberval()) AS
SELECT sys_XMLGen(OBJECT_VALUE) FROM employee_ov;
Queries on sys_XMLGen views are rewritten to access the object attributes directly if they meet certain conditions. Simple XPath traversals with SQL functions existsNode, extractValue, and extract are candidates for rewrite. See Chapter 6, "XPath Rewrite", for details on XPath rewrite. For example, a query such as the following:
SELECT extract(OBJECT_VALUE, '/ROW/EMPNO') FROM employee_ov_xml WHERE extractValue(OBJECT_VALUE, '/ROW/ENAME') = 'Smith';
This query is rewritten to something like the following:
SELECT sys_XMLGen(e.employee_id) FROM employees e WHERE e.last_name = 'Smith';
Example 18-24 illustrates XPath rewrite on an XML-schema-based XMLType view using an object type.
Example 18-24 XML-Schema-Based Views Constructed Using Object Types
This example uses the same object types andXML schema (emp_complex.xsd) as in section "Creating Schema-Based XMLType Views Over Object Views".
CREATE VIEW xmlv_adts OF XMLType
XMLSchema "http://www.oracle.com/emp_complex.xsd" ELEMENT "Employee"
WITH OBJECT OID (
OBJECT_VALUE.extract(
'/Employee/EmployeeId/text()').getNumberVal()) AS
SELECT emp_t(e.employee_id, e.last_name, e.job_id, e.manager_id,
e.hire_date, e.salary, e.commission_pct,
dept_t(d.department_id, d.department_name, d.location_id))
FROM employees e, departments d
WHERE e.department_id = d.department_id;
A query using SQL function extractValue:
SELECT extractValue(OBJECT_VALUE, '/Employee/EMPNO') "EmpID ",
extractValue(OBJECT_VALUE, '/Employee/ENAME') "Ename ",
extractValue(OBJECT_VALUE, '/Employee/JOB') "Job ",
extractValue(OBJECT_VALUE, '/Employee/MGR') "Manager ",
extractValue(OBJECT_VALUE, '/Employee/HIREDATE') "HireDate ",
extractValue(OBJECT_VALUE, '/Employee/SAL') "Salary ",
extractValue(OBJECT_VALUE, '/Employee/COMM') "Commission ",
extractValue(OBJECT_VALUE, '/Employee/DEPT/DEPTNO') "Deptno ",
extractValue(OBJECT_VALUE, '/Employee/DEPT/DNAME') "Deptname ",
extractValue(OBJECT_VALUE, '/Employee/DEPT/LOC') "Location "
FROM xmlv_adts
WHERE existsNode(OBJECT_VALUE, '/Employee[SAL > 15000]') = 1;
This query becomes:
SELECT e.employee_id "EmpID ", e.last_name "Ename ", e.job_id "Job ",
e.manager_id "Manager ", e.hire_date "HireDate ", e.salary "Salary ",
e.commission_pct "Commission ", d.department_id "Deptno ",
d.department_name "Deptname ", d.location_id "Location "
FROM employees e, departments d
WHERE e.department_id = d.department_id AND e.salary > 15000;
You can disable XPath rewrite for views constructed using a SQL/XML function by using the following event flag:
ALTER SESSION SET EVENTS '19027 trace name context forever, level 64';
You can disable XPath rewrite for view constructed using object types, object views, and SQL function sys_XMLGen by using the following event flag:
ALTER SESSION SET EVENTS '19027 trace name context forever, level 1';
You can trace why XPath rewrite does not happen by using the following event flag. The trace message is printed in the tracefile.
ALTER SESSION SET EVENTS '19027 trace name context forever, level 8192';
In the preceding examples, the CREATE VIEW statement specified the XML schema URL and element name, whereas the underlying view query simply constructed a non-schema-based XMLType. However, there are several scenarios where you may want to avoid the CREATE VIEW step, but still must construct XML schema-based XML.
To achieve this, you can use the following XML-generation SQL functions to optionally accept an XML schema URL and element name:
createXML
sys_XMLGen
sys_XMLAgg
Example 18-25 Generating XML Schema-Based XML Without Creating Views
This example uses the same type and XML schema definitions as in section "Wrapping Relational Department Data with Nested Employee Data as XML". With those definitions, createXML creates XML that is XML schema-based.
SELECT (XMLTYPE.createXML(
dept_t(d.department_id, d.department_name, d.location_id,
CAST(MULTISET(SELECT emp_t(e.employee_id, e.last_name, e.job_id,
e.manager_id, e.hire_date, e.salary,
e.commission_pct)
FROM employees e
WHERE e.department_id = d.department_id)
AS emplist_t)),
'http://www.oracle.com/dept_complex.xsd', 'Department'))
FROM departments d;
As XMLType has an automatic constructor, XMLTYPE.createXML could in fact be replaced by just XMLTYPE here.
|
 Copyright © 2002, 2005, Oracle. All rights reserved. Legal Notices |
|