1 / 18

Creating XMLType Views

Creating XMLType Views. Objectives. After completing this lesson, you should be able to do the following: Create non-schema-based XMLType views Create XML Schema-based XMLType views Create XMLType views from XMLType tables Perform DML on XMLType views. What Are XMLType Views?.

noleta
Télécharger la présentation

Creating XMLType Views

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Creating XMLType Views

  2. Objectives • After completing this lesson, you should be able to do the following: • Create non-schema-based XMLType views • Create XML Schema-based XMLType views • Create XMLType views from XMLType tables • Perform DML on XMLType views

  3. What Are XMLType Views? • An XMLType view: • Wraps existing relational and object-relational data in XML formats • Shows data as a collection of XMLType instances • Can be one of the two types: • Non-schema-based • XML Schema-based Relationaltable XML XMLTypeobjects Objecttable XMLTypeview

  4. Creating XMLType Views • An XMLType view may be created by using: • SQL/XML operators • Object types Objecttypes XMLTypeview SQL/XMLoperators Objecttables Objectconstructors Objectviews Relationaltables XMLSchema Relationaltables

  5. Creating Non-Schema-Based Views • Non-schema-based XMLType views are: • Not constrained by a registered XML Schema • Created in two ways: • Using SQL/XML functions • Using object types and views with SYS_XMLGEN() • Created by using the CREATE VIEW statement with the following syntax: • Note: Set the OBJECT_ID using: extract(OBJECT_VALUE,'...').getNumberVal() CREATE OR REPLACE VIEW view_name OF XMLType WITH OBJECT ID {DEFAULT|(expression)} AS sql_query;

  6. Non-Schema XMLType Views with SQL/XML Functions • Create the view: • Query the view: CREATE OR REPLACE VIEW emp_view OF XMLType WITH OBJECT ID (extract( OBJECT_VALUE,'/employee/@empno').getNumberVal()) AS SELECT XMLElement("employee", XMLAttributes(employee_id AS "empno"), XMLForest(e.last_name AS "last_name", e.hire_date AS "hiredate", e.salary as "salary")) AS "result" FROM employees e WHERE salary > 15000; SELECT * FROM emp_view;

  7. Non-Schema XMLType Views with Object Types and SYS_XMLGEN() • Create the object type: • Create the view by using SYS_XMLGEN(): CREATE TYPE employee_t AS OBJECT ( "@empno" NUMBER(6), name VARCHAR2(30), paycheck NUMBER(8,2)); / CREATE OR REPLACE VIEW empobj_view OF XMLType WITH OBJECT ID (EXTRACT( OBJECT_VALUE,'/employee/@empno').getnumberval()) AS SELECT SYS_XMLGEN( employee_t(e.employee_id, e.last_name, e.salary), XMLFormat('EMPLOYEE')) FROM employees e WHERE salary > 15000;

  8. Creating XML Schema-Based Views • XML Schema-based XMLType views: • Require the XML Schema to be registered first • Are constrained by the registered XML Schema • Are created by using the CREATE VIEW statement with the following syntax: • Identifying the XML Schema in the XMLSCHEMA clause • Specifying the document element in the ELEMENT clause CREATE OR REPLACE VIEW view_name OF XMLType XMLSCHEMA "url" ELEMENT "element-name" WITH OBJECT ID {DEFAULT|(expression)} AS sql_query;

  9. XML Schema XMLType Views with SQL/XML Functions • Register the XML Schema: BEGIN DBMS_XMLSCHEMA.registerSchema('region.xsd', '<xsd:schema version="1.0" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:element name="region"> <xsd:complexType> <xsd:sequence> <xsd:element name="region_id" type="xsd:int"/> <xsd:element name="region_name" type="xsd:string"/> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>', TRUE, TRUE, FALSE); END; /

  10. XML Schema XMLType Views with SQL/XML Functions • Create the XML-Schema-based XMLType view: • Note: The noNamespaceSchemaLocation attribute CREATE OR REPLACE VIEW region_xmlv OF XMLType XMLSCHEMA "region.xsd" ELEMENT "region" WITH OBJECT ID (extract(OBJECT_VALUE, '/region/region_id').getNumberVal()) AS SELECT XMLElement("region", XMLAttributes( 'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi", 'region.xsd' AS "xsi:noNamespaceSchemaLocation"), XMLForest(region_id AS "region_id", region_name AS "region_name")) FROM regions;

  11. Using Namespaces with SQL/XML Functions • When the XML Schema defines targetNamespace, use partially escaped mapping in SQL/XML functions to create elements with namespaces and prefixes. CREATE OR REPLACE VIEW region_ns_xmlv OF XMLType XMLSCHEMA "region_ns.xsd" ELEMENT "region" WITH OBJECT ID (extract(OBJECT_VALUE, '/region/region_id').getNumberVal()) AS SELECT XMLElement("rgn:region", XMLAttributes( 'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi", 'http://www.hr.com/regions' AS "xmlns:rgn", 'http://www.hr.com/regions region_ns.xsd' AS "xsi:schemaLocation"), XMLForest(region_id AS "rgn:region_id", region_name AS "rgn:region_name")) FROM regions;

  12. XML Schema XMLType Views with Object Types • The steps to create an XMLType view by using object types are: 1. Create the object types. 2. Create or generate the XML Schema. 3. Register the XML Schema. 4. Create the XMLType view by using one of the following: a. One-step processUse a query on the relational tables with its columns in the object constructor. b. Two-step process:Create an object view.Create an XMLType view on the object view.

  13. Creating XMLType Views from XMLType Tables • To create an XMLType view on an XMLType table: • Create and populate the XMLType table: • Create the XMLType view: CREATE TABLE region_xmltab OF XMLType XMLSchema "region.xsd" ELEMENT "region"; INSERT INTO region_xmltab VALUES (XMLType( '<region xmlns:xsi= "http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="region.xsd"> <region_id>5</region_id> <region_name>Japan</region_name> </region>')); CREATE OR REPLACE VIEW region_xmltabv OF XMLType XMLSchema "region.xsd" ELEMENT "region" AS SELECT value(r) FROM region_xmltab r;

  14. DML with XMLType Views • An XMLType view: • Is updateable if its underlying objects are inherently updateable • Is not directly updateable if the underlying objects are not directly updateable, and requires DML operations to be performed in INSTEAD OF triggers • Note: The steps to create an updateable XMLType view are similar to creating an XMLType view with SQL object types.

  15. Example of an Updateable XMLType View 1. Create the SQL object type: 2. Generate and register the XML Schema: CREATE TYPE region_t AS OBJECT( "id" NUMBER, "name" VARCHAR2(25)); / BEGIN DBMS_XMLSCHEMA.registerSchema( 'http://www.hr.com/region_t.xsd', DBMS_XMLSCHEMA.generateSchema( USER,'REGION_T','region'), TRUE, FALSE, FALSE); END; /

  16. Example of an Updateable XMLType View 3. Create the XMLType View 4. Perform the DML operation CREATE OR REPLACE VIEW region_upd_xmlv OF XMLType XMLSCHEMA "http://www.hr.com/region_t.xsd" ELEMENT "region" WITH OBJECT ID (object_value.extract( '/region/id').getNumberVal()) AS SELECT region_t(r.region_id, r.region_name) FROM regions r; INSERT INTO region_upd_xmlv VALUES (XMLType( '<region xmlns:xsi= "http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation= "http://www.hr.com/region_t.xsd"> <id>5</id> <name>Pacific</name> </region>'));

  17. Summary • In this lesson, you should have learned how to: • Create XMLType views without an XML Schema for validation by using SQL/XML functions, or SYS_XMLGEN() with objects • Create XML Schema-based XMLType views based on SQL/XML functions, or object tables and views • Create XMLType Views from XMLType tables for restricting or transforming XMLType data • Perform DML on XMLType views

  18. Practice 12: Overview • This practice covers the following topics: • Creating a non-schema-based XMLType view of employees by using SQL/XML functions • Creating an updateable XML Schema-based XMLType view based on an object type by using the department data

More Related