Perl Cd Bookshelf [Electronic resources] نسخه متنی

Creating XMLType Views

An XMLType view is an efficient way of encapsulating existing relational or object-relational data in XML. In Oracle Database 10g, you can use any SQL or PL/SQL functions that generate XMLTypes to create an XMLType view. There are two different ways of creating XMLType views—as columns or rows.

You can create a view where the XMLType is a single column, as shown in the following example using a SQL/XML statement:

CREATE OR REPLACE VIEW employee_vw AS
SELECT XMLELEMENT("Employee",
XMLATTRIBUTES(first_name||' '||last_name AS "name"),
XMLFOREST(salary, phone_number))AS result,employee_id
FROM employees;

When you run describe employee_vw, you will see the following definition for the view:

SQL> describe employee_vw;
Name Null? Type
----------------------------------------- -------- --
RESULT SYS.XMLTYPE
EMPLOYEE_ID NOT NULL NUMBER(6)

The other way is to create an object XMLType view, where each row object is an XMLType instance and is associated with a unique OBJECT ID (OID). The resulting SYS_NC_ROWINFO$ is a virtual column referring to the XMLType instance in the XMLType object view or table:

CREATE OR REPLACE VIEW employee_vw OF XMLTYPE WITH OBJECT ID
(EXTRACT(SYS_NC_ROWINFO$,'/Employee/@empno').getNumberVal())
AS 
SELECT XMLELEMENT("Employee",
XMLATTRIBUTES(e.employee_id AS "empno"),
XMLFOREST(e.first_name, e.last_name, e.job_id))AS result
FROM employees e;

When creating XMLType object views, you need to specify the OBJECT ID via the OF XMLType WITH OBJECT ID syntax and assign the OBJECT ID by extracting a scalar value out of the XMLType. You can use the DEFAULT keyword to ask the database to create a default OBJECT ID. However, this is not recommended, because it will generate a 16-byte ID that is difficult to use when referring to the data in the view.

After you have created either view, you can query it as you would a relational table with XMLType columns or an XMLType object table:

SELECT * FROM employee_vw WHERE ROWNUM<2;

To optimize the queries on the XMLType object views to be able to use “query rewrite”, you can associate an XML schema with it through the following SQL statement:

CREATE OR REPLACE VIEW employee_vw OF XMLTYPE
XMLSCHEMA 
"http://xmlns.oracle.com/xml/employee.xsd" ELEMENT "Employee"
WITH OBJECT ID
(EXTRACT(SYS_NC_ROWINFO$,'/Employee/@empno').getNumberVal())
AS
SELECT XMLELEMENT("Employee",
XMLATTRIBUTES(e.employee_id AS "empno"),
XMLFOREST(e.first_name, e.last_name, e.job_id))AS result
FROM employees e;

The advantage of using XMLType views is that you can have several views representing differing XML hierarchies with single data storage.