Oracle XML DB and XQuery Chris Gianfrancesco Aruna

Oracle XML DB and XQuery Chris Gianfrancesco Aruna Apuri Oleg Rekutin Jason Wilson

Introduction n n XML Type abstraction Storage ¨ Shredded n Publishing ¨ XML n or LOB Views of relational tables SQL / XML functions and constructs ¨ XMLQuery, XMLTable, ¨ XML Updates n and more. . . XQuery evaluation and processing

XML Type XQuery XPath XSLT SQL / XML Type abstraction Physical Storage CLOB XML Type Views Shredded Relational Data Hybrid Binary XML

XML View Create virtual XML version of objectrelational data n Allows XQuery to access relational data n Uses XML Publishing n ora: view() n

SQL/XML Functions n SQL/XML querying function and construct ¨ XMLQuery, n XMLTable SQL/XML functions for creating XML from SQL ¨ XMLElement(), XMLForest() XMLConcat(), XMLAttributes(),

More XML Functions n Other XML functions ¨ XMLCol. Att. Val(), XMLSequence(), Extract. Value(), Extract(), XMLTransform() n To support XML updates ¨ Update. XML(), Delete. XML(), Insert. Child. XML(), Insert. XMLBefore(), Append. Child. XML()

XQuery Hybrid Evaluation Transform XMLTable into XMLQuery n Static analysis and type checking n If possible, compiles into native SQL data structures n If not possible, XMLQuery is left as is for processing by XMLQuery processor n

XQuery Hybrid Evaluation SQL query containing XMLQuery/XMLTable Transform XMLTable to XMLQuery SQL query containing XMLQuery Native compilation of XMLQuery SQL structures with XML operators SQL structures containing XMLQuery XQuery evaluated natively Co-processor evaluates XMLQuery expressions

Input & Data Representation ¨ All data in one row, one XMLType column

Input & Data Representation ¨ Each data row in separate DB row, column of XMLType

Input & Data Representation ¨ Each data row in separate DB row, contents in separate columns

Input Tools n Straight XML in SQL ¨ INSERT VALUES( XMLType(‘<xml>goes here</xml>’)) JDBC using special XMLType (also C) n SQL*Loader w/ direct path load mode n XML-SQL Utility (XSU) n ¨ Maps XML to columns ¨ Rigid default mapping ¨ No support for attributes

Storage in Database n XMLType CLOB ¨ File preserved as complete text (whitespace, comments, etc) [textual fidelity] ¨ Can still be validated against a schema ¨ Data internally is not “typed” ¨ Slow querying ¨ Fastest storage and retrieval

Storage in Database n XMLType View ¨ Create a virtual XML document on top of relational tables ¨ Fast querying, manipulation using pure SQL ¨ Deeply nested views are slow ¨ Updating/inserting requires triggers ¨ Lose strict order guarantee, no textual fidelity ¨ Supports multiple XML schemas on top of one relational schema

Storage in Database n Native XML type (Structured Storage) ¨ Preserves textual fidelity ¨ Shreds into SQL tables ¨ Complete validation, full SQL support ¨ No triggers to update tables (built-in rewriting) ¨ Some overhead ¨ Cannot change schema w/o reloading all data ¨ Requires a schema

Structured Storage Detail n Annotate XML schema to control nested collections storage, as: ¨ CLOB ¨ Array of serialized SQL objects ¨ Nested table of serialized SQL objects ¨ Array of XMLType

