XML STORAGE AND XPATH QUERIES IN ORACLE

Jiankai Wu & Joel Poualeu

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

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.

Structure storage

How to decide

• Data-centric XML documents

• Document-centric XML documents

• Hybrid XML documents

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

registers a XML schemaBEGIN

DBMS_XMLSCHEMA.REGISTERSCHEMA

(

SCHEMAURL => 'http://localhost/Oraxdb/OracleXMLSchema.xsd',

SCHEMADOC => BFILENAME('XML_DIR','OracleXMLSchema.xsd'),

GENTABLES => FALSE,

csid        => NLS_CHARSET_ID('AL32UTF8'));

END;

/

 

PL/SQL PROCEDURE successfully completed.

create the schema based XML Type table

CREATE TABLE xml_demo_structured

OF XMLTYPE

xmlschema "http://xmlns.oracle.com/xdb/schemas/ORADEV/localhost/Oraxdb/OracleXMLSchema.xsd"

ELEMENT "EmployeeDetails"

/

TABLE created

Insert a sample XML Type data in the table

INSERT INTO xml_demo_structured VALUES

(xmltype(BFILENAME('XML_DIR', 'Allen.txt'), NLS_CHARSET_ID('AL32UTF8')))

/

 

1 ROW inserted.

sample text file Allen.txt

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

creation of a table of XMLType

1.CREATE TABLE xml_demo_unstructured OF XMLTYPE

2.xmltype STORE AS clob

3./

4. 

5.TABLE created.

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

Hybrid storage

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

resume

XPATH• Addresses an XML document’s 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 @ prefix.• [ ] can further specify an element.

Xpath Addressing Example<?xml version="1.0" encoding="UTF-8"?>

<a>

<b>

<c>Hello world!</c>

<d price="$56" />

</b>

</a>Expression Result

a/b select <b> elements children of <a> elements

/a/b/c "Hello world!"

/a/b/d/@price "$56"

Xpath Expression Example<?xml version="1.0" encoding="UTF-8"?>

<inventory>

<other>

<msg>not available</msg>

</other>

<item class="cleaning">

<name>Soap</name>

<amount>33</amount>

</item>

<item class="food" type="perishable">

<name>Milk</name>

<amount>56</amount>

</item>

</inventory>

Expression Result/inventory/item[2]/name Milk

/inventory/item/name[2] null

/inventory/item[@class="cleaning"]/name Soap

/inventory/item[@type]/name Milk

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, <, >, <=, >=• Supported functions

• substring-before(string1, string2)• substring-after(string1, string2)• translate(string1, string2, string3)• normalize-space()

References• "How XPath Works." Oracle Documentation. N.p., n.d. Web. 2 May

2013. <http://docs.oracle.com/javaee/1.4/tutorial/doc/JAXPXSLT3.html>.

• "Introduction to Oracle XML DB." Oracle Documentation. N.p., n.d. Web. 2 May 2013. <http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb01int.htm>.

• "XPath 2.0 Expression Syntax." The SAXON XSLT and XQuery Processor. N.p., n.d. Web. 2 May 2013. <http://saxon.sourceforge.net/saxon7.5/e