1 / 38

Oracle XML DB and XQuery

Oracle XML DB and XQuery. Chris Gianfrancesco Aruna Apuri Oleg Rekutin Jason Wilson. Introduction. XML Type abstraction Storage Shredded or LOB Publishing XML Views of relational tables SQL / XML functions and constructs XMLQuery, XMLTable, and more... XML Updates

leda
Download Presentation

Oracle XML DB and XQuery

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. Oracle XML DB and XQuery Chris Gianfrancesco Aruna Apuri Oleg Rekutin Jason Wilson

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

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

  4. XML View • Create virtual XML version of object-relational data • Allows XQuery to access relational data • Uses XML Publishing • ora:view()

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

  6. More XML Functions • Other XML functions • XMLColAttVal(), XMLSequence(), ExtractValue(), Extract(), XMLTransform() • To support XML updates • UpdateXML(), DeleteXML(), InsertChildXML(), InsertXMLBefore(), AppendChildXML()

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

  8. 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

  9. Input & Data Representation • All data in one row, one XMLType column

  10. Input & Data Representation • Each data row in separate DB row,column of XMLType

  11. Input & Data Representation • Each data row in separate DB row,contents in separate columns

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

  13. Storage in Database • 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

  14. Storage in Database • 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

  15. Storage in Database • 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

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

  17. Working with XML Schema • Registering schema begin dbms_xmlschema.registerSchema( ‘http://namespace', xdbURIType('schema.xsd').getClob(), TRUE,TRUE,FALSE,TRUE); end; • Creating table w/ schema CREATE TABLE TableName of XMLType XMLSCHEMA "http://namespace"

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

  19. XQuery database support • Production in Oracle Database 10gr2 • 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

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

  21. XQuery Java implementation • XQuery or XQueryX 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

  22. Parser convert XQuery into XQueryX XQueryX is an XML representation of XQuery (another W3C candidate recommendation) XML parser construct a DOM tree from XQueryX Work on the DOM afterward Corresponding components are extended for XQuery too Processing XQueryOracle XQuery Compilation Engine

  23. 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.com")//author) RETURN <result> $a, FOR $b IN document("http://www.bn.com")/bib/book[author = $a] RETURN $b/title </result> </results>

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

  25. Environments in which XQueryX useful • Parser Reuse. In heterogeneous data environments, a variety of systems may be used to execute a query. One parser can generate XQueryX for all of these systems. • Queries on Queries. Because XQueryX 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 XQueryX queries to determine which queries use FLWOR expressions to range over a set of invoices. • Generating Queries. In some XML-oriented programming environments, it may be more convenient to build a query in its XQueryX representation than in the corresponding XQuery representation, since XML tools can be used to do so. • Embedding Queries in XML. XQueryX can be embedded directly in an XML document

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

  27. XQuery Static Type-Checking in Oracle XML DB • 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.

  28. Static Type-Checking of XQuery Expressions: ora:view • 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: XP0005 - XPath step specifies an invalid element/attribute name: (REGION_ID)

  29. 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;

  30. Result Sequence <ROW><DEPARTMENT_ID>10</DEPARTMENT_ID><DEPARTMENT_NAME>Administration</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>1800</LOCATION_ID></ROW> <ROW><DEPARTMENT_ID>30</DEPARTMENT_ID><DEPARTMENT_NAME>Purchasing</DEPARTMENT_NAME><MANAGER_ID>114</MANAGER_ID><LOCATION_ID>1700</LOCATION_ID></ROW> <ROW><DEPARTMENT_ID>40</DEPARTMENT_ID><DEPARTMENT_NAME>Human Resources</DEPARTMENT_NAME><MANAGER_ID>203</MANAGER_ID><LOCATION_ID>2400</LOCATION_ID></ROW>

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

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

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

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

  35. Native Processor Architecture

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

  37. Conclusion • XMLType • Variety of ways for data to be stored • XQuery parsing and static type checking • XQuery native processing and co-processor

  38. References • Zhen Hua Liu, Maralidhar Krishnaprasad, Vikas Aora. Native XQuery Processing in Oracle XMLDB. SIGMOD2005. • Ravi Murthy, Zhen Hua Liu, Muralidhar Krishnaprasad, et al. Towards An Enterprise XML Architecture. SIGMOD2005. • Mark Scardina. XML Storage Models: One Size Does Not Fit All. http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/scardina_xmldb.html • XML Query (XQuery) Support in Oracle Database 10g Release 2. Oracle White Paper. May 2005. • XML and Datenbanken. http://www.dbis.ethz.ch/education/ws0506/xml_db_ws2005 • http://www.dbspecialists.com • http://www.w3.org/TR/2003/WD-xqueryx-20031219/#N1016C • http://www.w3schools.com/xquery/xquery_example.asp

More Related