Oracle XDB, Release 9iR2: Publishing XML and XMLType Views

1
(No Transcript)
2
Oracle XDB, Release 9iR2 Publishing XML and
XMLType Views
Prepared for IT620 January 27 , 2003
3
Talk Outline

• Background
• Generating XML using DBMS_XMLGEN
• Generating XML using SQLX functions
• Generating XML using Oracle SQL functions
• XMLType Views
• Pointers to More Information

4
Background

5
What we covered in Publishing Relational Data in
XML

• 1 Fernandez et. al.  SilkRoute A Framework for
  Publishing Relational Data in XML, ACM TODS

6
XML Generation Overview
SQLX Functions XMLElement XMLForest XMLColAttrVal
XMLConcat XMLAgg
PL/SQL Package DBMS_XMLGEN
SQL Functions SYS_XMLGEN SYS_XMLAGG XMLSequence
Generated XML XMLType instance XML as DOM XML as
String Varray of XMLType Sequences Forest of XML
Elements
7
Oracle XDB Generating XML Using DBMS_XMLGEN
package

8
DBMS_XMLGEN

• Can create an XML document instance from any SQL
  query and get the document as a CLOB or XMLType.
• Can use a fetch interface with maximum rows and
  rows to skip. For example, the first fetch could
  retrieve a maximum of 10 rows, skipping the first
  four. This is useful for pagination in Web-based
  applications.

9
DBMS_XMLGEN

• Every row of the query result maps to an XML
  element with the default tag name ROW.
• The entire result is enclosed in a ROWSET
  element. These names are both configurable, using
  the setRowTagName() and setRowSetTagName()
  procedures in DBMS_XMLGEN.
• Each column in the SQL query result, maps as a
  subelement of the ROW element.
• Binary data is transformed to its hexadecimal
  representation.

10
DBMS_XMLGEN Example output
SELECT FROM scott.emp -- Result of the form
lt?xml version"1.0"?gt ltROWSETgt ltROWgt
ltEMPNOgt30lt/EMPNOgt ltENAMEgtScottlt/ENAMEgt
ltSALARYgt20000lt/SALARYgt lt/ROWgt ltROWgt
ltEMPNOgt30lt/EMPNOgt ltENAMEgtMarylt/ENAMEgt
ltAGEgt40lt/AGEgt lt/ROWgt lt/ROWSETgt
11
Oracle XDB Generating XML Using SQLX Functions

12
SQLX FunctionXMLElement

• It takes an element name, an optional collection
  of attributes for the element, and zero or more
  arguments that make up the element's content and
  returns an instance of type XMLType

SELECT e.employee_id, XMLELEMENT ( "Emp",
e.fname ' ' e.lname ) AS "result" FROM
employees e WHERE employee_id gt 200 -- Result
of the form -- ID result --
-------------------- 1001 ltEmpgtJohn Smithlt/Empgt
1206 ltEmpgtMary Martinlt/Empgt
13
SQLX FunctionXMLElement

• You can generated nested XML document by nesting
  XMLElement() calls

SELECT XMLELEMENT("Emp",
XMLELEMENT("name", e.fname
' ' e.lname),
XMLELEMENT ( "hiredate", e.hire)) AS
"result WHERE employee_id gt 200 -- Result of
the form ltEmpgt ltnamegtJohn Smithlt/namegt
lthiredategt2000-05-24lt/hiredategt lt/Empgt ltEmpgt
ltnamegtMary Martinlt/namegt lthiredategt1996-02-01lt/h
iredategt lt/Empgt
14
SQLX FunctionXMLForest

• Produces a forest of XML elements from the given
  list of arguments

SELECT XMLELEMENT("Emp",
XMLForest ( e.hire, e.dept AS
"department") )AS "result WHERE employee_id gt
200 -- Result of the form ltEmpgt
ltHIREgt2000-05-24lt/HIREgt ltdepartmentgtAccountinglt/
departmentgt lt/Empgt ltEmpgt ltHIREgt1996-02-01lt/HI
REgt ltdepartmentgtShippinglt/departmentgt lt/Empgt
15
SQLX FunctionXMLConcat

• Concatenates all the arguments passed in to
  create a XML fragment

SELECT XMLConcat ( XMLElement ("first", e.fname),

XMLElement ("last", e.lname)) AS "result" FROM
employees e -- Result of the form
ltfirstgtMarylt/firstgt ltlastgtMartinlt/lastgt
ltfirstgtJohnlt/firstgt ltlastgtSmithlt/lastgt
16
SQLX FunctionXMLAgg

• Produces a forest of XML elements from a
  collection of XML elements
• Can be used to concatenate XMLType instances
  across multiple rows

