Oracle XDB, Release 9iR2 Publishing XML and XMLType Views

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 SQL Functions SYS_XMLGEN SYS_XMLAGG XMLSequence PL/SQL Package DBMS_XMLGEN 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: <?xml version="1.0"?> <ROWSET> <ROW> <EMPNO>30</EMPNO> <ENAME>Scott</ENAME> <SALARY>20000</SALARY> </ROW> <ROW> <EMPNO>30</EMPNO> <ENAME>Mary</ENAME> <AGE>40</AGE> </ROW> </ROWSET>

11. Oracle XDB: Generating XML Using SQLX Functions

12. SQLX Function:XMLElement • 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 > 200; -- Result of the form: -- ID result -- -------------------- 1001 <Emp>John Smith</Emp> 1206 <Emp>Mary Martin</Emp>

13. SQLX Function:XMLElement • 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 > 200; -- Result of the form: <Emp> <name>John Smith</name> <hiredate>2000-05-24</hiredate> </Emp> <Emp> <name>Mary Martin</name> <hiredate>1996-02-01</hiredate> </Emp>

14. SQLX Function:XMLForest • 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 > 200; -- Result of the form: <Emp> <HIRE>2000-05-24</HIRE> <department>Accounting</department> </Emp> <Emp> <HIRE>1996-02-01</HIRE> <department>Shipping</department> </Emp>

15. SQLX Function:XMLConcat • 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: <first>Mary</first> <last>Martin</last> <first>John</first> <last>Smith</last>

16. SQLX Function:XMLAgg • 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: <Department name="Accounting"> <emp>Yates</emp> <emp>Smith</emp> </Department> <Department name="Shipping"> <emp>Oppenheimer</emp> <emp>Martin</emp> </Department>

17. SQLX Function:XMLColAttVal • 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: <Emp name="John Smith"> <column name="HIRE">2000-05-24</column> <column name="department">Accounting</column> </Emp> <Emp name="Mary Martin"> <column name="HIRE">1996-02-01</column> <column name="department">Shipping</column> </Emp>

18. Oracle XDB: Generating XML Using Oracle SQL Functions

19. SQL Function:XMLSequence • Returns an XMLSequenceType which is a VARRAY of XMLType instances • Result can be used in FROM clause of SQL queries <EMPLOYEES> <EMP> <EMPNO>112</EMPNO> <EMPNAME>Joe</EMPNAME> <SALARY>50000</SALARY> </EMP> <EMP> <EMPNO>217</EMPNO> <EMPNAME>Jane</EMPNAME> <SALARY>60000</SALARY> </EMP> <EMP> <EMPNO>412</EMPNO>7 <EMPNAME>Jack</EMPNAME> <SALARY>40000</SALARY> </EMP> </EMPLOYEES>

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') >= 50000; <EMPLOYEES> <EMP> <EMPNO>112</EMPNO> <EMPNAME>Joe</EMPNAME> <SALARY>50000</SALARY> </EMP> <EMP> <EMPNO>217</EMPNO> <EMPNAME>Jane</EMPNAME> <SALARY>60000</SALARY> </EMP> </EMPLOYEES>

21. SQL Function:SYS_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: <?xml version=''1.0''?> <employee_id>60</employee_id>

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 Function:SYS_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 DB:XMLType 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/@empno').getnumberval()) AS SELECT XMLELEMENT("Emp", XMLAttributes(empno), XMLForest(e.fname ||' '|| e.lname AS "name", e.hire AS "hiredate")) AS "result" FROM employees e WHERE salary > 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; <Emp empno="2100"> <name>John Smith</name> <hiredate>2000-05-24</hiredate> </Emp> <Emp empno="2200"> <name>Mary Martin</name> <hiredate>1996-02-01</hiredate> </Emp>

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.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" …"> <element name = "Department" xdb:SQLType="DEPT_T" xdb:SQLSchema="SCOTT"> <complexType> <sequence> <element name = "DeptNo" type = "positiveInteger" xdb:SQLName="DEPTNO" xdb:SQLType="NUMBER"/> … <element name = "Employee" maxOccurs = "unbounded" xdb:SQLName = "EMPS" xdb:SQLType="EMPLIST_T" xdb:SQLSchema="SCOTT"> <complexType> <sequence> <element name = "EmployeeId" type = "positiveInteger" xdb:SQLName="EMPNO" xdb:SQLType="NUMBER"/> … </sequence> </complexType> </element> </sequence> </complexType> </element> </schema>', …);

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_T[SAL > 200]') = 1; becomes: SELECT d.deptno FROM dept d WHERE EXISTS (SELECT NULL FROM emp e WHERE e.deptno = d.deptno AND e.sal > 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