Working with XML Schema n Registering schema begin dbms_xmlschema. register. Schema( ‘http: //namespace', xdb. URIType('schema. xsd'). get. Clob(), TRUE, FALSE, TRUE); end; n Creating table w/ schema CREATE TABLE Table. Name of XMLType XMLSCHEMA "http: //namespace"

XQuery Support in Oracle n XMLDB integrated database engine SQL / XML standard support ¨ Optimized queries – rewrite to relational ¨ n Standalone Java query engine 100% Java ¨ Integrated into Oracle App Server -XDS ¨ Interoperates with XSLT/XPath ¨

XQuery database support n n n n Production in Oracle Database 10 gr 2 Supports XMLQuery and XMLTable construct Native compilation into SQL /XML structures Returns XMLType(Content) Can query over relational, O-R, XMLType data fn: doc - Maps to XDB Repository on server SQLPlus provides xquery command to execute XQuery XSL-T will also get compiled to XQuery

Architecture X Q U E R Y XQuery XSL-T Parser XQuery. X Statically Type checked Tree Compiler Normalization Normalized Tree (casts, treat ) Rewrite to SQLX Compiled XQuery Tree XQuery Type check SQL/XML Operand Tree SQLX rewrite SQL Operand Tree SQL Metadata XMLSchema Repository XML Indexes, Text Indexes Relational Optimizer Execution Structures XQuery F&O Execution engine S Q L

XQuery Java implementation n n n XQuery or XQuery. X input Extensible function implementation Compiles into rowsource like structures Optimization – push XQuery to XMLDB XQJ API driver – for accessing mid tier/backend Shared data model with XSL/XPath Shared F&O – pre-defined & external ¨ Standard implementation interfaces ¨ Write Java Function once use it in XQuery/XSLT

Processing XQuery Oracle XQuery Compilation Engine n Parser convert XQuery into XQuery. X n XQuery. X is an XML representation of XQuery (another W 3 C candidate recommendation) n XML parser construct a DOM tree from XQuery. X n Work on the DOM afterward n Corresponding components are extended for XQuery too

Sample XQuery For each author in the bibliography, list the author's name and the titles of all books by that author, grouped inside a "result" element. " <results> FOR $a IN distinct(document("http: //www. bn. co m")//author) RETURN <result> $a, FOR $b IN document("http: //www. bn. com")/bib/b ook[author = $a] RETURN $b/title </result> </results>

WHAT IS XQuery. X n Is an XML representation of an XQuery. n Created by mapping the productions of the XQuery abstract syntax directly into XML productions. n The result is not particularly convenient for humans to read and write. n Easy for programs to parse, and because XQuery. X is represented in XML, standard XML tools can be used to create, interpret, or modify queries

Environments in which XQuery. X useful n Parser Reuse. In heterogeneous data environments, a variety of systems may be used to execute a query. One parser can generate XQuery. X for all of these systems. n Queries on Queries. Because XQuery. X is represented in XML, queries can be queried and can be transformed into new queries. For instance, a query can be performed against a set of XQuery. X queries to determine which queries use FLWOR expressions to range over a set of invoices. n n Generating Queries. In some XML-oriented programming environments, it may be more convenient to build a query in its XQuery. X representation than in the corresponding XQuery representation, since XML tools can be used to do so. n Embedding Queries in XML. XQuery. X can be embedded directly in an XML document

Why XQuery static type checking? XQuery static type checking is very useful when the input XML structure is known during compile time. n The feature itself enables early error recovery. n

XQuery Static Type-Checking in Oracle XML DB n n n Oracle XML DB performs static (that is, compile-time) type -checking of XQuery expressions. It also performs dynamic (runtime) type-checking. Example Static Type-Checking of XQuery Expression The XML view produced on the fly by Oracle XQuery function ora: view has ROW as its top-level element, but this example incorrectly lacks that ROW wrapper element. This omission raises a compile-time error. Forgetting that ora: view wraps relational data in this way is an easy mistake to make, and one that could be difficult to diagnose without static type-checking.

Static Type-Checking of XQuery Expressions: ora: view n This produces a static-type-check error, because "ROW" is missing. SELECT XMLQuery('for $i in ora: view("REGIONS"), $j in ora: view("COUNTRIES") where $i/REGION_ID = $j/REGION_ID and $i/REGION_NAME = "Asia" return $j' RETURNING CONTENT) AS asian_countries FROM DUAL; SELECT XMLQuery('for $i in ora: view("REGIONS"), $j in ora: view("COUNTRIES") * ERROR at line 1: ORA-19276: XP 0005 - XPath step specifies an invalid element/attribute name: (REGION_ID)

Correct code SELECT XMLQuery('for $i in ora: view("REGIONS"), $j in ora: view("COUNTRIES") where $i/ROW/REGION_ID = $j/ROW/REGION_ID and $i/ROW/REGION_NAME = "Asia" return $j' RETURNING CONTENT) AS asian_countries FROM DUAL;

Result Sequence <ROW><DEPARTMENT_ID>10</DEPARTMENT_ID><DEPARTMENT_NAME>Administr ation</DEPARTMENT_NAME><MANAGER_ID>200</MANAGER_ID><LOCATION_ ID>1700</LOCATION_ID></ROW> <ROW><DEPARTMENT_ID>20</DEPARTMENT_ID><DEPARTMENT_NAME>Marketing </DEPARTMENT_NAME><MANAGER_ID>201</MANAGER_ID><LOCATION_ID>18 00</LOCATION_ID></ROW> <ROW><DEPARTMENT_ID>30</DEPARTMENT_ID><DEPARTMENT_NAME>Purchasin g</DEPARTMENT_NAME><MANAGER_ID>114</MANAGER_ID><LOCATION_ID>1 700</LOCATION_ID></ROW> <ROW><DEPARTMENT_ID>40</DEPARTMENT_ID><DEPARTMENT_NAME>Human Resources</DEPARTMENT_NAME><MANAGER_ID>203</MANAGER_ID><LOCAT ION_ID>2400</LOCATION_ID></ROW>

XQuery Processing Choices: co-processor or native compilation? n Co-processor: n ¨ “off-the-shelf” XQuery processor ¨ opaque to DBMS n Native compilation: ¨ XQuery processing added to database engine ¨ DBMS-specific processor

Co-processor Advantages Easy to implement and install n Modularity of XQuery processor n Standard XQuery processor between applications n Third-party development n Flexibility n

Co-processor Limitations n Storage Optimization ¨ Advanced Oracle XML DB features being wasted (e. g. indexed XML) n Query Optimization ¨ Cannot use already-established Oracle query engine optimizations ¨ No support for SQL/XML query optimization

Oracle's Native Processing XQueries are compiled into sub-blocks and execution structures usable by existing DB engine n “tightly integrate XQuery and SQL/XML support within the database kernel” n Focuses on utilizing existing optimization techniques (algebra optimizations) n XQuery interpreter for unsupported operations n

Native Processor Architecture

Advantages of Oracle's Approach Fully utilizes mature optimization techniques n Integration of SQL and XQueries n ¨ Much stronger support for SQL/XML mixed query optimizations ¨ No need for development of a separate set of optimizations n “performance that is orders of magnitude faster than the co-processor approach”

Conclusion XMLType n Variety of ways for data to be stored n XQuery parsing and static type checking n XQuery native processing and coprocessor n

References n n n n Zhen Hua Liu, Maralidhar Krishnaprasad, Vikas Aora. Native XQuery Processing in Oracle XMLDB. SIGMOD 2005. Ravi Murthy, Zhen Hua Liu, Muralidhar Krishnaprasad, et al. Towards An Enterprise XML Architecture. SIGMOD 2005. Mark Scardina. XML Storage Models: One Size Does Not Fit All. http: //www. oracle. com/technology/oramag/webcolumns/2003/techarticles/s cardina_xmldb. html XML Query (XQuery) Support in Oracle Database 10 g Release 2. Oracle White Paper. May 2005. XML and Datenbanken. http: //www. dbis. ethz. ch/education/ws 0506/xml_db_ws 2005 http: //www. dbspecialists. com http: //www. w 3. org/TR/2003/WD-xqueryx-20031219/#N 1016 C http: //www. w 3 schools. com/xquery_example. asp