| Oracle® XML DB Developer's Guide 11g Release 2 (11.2) Part Number E10492-02 |
|
|
View PDF |
| Oracle® XML DB Developer's Guide 11g Release 2 (11.2) Part Number E10492-02 |
|
|
View PDF |
This chapter describes how to create and use XMLType views. It 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, CLOB, and binary XML storage available for XMLType tables.
XMLType views are similar to object views. Each row of an XMLType view corresponds to an XMLType instance. The object identifier for uniquely identifying each row in the view can be created using SQL/XML functions XMLCast and XMLQuery.
Throughout this chapter XML schema refers to the W3C XML Schema 1.0 recommendation, http://www.w3.org/XML/Schema.
There are two types of XMLType views:
Non-schema-based XMLType views. These views do not confirm to a particular XML schema.
XML schema-based XMLType views. As with XMLType tables, XMLType views that conform to a particular XML schema are called XML schema-based XMLType views. These provide stronger typing than non-schema-based XMLType views.
XPath rewrite of queries over XMLType views is enabled for both XML schema-based and non-schema-based XMLType views. XPath rewrite is described in Chapter 8, "XPath Rewrite for Structured Storage".
To create an XML schema-based XMLType view, first register your XML schema. If the view is an object view, that is, if it is constructed using an object type, then the XML schema should have annotations that represent the bidirectional mapping from XML to SQL object types. XMLType views conforming to this registered XML schema can then be created by providing an underlying query that constructs instances of the appropriate SQL object type.
See Also:
You can create XMLType views in any of the following ways:
Based on SQL/XML publishing functions, such as XMLElement, XMLForest, XMLConcat, and XMLAgg. SQL/XML publishing functions can be used to construct both non-schema-based XMLType views and XML schema-based XMLType views. This enables construction of XMLType view from the underlying relational tables directly without physically migrating those relational legacy data into XML. However, to construct XML schema-based XMLType view, the XML schema must be registered and the XML value generated by SQL/XML publishing functions must be constrained to the XML schema.
Based on object types, object views, and Oracle SQL function sys_XMLGen. Non-schema-based XMLType views can be constructed using object types, object views, and function sys_XMLGen and XML schema-based XMLType view can be constructed using object types and object views. This enables the construction of the XMLType view from underlying relational or object relational tables directly without physically migrating the relational or object relational legacy data into XML. Creating non-schema-based XMLType view requires the use of sys_XMLGen over existing object types or object views. Creating XML-schema-based XMLType view requires to annotate the XML schema with a mapping to existing object types or to generate the XML schema from the existing object types.
Directly from an XMLType table.
Figure 19-1 shows the CREATE VIEW clause for creating XMLType views. See Oracle Database SQL Language Reference for details on the CREATE VIEW syntax.
Figure 19-1 Creating XMLType Views Clause: Syntax
Non-schema-based XMLType views are XMLType views whose resultant XML value is not constrained to be a particular element in a registered XML schema. You can create a non-schema-based XMLType view in either of these ways:
Using SQL/XML publishing functions.
|
See Also: Chapter 18, "Generating XML Data from the Database", for details on SQL/XML publishing functions |
Using object types or object views, together with Oracle SQL function sys_XMLGen. This is convenient when you already have object types, views, and tables that you want to map to XML data.
Example 19-1 shows how to create an XMLType view using SQL/XML function XMLELement.
Example 19-1 Creating an XMLType View using XMLELEMENT
CREATE OR REPLACE VIEW emp_view OF XMLType
WITH OBJECT ID (XMLCast(XMLQuery('/Emp/@empno'
PASSING OBJECT_VALUE RETURNING CONTENT)
AS BINARY_DOUBLE)) AS
SELECT XMLElement("Emp",
XMLAttributes(employee_id AS "empno"),
XMLForest(e.first_name ||' '|| e.last_name AS "name",
e.hire_date AS "hiredate"))
AS "result" FROM employees e WHERE salary > 15000;
SELECT * FROM emp_view;
SYS_NC_ROWINFO$
-------------------------------------------------------------------------------------
<Emp empno="100"><name>Steven King</name><hiredate>2003-06-17</hiredate></Emp>
<Emp empno="101"><name>Neena Kochhar</name><hiredate>2005-09-21</hiredate></Emp>
<Emp empno="102"><name>Lex De Haan</name><hiredate>2001-01-13</hiredate></Emp>
Existing data in relational tables or views can be exposed as XML this way. If a view is generated using a SQL/XML publishing function, then queries that access that view using XPath expressions can often be rewritten. These optimized queries can then directly access the underlying relational columns. See Chapter 8, "XPath Rewrite for Structured Storage" for details.
You can perform DML operations on these XMLType views, but, in general, you must write instead-of triggers to handle the DML operation.
You can also create an XMLType view using object types and Oracle SQL function sys_XMLGen. Function sys_XMLGen accepts as argument an instance of an object type, and it generates a corresponding instance of XMLType. The query in Example 19-2 uses sys_XMLGen and produces the same result as the query of Example 19-1.
Example 19-2 Creating an XMLType View using Object Types and SYS_XMLGEN
CREATE TYPE emp_t AS OBJECT ("@empno" NUMBER(6),
fname VARCHAR2(20),
lname VARCHAR2(25),
hiredate DATE);
/
CREATE OR REPLACE VIEW employee_view OF XMLType
WITH OBJECT ID (XMLCast(XMLQuery('/Emp/@empno'
PASSING OBJECT_VALUE RETURNING CONTENT)
AS BINARY_DOUBLE)) AS
SELECT sys_XMLGen(emp_t(e.employee_id, e.first_name, e.last_name, e.hire_date),
XMLFormat('EMP'))
FROM employees e WHERE salary > 15000;
SELECT * FROM employee_view;
SYS_NC_ROWINFO$
--------------------------------------------------------
<?xml version="1.0"?
<EMP empno="100">
<FNAME>Steven</FNAME>
<LNAME>King</LNAME>
<HIREDATE>17-JUN-03</HIREDATE>
</EMP>
<?xml version="1.0"?>
<EMP empno="101">
<FNAME>Neena</FNAME>
<LNAME>Kochhar</LNAME>
<HIREDATE>21-SEP-05</HIREDATE>
</EMP>
<?xml version="1.0"?>
<EMP empno="102">
<FNAME>Lex</FNAME>
<LNAME>De Haan</LNAME>
<HIREDATE>13-JAN-01</HIREDATE>
</EMP>
Existing relational or object-relational data can be exposed as XML data using this mechanism.
XML schema-based XMLType views are views whose data is constrained to conform to an XML schema. You can create an XML schema-based XMLType view in either of these ways:
Using SQL/XML publishing functions.
Using object types or object views. This is convenient when you already have object types, views, and tables that you want to map to XML data.
You can use SQL/XML publishing functions to create XML schema-based XMLType views in a similar way as for the non-schema-based case described in section "Creating Non-Schema-Based XMLType Views":
Create and register the XML schema document that contains the necessary XML structures. You do not need to annotate the XML schema to define the mapping between XML types and SQL object types.
Use SQL/XML publishing functions to create an XMLType view that conforms to the XML schema.
These two steps are illustrated in Example 19-3 and Example 19-4, respectively.
Example 19-3 Registering XML Schema emp_simple.xsd
BEGIN
DBMS_XMLSCHEMA.registerSchema('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;
Example 19-3 assumes that you have an XML schema emp_simple.xsd that contains XML structures defining an employee. It registers the XML schema with the target location http://www.oracle.com/emp_simple.xsd.
When using SQL/XML publishing functions to generate XML schema-based content, you must specify the appropriate namespace information for all of the elements and also indicate the location of the schema using attribute xsi:schemaLocation. These can be specified using the XMLAttributes clause. Example 19-4 illustrates this.
Example 19-4 Creating an XMLType View using SQL/XML Publishing Functions
CREATE OR REPLACE VIEW emp_simple_xml OF XMLType XMLSCHEMA "http://www.oracle.com/emp_simple.xsd" ELEMENT "Employee" WITH OBJECT ID (XMLCast(XMLQuery('/Employee/EmployeeId/text()' PASSING OBJECT_VALUE RETURNING CONTENT) AS BINARY_DOUBLE)) AS SELECT XMLElement("Employee", XMLAttributes( 'http://www.oracle.com/emp_simple.xsd' AS "xmlns" , 'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi", 'http://www.oracle.com/emp_simple.xsd http://www.oracle.com/emp_simple.xsd' AS "xsi:schemaLocation"), XMLForest(e.employee_id AS "EmployeeId", e.last_name AS "Name", e.job_id AS "Job", e.manager_id AS "Manager", e.hire_date AS "HireDate", e.salary AS "Salary", e.commission_pct AS "Commission", XMLForest( d.department_id AS "DeptNo", d.department_name AS "DeptName", d.location_id AS "Location") AS "Dept")) FROM employees e, departments d WHERE e.department_id = d.department_id;
In Example 19-4, function XMLElement creates XML element Employee. Function XMLForest creates the children of element Employee. The XMLAttributes clause inside XMLElement constructs the required XML namespace and schema location attributes, so that the XML data that is generated conforms to the XML schema of the view. The innermost call to XMLForest creates the children of element department, which is a child of element Employee.
By default, the XML generation functions create a non-schema-based XML instance. However, when the schema location is specified, using attribute xsi:schemaLocation or xsi:noNamespaceSchemaLocation, Oracle XML DB generates XML schema-based XML data. For XMLType views, as long as the names of the elements and attributes match those in the XML schema, the XML data is converted implicitly into a valid XML schema-based document. Any errors in the generated XML data are caught later, when operations such as validation or extraction operations are performed on the XML instance.
Example 19-5 queries the XMLType view, returning an XML result from tables employees and departments. The result of the query is shown pretty-printed, for clarity.
Example 19-5 Querying an XMLType View
SELECT OBJECT_VALUE AS RESULT FROM emp_simple_xml WHERE ROWNUM < 2;
RESULT
---------------------------------------------------------------------
<Employee xmlns="http://www.oracle.com/emp_simple.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.oracle.com/emp_simple.xsd
http://www.oracle.com/emp_simple.xsd">
<EmployeeId>200</EmployeeId>
<Name>Whalen</Name>
<Job>AD_ASST</Job>
<Manager>101</Manager>
<HireDate>2003-09-17</HireDate>
<Salary>4400</Salary>
<Dept>
<DeptNo>10</Deptno>
<DeptName>Administration</DeptName>
<Location>1700</Location>
</Dept>
</Employee>
If you have complex XML schemas involving namespaces, you must use the partially escaped mapping provided by the SQL/XML publishing functions and create elements with appropriate namespaces and prefixes.
The query in Example 19-6 creates XML instances that have the correct namespace, prefixes, and target schema location. It can be used as the query in the definition of view emp_simple_xml.
Example 19-6 Using Namespace Prefixes with SQL/XML Publishing Functions
SELECT XMLElement("ipo:Employee",
XMLAttributes('http://www.oracle.com/emp_simple.xsd' AS "xmlns:ipo",
'http://www.oracle.com/emp_simple.xsd
http://www.oracle.com/emp_simple.xsd' AS "xmlns:xsi"),
XMLForest(e.employee_id AS "ipo:EmployeeId",
e.last_name AS "ipo:Name",
e.job_id AS "ipo:Job",
e.manager_id AS "ipo:Manager",
TO_CHAR(e.hire_date,'YYYY-MM-DD') AS "ipo:HireDate",
e.salary AS "ipo:Salary",
e.commission_pct AS "ipo:Commission",
XMLForest(d.department_id AS "ipo:DeptNo",
d.department_name AS "ipo:DeptName", d.location_id
AS "ipo:Location") AS "ipo:Dept"))
FROM employees e, departments d
WHERE e.department_id = d.department_id AND d.department_id = 20;
BEGIN
-- Delete schema if it already exists (else error)
DBMS_XMLSCHEMA.deleteSchema('emp-noname.xsd', 4);
END;
XMLELEMENT("IPO:EMPLOYEE",XMLATTRIBUTES('HTTP://WWW.ORACLE.COM/EMP_SIMPLE.XSD'AS
--------------------------------------------------------------------------------
<ipo:Employee
xmlns:ipo="http://www.oracle.com/emp_simple.xsd"
xmlns:xsi="http://www.oracle.com/emp_simple.xsd
http://www.oracle.com/emp_simple.xsd">
<ipo:EmployeeId>201</ipo:EmployeeId><ipo:Name>Hartstein</ipo:Name>
<ipo:Job>MK_MAN</ipo:Job><ipo:Manager>100</ipo:Manager>
<ipo:HireDate>2004-02-17</ipo:HireDate><ipo:Salary>13000</ipo:Salary>
<ipo:Dept><ipo:DeptNo>20</ipo:DeptNo><ipo:DeptName>Marketing</ipo:DeptName>
<ipo:Location>1800</ipo:Location></ipo:Dept></ipo:Employee>
<ipo:Employee xmlns:ipo="http://www.oracle.com/emp_simple.xsd"
xmlns:xsi="http://www.oracle.com/emp_simple.xsd
http://www.oracle.com/emp_simple.xsd"><ipo:EmployeeId>202</ipo:EmployeeId>
<ipo:Name>Fay</ipo:Name><ipo:Job>MK_REP</ipo:Job><ipo:Manager>201</ipo:Manager>
<ipo:HireDate>2005-08-17</ipo:HireDate><ipo:Salary>6000</ipo:Salary>
<ipo:Dept><ipo:DeptNo>20</ipo:Dept
No><ipo:DeptName>Marketing</ipo:DeptName><ipo:Location>1800</ipo:Location>
</ipo:Dept>
</ipo:Employee>
If the XML schema had no target namespace, then you could use attribute xsi:noNamespaceSchemaLocation to indicate that. Example 19-7 shows such an XML schema.
Example 19-7 XML Schema with No Target Namespace
BEGIN
DBMS_XMLSCHEMA.registerSchema(
'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;
Example 19-8 creates a view that conforms to the XML schema in Example 19-7. The XMLAttributes clause creates an XML element that contains the noNamespace schema location attribute.
Example 19-8 Creating a View for an XML Schema with No Target Namespace
CREATE OR REPLACE VIEW emp_xml OF XMLType XMLSCHEMA "emp-noname.xsd" ELEMENT "Employee" WITH OBJECT ID (XMLCast(XMLQuery('/Employee/EmployeeId/text()' PASSING OBJECT_VALUE RETURNING CONTENT) AS BINARY_DOUBLE)) AS SELECT XMLElement( "Employee", XMLAttributes('http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi", 'emp-noname.xsd' AS "xsi:noNamespaceSchemaLocation"), XMLForest(e.employee_id AS "EmployeeId", e.last_name AS "Name", e.job_id AS "Job", e.manager_id AS "Manager", e.hire_date AS "HireDate", e.salary AS "Salary", e.commission_pct AS "Commission", XMLForest(d.department_id AS "DeptNo", d.department_name AS "DeptName", d.location_id AS "Location") AS "Dept")) FROM employees e, departments d WHERE e.department_id = d.department_id;
Example 19-9 creates view dept_xml, which conforms to XML schema dept.xsd.
Example 19-9 Using SQL/XML Publishing 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 (XMLCast(XMLQuery('/Department/DeptNo'
PASSING OBJECT_VALUE RETURNING CONTENT)
AS BINARY_DOUBLE)) AS
SELECT XMLElement(
"Department",
XMLAttributes(
'http://www.oracle.com/emp.xsd' AS "xmlns" ,
'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi",
'http://www.oracle.com/dept.xsd
http://www.oracle.com/dept.xsd' AS "xsi:schemaLocation"),
XMLForest(d.department_id "DeptNo",
d.department_name "DeptName",
d.location_id "Location"),
(SELECT XMLagg(
XMLElement("Employee",
XMLForest(
e.employee_id "EmployeeId",
e.last_name "Name",
e.job_id "Job",
e.manager_id "Manager",
to_char(e.hire_date,'YYYY-MM-DD') "Hiredate",
e.salary "Salary",
e.commission_pct "Commission")))
FROM employees e
WHERE e.department_id = d.department_id))
FROM departments d;
This is the XMLType instance that results:
SELECT OBJECT_VALUE AS result FROM dept_xml WHERE ROWNUM < 2;
RESULT
----------------------------------------------------------------
<Department
xmlns="http://www.oracle.com/emp.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.oracle.com/dept.xsd
http://www.oracle.com/dept.xsd">
<DeptNo>10</DeptNo>
<DeptName>Administration</DeptName>
<Location>1700</Location>
<Employee>
<EmployeeId>200</EmployeeId>
<Name>Whalen</Name>
<Job>AD_ASST</Job>
<Manager>101</Manager>
<Hiredate>2003-09-17</Hiredate>
<Salary>4400</Salary>
</Employee>
</Department>
To create an XML schema-based XMLType view from object types or object views, do the following:
Create the object types, if they do not yet exist.
Create and then register the XML schema, annotating it to define the mapping between XML types and SQL object types and attributes.
You can annotate the XML schema before registering it. You typically do this when you wrap existing data to create an XMLType view.
You can use PL/SQL functions DBMS_XMLSchema.generateSchema and DBMS_XMLSchema.generateSchemas to generate the default XML mapping for specified object types. The generated XML schema has the requisite annotations SQLType, SQLSchema, and so on. When such an XML schema document is registered, the following validation can occur:
SQLType for attributes or elements based on simpleType. The SQL type must be compatible with the XML type of the corresponding XMLType data. For example, an XML string data type can be mapped only to a VARCHAR2 or a Large Object (LOB) data type.
SQLType specified for elements based on complexType. This is either a LOB or an object type whose structure must be compatible with the declaration of the complexType, that is, the object type must have the correct number of attributes with the correct data types.
Create the XMLType view, specifying the XML schema URL and the root element name. The query defining the view first constructs the object instances and then converts them to XML.
Create an object view.
Create an XMLType view over the object view.
The following sections present examples of creating XML schema-based XMLType views using object types or object views. They are based on relational tables that contain employee and department data.
"Creating XMLType Employee View, with Nested Department Information"
"Creating XMLType Department View, with Nested Employee Information"
The same relational data is used to create each of two XMLType views. In the employee view, emp_xml, the XML document describes an employee, with the employee's department as nested information. In the department view, dept_xml, the XML data describes a department, with the department's employees as nested information.
This section describes how to create XMLType view emp_xml based on object views. For the last step, there are two alternatives:
"Step 3a. Create XMLType View emp_xml using Object Type emp_t" – create XMLType view emp_xml using object type emp_t
"Step 3b. Create XMLType View emp_xml using Object View emp_v" – create XMLType view emp_xml using object view emp_v
Example 19-10 creates the object types used in the other steps.
Example 19-10 Creating Object Types for Schema-Based XMLType Views
CREATE TYPE dept_t AS OBJECT
(deptno NUMBER(4),
dname VARCHAR2(30),
loc NUMBER(4));
/
CREATE TYPE emp_t AS OBJECT
(empno NUMBER(6),
ename VARCHAR2(25),
job VARCHAR2(10),
mgr NUMBER(6),
hiredate DATE,
sal NUMBER(8,2),
comm NUMBER(2,2),
dept dept_t);
/
You can create an XML schema manually, or you can use package DBMS_XMLSCHEMA to generate an XML schema automatically from existing object types, as shown in Example 19-11.
Example 19-11 Generating an XML Schema with DBMS_XMLSCHEMA.GENERATESCHEMA
SELECT DBMS_XMLSCHEMA.generateSchema('HR','EMP_T') AS result FROM DUAL;
Example 19-11 generates the XML schema for type emp_t. You can supply various arguments to PL/SQL function DBMS_XMLSCHEMA.generateSchemas, to add namespaces, and so on. You can also edit the XML schema to change the default mappings that are generated. Function generateSchemas generates a list of XML schemas, one for each SQL database schema that is referenced by the object type and its object attributes.
Example 19-12 shows how to register XML schema emp_complex.xsd, which specifies how XML elements and attributes are mapped to corresponding object attributes in the object types (the xdb:SQLType annotations).
Example 19-12 Registering XML Schema emp_complex.xsd
BEGIN
-- Delete schema if it already exists (else error)
DBMS_XMLSCHEMA.deleteSchema('http://www.oracle.com/emp_complex.xsd', 4);
END;
/
COMMIT;
BEGIN
DBMS_XMLSCHEMA.registerSchema(
'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;
/
Example 19-12 registers the XML schema using the target location http://www.oracle.com/emp_complex.xsd.
Example 19-13 creates an XMLType view using object type emp_t.
Example 19-13 Creating XMLType View emp_xml
CREATE OR REPLACE VIEW emp_xml OF XMLType
XMLSCHEMA "http://www.oracle.com/emp_complex.xsd"
ELEMENT "Employee"
WITH OBJECT ID (XMLCast(XMLQuery('/Employee/EMPNO'
PASSING OBJECT_VALUE RETURNING CONTENT)
AS BINARY_DOUBLE)) AS
SELECT emp_t(e.employee_id, e.last_name, e.job_id, e.manager_id, e.hire_date,
e.salary, e.commission_pct,
dept_t(d.department_id, d.department_name, d.location_id))
FROM employees e, departments d
WHERE e.department_id = d.department_id;
Example 19-13 uses SQL/XML function XMLCast in the OBJECT ID clause to convert the XML employee number to SQL data type BINARY_DOUBLE.
Example 19-14 creates an XMLType view based on an object view.
Example 19-14 Creating an Object View and an XMLType View on the Object View
CREATE OR REPLACE VIEW emp_v OF emp_t WITH OBJECT ID (empno) AS SELECT emp_t(e.employee_id, e.last_name, e.job_id, e.manager_id, e.hire_date, e.salary, e.commission_pct, dept_t(d.department_id, d.department_name, d.location_id)) FROM employees e, departments d WHERE e.department_id = d.department_id; CREATE OR REPLACE VIEW emp_xml OF XMLType XMLSCHEMA "http://www.oracle.com/emp_complex.xsd" ELEMENT "Employee" WITH OBJECT ID DEFAULT AS SELECT VALUE(p) FROM emp_v p;
This section describes how to create XMLType view dept_xml. Each department in this view contains nested employee information. For the last step, there are two alternatives:
"Step 3a. Create XMLType View dept_xml using Object Type dept_t" – create XMLType view dept_xml using the object type for a department, dept_t
"Step 3b. Create XMLType View dept_xml using Relational Data Directly" – create XMLType view dept_xml using relational data directly
Example 19-15 creates the object types used in the other steps.
Example 19-15 Creating Object Types
CREATE TYPE emp_t AS OBJECT (empno NUMBER(6),
ename VARCHAR2(25),
job VARCHAR2(10),
mgr NUMBER(6),
hiredate DATE,
sal NUMBER(8,2),
comm NUMBER(2,2)); /
CREATE OR REPLACE TYPE emplist_t AS TABLE OF emp_t;
/
CREATE TYPE dept_t AS OBJECT (deptno NUMBER(4),
dname VARCHAR2(30),
loc NUMBER(4),
emps emplist_t);
/
You can either use a pre-existing XML schema or generate an XML schema from the object type with function DBMS_XMLSCHEMA.generateSchema or DBMS_XMLSCHEMA.generateSchemas (see Example 19-11). Example 19-16 registers the XML schema dept_complex.xsd.
Example 19-16 Registering XML Schema dept_complex.xsd
BEGIN
-- Delete schema if it already exists (else error)
DBMS_XMLSCHEMA.deleteSchema('http://www.oracle.com/dept_complex.xsd', 4);
END;
/
BEGIN
DBMS_XMLSCHEMA.registerSchema('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;
/
Example 19-17 creates XMLType view dept_xml using object type dept_t.
Example 19-17 Creating XMLType View dept_xml using Object Type dept_t
CREATE OR REPLACE VIEW dept_xml OF XMLType
XMLSCHEMA "http://www.oracle.com/dept_complex.xsd" ELEMENT "Department"
WITH OBJECT ID (XMLCast(XMLQuery('/Department/DEPTNO'
PASSING OBJECT_VALUE RETURNING CONTENT)
AS BINARY_DOUBLE)) AS
SELECT dept_t(d.department_id, d.department_name, d.location_id,
cast(MULTISET
(SELECT emp_t(e.employee_id, e.last_name, e.job_id,
e.manager_id, e.hire_date,
e.salary, e.commission_pct)
FROM employees e WHERE e.department_id = d.department_id)
AS emplist_t))
FROM departments d;
Alternatively, you can use SQL/XML publishing functions to create XMLType view dept_xml from the relational tables without using object type dept_t. Example 19-18 illustrates this.
Example 19-18 Creating XMLType View dept_xml using Relational Data Directly
CREATE OR REPLACE VIEW dept_xml OF XMLType
XMLSCHEMA "http://www.oracle.com/dept_complex.xsd" ELEMENT "Department"
WITH OBJECT ID (XMLCast(XMLQuery('/Department/DEPTNO'
PASSING OBJECT_VALUE RETURNING CONTENT)
AS BINARY_DOUBLE)) AS
SELECT
XMLElement(
"Department",
XMLAttributes('http://www.oracle.com/dept_complex.xsd' AS "xmlns",
'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi",
'http://www.oracle.com/dept_complex.xsd
http://www.oracle.com/dept_complex.xsd'
AS "xsi:schemaLocation"),
XMLForest(d.department_id "DeptNo", d.department_name "DeptName",
d.location_id "Location"),
(SELECT XMLAgg(XMLElement("Employee",
XMLForest(e.employee_id "EmployeeId",
e.last_name "Name",
e.job_id "Job",
e.manager_id "Manager",
e.hire_date "Hiredate",
e.salary "Salary",
e.commission_pct "Commission")))
FROM employees e WHERE e.department_id = d.department_id))
FROM departments d;
Note:
XML schema and element information must be specified at the view level, because 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 data or to restrict the rows returned.
Example 19-19 creates an XMLType view by restricting the rows included from an underlying XMLType table. It uses XML schema dept_complex.xsd to create the underlying table — see "Creating XMLType Department View, with Nested Employee Information".
Example 19-19 Creating an XMLType View by Restricting Rows from an XMLType Table
CREATE TABLE dept_xml_tab OF XMLType
XMLSchema "http://www.oracle.com/dept_complex.xsd" ELEMENT "Department"
NESTED TABLE XMLDATA."EMPS" STORE AS dept_xml_tab_tab1;
CREATE OR REPLACE VIEW dallas_dept_view OF XMLType
XMLSchema "http://www.oracle.com/dept.xsd" ELEMENT "Department"
AS SELECT OBJECT_VALUE FROM dept_xml_tab
WHERE XMLCast(XMLQuery('/Department/LOC'
PASSING OBJECT_VALUE RETURNING CONTENT)
AS VARCHAR2(20))
= 'DALLAS';
CREATE OR REPLACE VIEW dallas_dept_view OF XMLType
Here, dallas_dept_view restricts the XMLType table rows to those departments whose location is Dallas.
Example 19-20 shows how you can create an XMLType view by transforming XML data using a style sheet.
You can reference an XMLType view object using SQL function ref:
SELECT ref(d) FROM dept_xml_tab d;
An XMLType view reference is based on one of the following object IDs:
System-generated OID — for views on XMLType tables or object views
Primary key based OID -- for views with OBJECT ID expressions
These REFs can be used to fetch OCIXMLType instances in the OCI Object cache, or they can be used in SQL queries. These REFs act the same as REFs to object views.
An XMLType view may not be inherently updatable. This means that you must 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.
One way to identify when an XMLType view is implicitly updatable is to query an XMLType view to see whether the view is based on an object view or an object constructor that is itself inherently updatable. Example 19-21 illustrates this.
Example 19-21 Identifying When a View is Implicitly Updatable
CREATE TYPE dept_t AS OBJECT
(deptno NUMBER(4),
dname VARCHAR2(30),
loc NUMBER(4));
/
BEGIN
-- Delete schema if it already exists (else error)
DBMS_XMLSCHEMA.deleteSchema('http://www.oracle.com/dept.xsd', 4);
END;
/
COMMIT;
BEGIN
DBMS_XMLSCHEMA.registerSchema('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 (XMLCast(XMLQuery('/Department/DEPTNO'
PASSING OBJECT_VALUE RETURNING CONTENT)
AS BINARY_DOUBLE)) AS
SELECT dept_t(d.department_id, d.department_name, d.location_id)
FROM departments d;
INSERT INTO dept_xml
VALUES (
XMLType.createXML(
'<Department
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://www.oracle.com/dept_t.xsd" >
<DEPTNO>300</DEPTNO>
<DNAME>Processing</DNAME>
<LOC>1700</LOC>
</Department>'));
UPDATE dept_xml d
SET d.OBJECT_VALUE = updateXML(d.OBJECT_VALUE, '/Department/DNAME/text()',
'Shipping')
WHERE XMLExists('/Department[DEPTNO=300]' PASSING OBJECT_VALUE);
|
 Copyright © 2002, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|