XML Storage and XPath Queries in Oracle

1
XML Storage and XPath Queries in Oracle

• Jiankai Wu Joel Poualeu

2
Outline

1. XML type storage models
2. How to decide selection of models
3. Example of creating XML type table
4. XPath Addressing
5. XPath Expressions
6. XPath Data Types, Operators and Functions

3
XMLType Storage Models

• Unstructured storage referred to as
  CLOB(Character Large Object) storage and
  text-based persistence.
• Structured storage referred to as
  object-relational storage and object-based
  persistence.

4
Structure storage
5
How to decide

• Data-centric XML documents
• Document-centric XML documents
• Hybrid XML documents

6
Data-centric

• Data is highly structured, with relatively static
  and predictable structure
• Example an employee record (fields employee
  number, name, address, and so on)
• Employ object-relational (structured) storage

7
registers a XML schema

• BEGIN
• DBMS_XMLSCHEMA.REGISTERSCHEMA
• (
• SCHEMAURL gt 'http//localhost/Oraxdb/OracleXMLSch
  ema.xsd',
• SCHEMADOC gt BFILENAME('XML_DIR','OracleXMLSchema.
  xsd'),
• GENTABLES gt FALSE,
• csid        gt NLS_CHARSET_ID('AL32UTF8'))
• END
• /
•  
• PL/SQL PROCEDURE successfully completed.

8
create the schema based XML Type table

• CREATE TABLE xml_demo_structured
• OF XMLTYPE
• xmlschema "http//xmlns.oracle.com/xdb/schemas/ORA
  DEV/localhost/Oraxdb/OracleXMLSchema.xsd"
• ELEMENT "EmployeeDetails"
• /
• TABLE created

9
Insert a sample XML Type data in the table
sample text file Allen.txt

• INSERT INTO xml_demo_structured VALUES
• (xmltype(BFILENAME('XML_DIR', 'Allen.txt'),
  NLS_CHARSET_ID('AL32UTF8')))
• /
•  
• 1 ROW inserted.

10
Document-centric

• Data is generally without structure or of
  variable structure
• Content is mixed--elements contain large amounts
  of text
• Stored or fetched as a complete document
• Employ CLOB-based(unstructured) storage

11
creation of a table of XMLType

• CREATE TABLE xml_demo_unstructured OF XMLTYPE
• xmltype STORE AS clob
• /
•  
• TABLE created.

12
Structured (object-relational) storage

• Optimized memory management
• Reduced storage requirements
• B-tree indexing
• Structural flexibility is reduced

Unstructured (CLOB) storage

• Easy to insert and retrieve entire XML document
• No data conversion is needed

13
Hybrid storage

• The mixture of structured and unstructured
  storage
• Example an employee record that includes a
  free-form resume

14
XPATH

• Addresses an XML documents elements and
  attributes.
• Addressing is similar to file system addressing
• (/) is a path separator.
• (/) starts An absolute path from the root
• Relative path starts with anything else.
• (..) indicates the parent of the current node.
• (.) indicates the current node.
• Attributes are specified by _at_ prefix.
• can further specify an element.

15
Xpath Addressing Example

• lt?xml version"1.0" encoding"UTF-8"?gt
• ltagt
• ltbgt
• ltcgtHello world!lt/cgt
• ltd price"56" /gt
• lt/bgt
• lt/agt

Expression Result
a/b select ltbgt elements children of ltagt elements
/a/b/c "Hello world!"
/a/b/d/_at_price "56"
16
Xpath Expression Example

• lt?xml version"1.0" encoding"UTF-8"?gt
• ltinventorygt
• ltothergt
• ltmsggtnot availablelt/msggt
• lt/othergt
• ltitem class"cleaning"gt
• ltnamegtSoaplt/namegt
• ltamountgt33lt/amountgt
• lt/itemgt
• ltitem class"food" type"perishable"gt
• ltnamegtMilklt/namegt
• ltamountgt56lt/amountgt
• lt/itemgt
• lt/inventorygt

Expression Result
/inventory/item2/name Milk
/inventory/item/name2 null
/inventory/item_at_class"cleaning"/name Soap
/inventory/item_at_type/name Milk
17
XPath Data Types and Operators and functions

• XPath expressions yield either a set of nodes, a
  string, a Boolean (a true/false value), or a
  number
• , -, , div, mod, lt, gt, lt, gt
• Supported functions
• substring-before(string1, string2)
• substring-after(string1, string2)
• translate(string1, string2, string3)
• normalize-space()

18
References

• "How XPath Works." Oracle Documentation. N.p.,
  n.d. Web. 2 May 2013. lthttp//docs.oracle.com/java
  ee/1.4/tutorial/doc/JAXPXSLT3.htmlgt.
• "Introduction to Oracle XML DB." Oracle
  Documentation. N.p., n.d. Web. 2 May 2013.
  lthttp//docs.oracle.com/cd/B28359_01/appdev.111/b2
  8369/xdb01int.htmgt.
• "XPath 2.0 Expression Syntax." The SAXON XSLT and
  XQuery Processor. N.p., n.d. Web. 2 May 2013.
  lthttp//saxon.sourceforge.net/saxon7.5/e