Oracle XML DB and XQuery

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
• oraview()

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(ltxmlgtgoes herelt/xmlgt))
• JDBC using special XMLType (also C)
• SQLLoader 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').getCl
  ob(), 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
• fndoc - 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 FO
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 FO pre-defined external
• Standard implementation interfaces
• Write Java Function once use it in
  XQuery/XSLT

22
Processing XQueryOracle XQuery Compilation Engine

• 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

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."
ltresultsgt FOR a IN distinct(document("http//www.
bn.com")//author) RETURN ltresultgt
a, FOR b IN document("http//www.bn.com"
)/bib/bookauthor a RETURN b/title
lt/resultgt lt/resultsgt
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 oraview 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 oraview
  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
oraview

• This produces a static-type-check error, because
  "ROW" is missing.
• SELECT XMLQuery('for i in oraview("REGIONS"),
  j in oraview("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 oraview("REGIONS"),
  j in oraview("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 oraview("REGIONS"),
  j in oraview("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

• ltROWgtltDEPARTMENT_IDgt10lt/DEPARTMENT_IDgtltDEPARTMENT_
  NAMEgtAdministrationlt/DEPARTMENT_NAMEgtltMANAGER_IDgt2
  00lt/MANAGER_IDgtltLOCATION_IDgt1700lt/LOCATION_IDgtlt/RO
  Wgt
• ltROWgtltDEPARTMENT_IDgt20lt/DEPARTMENT_IDgtltDEPARTMENT_
  NAMEgtMarketinglt/DEPARTMENT_NAMEgtltMANAGER_IDgt201lt/M
  ANAGER_IDgtltLOCATION_IDgt1800lt/LOCATION_IDgtlt/ROWgt
• ltROWgtltDEPARTMENT_IDgt30lt/DEPARTMENT_IDgtltDEPARTMENT_
  NAMEgtPurchasinglt/DEPARTMENT_NAMEgtltMANAGER_IDgt114lt/
  MANAGER_IDgtltLOCATION_IDgt1700lt/LOCATION_IDgtlt/ROWgt
• ltROWgtltDEPARTMENT_IDgt40lt/DEPARTMENT_IDgtltDEPARTMENT_
  NAMEgtHuman Resourceslt/DEPARTMENT_NAMEgtltMANAGER_IDgt
  203lt/MANAGER_IDgtltLOCATION_IDgt2400lt/LOCATION_IDgtlt/R
  OWgt

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/oram
  ag/webcolumns/2003/techarticles/scardina_xmldb.htm
  l
• XML Query (XQuery) Support in Oracle Database 10g
  Release 2. Oracle White Paper. May 2005.
• XML and Datenbanken. http//www.dbis.ethz.ch/educa
  tion/ws0506/xml_db_ws2005
• http//www.dbspecialists.com
• http//www.w3.org/TR/2003/WD-xqueryx-20031219/N10
  16C
• http//www.w3schools.com/xquery/xquery_example.asp