SELECT XMLELEMENT( "Department",
XMLATTRIBUTES (
e.dept AS "name" ),
XMLAGG (XMLELEMENT ("emp",
e.lname))) AS "dept_list" FROM employees e GROUP
BY dept -- Result of the form ltDepartment
name"Accounting"gt ltempgtYateslt/empgt
ltempgtSmithlt/empgt lt/Departmentgt ltDepartment
name"Shipping"gt ltempgtOppenheimerlt/empgt
ltempgtMartinlt/empgt lt/Departmentgt
17
SQLX FunctionXMLColAttVal

• Generates a forest of XML column elements
  containing the value of the arguments passed in

SELECT XMLELEMENT("Emp",
XMLATTRIBUTES(e.fname '
'e.lname AS "name" ),
XMLCOLATTVAL ( e.hire, e.dept
AS "department")) AS "result" FROM employees e
-- Result of the form ltEmp name"John Smith"gt
ltcolumn name"HIRE"gt2000-05-24lt/columngt ltcolumn
name"department"gtAccountinglt/columngt lt/Empgt
ltEmp name"Mary Martin"gt ltcolumn
name"HIRE"gt1996-02-01lt/columngt ltcolumn
name"department"gtShippinglt/columngt lt/Empgt
18
Oracle XDB Generating XML Using Oracle SQL
Functions

19
SQL FunctionXMLSequence

• Returns an XMLSequenceType which is a VARRAY of
  XMLType instances
• Result can be used in FROM clause of SQL queries

ltEMPLOYEESgt ltEMPgt ltEMPNOgt112lt/EMPNOgt
ltEMPNAMEgtJoelt/EMPNAMEgt ltSALARYgt50000lt/SALARYgt
lt/EMPgt ltEMPgt ltEMPNOgt217lt/EMPNOgt
ltEMPNAMEgtJanelt/EMPNAMEgt ltSALARYgt60000lt/SALARYgt
lt/EMPgt ltEMPgt ltEMPNOgt412lt/EMPNOgt7
ltEMPNAMEgtJacklt/EMPNAMEgt ltSALARYgt40000lt/SALARYgt
lt/EMPgt lt/EMPLOYEESgt
20
XMLSequence Example

• Create a XML document containing employees who
  make 50,000 or more for each year

SELECT SYS_XMLAGG(value(e),
xmlformat('EMPLOYEES'))
FROM TABLE(XMLSequence(Extract(doc,
'/EMPLOYEES/EMP'))) e WHERE
EXTRACTVALUE(value(e), '/EMP/SALARY') gt 50000
ltEMPLOYEESgt ltEMPgt ltEMPNOgt112lt/EMPNOgt
ltEMPNAMEgtJoelt/EMPNAMEgt ltSALARYgt50000lt/SALARYgt
lt/EMPgt ltEMPgt ltEMPNOgt217lt/EMPNOgt
ltEMPNAMEgtJanelt/EMPNAMEgt ltSALARYgt60000lt/SALARYgt
lt/EMPgt lt/EMPLOYEESgt
21
SQL FunctionSYS_XMLGEN

• Similar to the XMLElement() except that it takes
  a single argument and converts the result to XML
• If input is a scalar value, the function returns
  an XML element containing the scalar value.
• If input is a type, the function maps the
  user-defined type attributes to XML elements.
• If input is a XMLType instance, then the function
  encloses the document in an XML element whose
  default tag name is ROW.

SELECT SYS_XMLGEN(employee_id) FROM employees
WHERE last_name LIKE 'Scott' FROM employees e
-- Result of the form lt?xml version''1.0''?gt
ltemployee_idgt60lt/employee_idgt
22
SYS_XMLGEN Benefits

• You can create and query XML instances within SQL
  queries.
• Using the object-relational infrastructure, you
  can create complex and nested XML instances from
  simple relational tables
• Optionally use XMLFormatObjectType to format the
  resulting XML

23
SQL FunctionSYS_XMLAgg

• Aggregates all XML documents or fragments
  represented by input argument and produces a
  single XML document.
• Adds a new enclosing element with a default name,
  ROWSET.

24
Oracle XML DBXMLType Views

25
Oracle XDB XML Views

• XML views (known as XMLType views as XML document
  represented using built-in XMLType)
• wrap existing relational as well as
  object-relational data into XML formats

26
Why use Oracle XMLType Views?

• To take advantage of the Oracle XML DB XML
  features 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.

27
What are Oracle XMLType Views?

• XMLType view similar to an object view
• Represents a collection of XMLType instances
• XMLType views of two types
• Non-conforming to an XML schema
• Conforming to an XML schema
• Allows for strong type checking
• Enables Optimization of queries over views
• To use XMLType views with XML schemas, you must
  first register your XML schema with annotations
  that represent the bi-directional mapping from
  XML to SQL object types.

28
Non-schema based XMLType Views
CREATE TABLE employees (empno number(4), fname
varchar2(20),
lname varchar2(20), hire date,
salary number(6)) INSERT INTO employees VALUES
(2100, 'John', 'Smith', Date'2000-05-24', 30000)
INSERT INTO employees VALUES (2200, 'Mary',
'Martin', Date'1996-02-01', 30000) CREATE OR
REPLACE VIEW Emp_view OF XMLTYPE WITH OBJECT ID
(EXTRACT(sys_nc_rowinfo,'/Emp/_at_empno').getnumberv
al()) AS SELECT XMLELEMENT("Emp",

XMLAttributes(empno),
XMLForest(e.fname ' '
e.lname AS "name",
e.hire
AS "hiredate")) AS "result" FROM employees e
WHERE salary gt 20000 -- empno attribute in the
document should become the unique identifier for
each row. -- SYS_NC_ROWINFO is a virtual column
that references the row XMLType instance.
29
Non-schema based XMLType Views (continued)
SELECT FROM Emp_view ltEmp empno"2100"gt
ltnamegtJohn Smithlt/namegt lthiredategt2000-05-24lt/hi
redategt lt/Empgt ltEmp empno"2200"gt ltnamegtMary
Martinlt/namegt lthiredategt1996-02-01lt/hiredategt
lt/Empgt
30
Schema based XMLType Views

• 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.
• 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 steps
• Create an object view
• Create an XMLType view over the object view

31
Schema based XMLType View Creation

• Create object types

CREATE OR REPLACE TYPE emp_t AS OBJECT ( EMPNO
NUMBER(4), ENAME VARCHAR2(10), JOB
VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE,
SAL NUMBER(7,2), COMM NUMBER(7,2) ) /
CREATE OR REPLACE TYPE emplist_t AS TABLE OF
emp_t / CREATE OR REPLACE TYPE dept_t AS
OBJECT (DEPTNO NUMBER(2), DNAME VARCHAR2(14),
LOC VARCHAR2(13), EMPS EMPLIST_T ) /
32
Schema based XMLType View Creation

• Register XML Schema

dbms_xmlschema.registerSchema('http//www.oracle.c
om/dept.xsd', ltschema xmlns"http//www.w
3.org/2001/XMLSchema"
targetNamespace"http//www.oracle.com/dept.xsd"
version"1.0" xmlnsxdb"http//xmln
s.oracle.com/xdb" "gt ltelement name
"Department" xdbSQLType"DEPT_T"
xdbSQLSchema"SCOTT"gt ltcomplexTypegt ltsequencegt
ltelement name "DeptNo" type "positiveInteger"
xdbSQLName"DEPTNO" xdbSQLType"NUMBER"/gt
ltelement name "Employee" maxOccurs
"unbounded" xdbSQLName "EMPS"
xdbSQLType"EMPLIST_T" xdbSQLSchema"SCOTT"gt
ltcomplexTypegt ltsequencegt ltelement
name "EmployeeId" type "positiveInteger"
xdbSQLName"EMPNO" xdbSQLType"NUMBER"/gt
lt/sequencegt lt/complexTypegt
lt/elementgt lt/sequencegt lt/complexTypegt
lt/elementgt lt/schemagt', )
33
Schema based XMLType View Creation

• Create XMLType View on Relational Tables

CREATE OR REPLACE VIEW dept_xml OF XMLTYPE
XMLSCHEMA "http//www.oracle.com/dept.xsd"
ELEMENT "Department" WITH OBJECT ID
(EXTRACTVALUE(sys_nc_rowinfo, '/Department/DeptNo
')) AS SELECT dept_t(d.deptno,
d.dname,
d.loc, cast(multiset(
SELECT emp_t(e.empno,

e.ename,
e.job,

e.mgr,

e.hiredate,

e.sal,e.comm)
FROM emp e WHERE e.deptno
d.deptno)
AS emplist_t)) FROM dept d
34
Query Rewrite on XMLType Views

• Queries on both (schema-based and non-schema
  based) XMLType views get rewritten for better
  performance
• Example

SELECT EXTRACTVALUE(value(x),'/ROW/DEPTNO')
FROM dept_xml x WHERE EXISTSNODE(value(x),
'/ROW/EMPS/EMP_TSAL gt 200') 1
becomes SELECT d.deptno FROM dept d
WHERE EXISTS (SELECT NULL FROM emp e
WHERE e.deptno d.deptno AND
e.sal gt 200)
35
DML Operations

• View inherently update-able
• Else via INSTEAD-OF-TRIGGERS

36
For more Information

• Main Page http//otn.oracle.com/tech/xml/doc.html
  
• Oracle9i XML Database Developer's Guide - Oracle
  XML DB, Release 2 (9.2) http//otn.oracle.com/docs
  /products/oracle9i/doc_library/release2/appdev.920
  /a96620/toc.htm

37
(No Transcript)