Using Oracle XML DB to Store, Query, and Access XML and Relational Data

Purpose

This tutorial shows you how to store, query, and access XML and relational data in Oracle XML DB.

Time to Complete

Approximately 30 minutes.

Topics

This tutorial covers the following topics:

	Overview
	Prerequisites
	Reviewing the XML Schema in Enterprise Manager
	Creating Binary XMLTable
	Improving Performance of XQuery Expressions through Index Creation
	Using XMLType Views with XQuery
	Using Relational Views over Binary XML Table
	Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

Since Oracle 9i Database Release 2, Oracle XML DB has been seamlessly integrated with the Oracle database to provide high-performance database-native storage, retrieval, and management of XML data. With the new Oracle Database 11g release, Oracle XML DB is taking another leap ahead with a rich set of new capabilities to simplify DBAs' tasks in managing XML data while further empowering XML and SOA application developers. Oracle XML DB now supports multiple database-native XML storage models and XML indexing schemes, SQL/XML standard operations, W3C standard XQuery data model and XQuery/XPath languages, database-native web services, high performance XML publishing, XML DB repository, and versioning and access control. This tutorial covers these topics along with key new features for using Oracle XML DB to store, query, transform, and access XML and relational data.

Binary XML Storage Model:
Binary XML is a new storage model for abstract data type XMLType, joining the existing native storage models of structured (object-relational) and unstructured (CLOB) storage. Binary XML storage provides more efficient database storage, updating, indexing, and fragment extraction than unstructured storage. It can provide better query performance than unstructured storage-it does not suffer from the XML parsing bottleneck (it is a post-parse persistence model). Like structured storage, binary XML storage is aware of XML Schema data types and can take advantage of native database data types. Like unstructured storage, no data conversion is needed during database insertion or retrieval. Like structured storage, binary XML storage allows for piecewise updates. Because binary XML data can also be used outside the database, it can serve as an efficient XML exchange medium, and you can offload work from the database to increase overall performance in many cases. Like unstructured storage, binary XML data is kept in document order. Like structured storage, data and metadata can, using binary storage, be separated at the database level, for efficiency. Like unstructured storage, however, binary storage allows for intermingled data and metadata, which lets instance structures vary. Binary XML storage allows for very complex and variable data, which in the structured-storage model could necessitate using many database tables and joins. Unlike the other XMLType storage models, you can use binary storage for XML schema-based data even if the XML schema is not known beforehand, and you can store multiple XML schemas in the same table and query across common elements.

XMLIndex Indexing for Binary XML and Unstructured XML Storage Models:
B-Tree indexes can be used advantageously with structured storage. They provide sharp focus by targeting the underlying objects directly. They are generally ineffective, however, in addressing the detailed structure (elements and attributes) of an XML document stored in a binary XML or a CLOB instance. That is the special domain of XMLIndex: binary XML and unstructured storage models. Unlike a B-Tree index, which you define for a specific column that represents an individual XML element or attribute, an XMLIndex index is very general: indexing with XMLIndex applies to all possible XPath expressions for your XML data.An XMLIndex index presents the following advantages over other indexing methods:

• An XMLIndex index can be used for SQL/XML functions XMLExists(), XMLTable(), and XMLQuery(), and it is effective in any part of a query; it is not limited to use in a WHERE clause. This is not the case for any of the other kinds of indexes you might use with XML data.
• XMLIndex can thus speed access to SELECT list data and FROM list data, making it useful for XML fragment extraction, in particular. Function-based indexes and CTXXPath indexes
• You need no prior knowledge of the XPath expressions that will be used in queries. XMLIndex is completely general. This is not the case for function-based indexes.
• You can use an XMLIndex index with either XML schema-based or non-schema-based data. It can be used with binary XML and unstructured storage models. B-Tree indexing is appropriate only for schema-based data stored object-relationally (structured storage); it is ineffective for XML schema-based data stored in a binary XML or a CLOB instance.
• You can use an XMLIndex index for searches with XPath expressions that target collections, that is, nodes that occur multiple times within a document. This is not the case for functional indexes.

Oracle Database-Native XQuery:
Since XQuery is now a W3C standard, the IT community has started adopting the business uses of XML and XQuery. As the innovation leader in commercial database technology, Oracle Database 11g provides a full-featured native XQuery engine integrated with the traditional Oracle database server.On the SQL side, the SQL/XML standard has defined a way to encapsulate XML in SQL and to integrate the querying of XML using XQuery. This is being accomplished by introducing new SQL functions: XMLQuery, XMLTable, XMLExists, and XMLCast , which operate on XML and SQL values using XQuery. Oracle Database 11g enables XQuery support in the database server through these SQL standard functions. A new XQUERY command has also been implemented in SQL*Plus to allow users to enter XQuery expressions on the command line. With standards-based implementation of XQuery in Oracle Database 11g, application developers can use their favorite APIs (e.g., JDBC, ODP.NET, and web service) to access Oracle Database XQuery capabilities.

Benefits of Oracle XQuery:
Using SQL/XML XQuery functions along with indexing schemes for structured, unstructured, and binary XML storage models, XML DB can perform uniform XML queries across different storage models with orders of magnitude performance improvement over DOM-based functional evaluation of XML queries. Furthermore, XML queries can be seamlessly merged with SQL relational queries to handle all query scenarios. Finally, the XML query capabilities of Oracle XML DB are built on the solid foundation of industry's best relational database that is highly reliable, available, scalable, and secure. In short, the XML DB query capabilities in Oracle Database 11g provide the most comprehensive and efficient functionality for versatile, scalable, concurrent, and high performance XML applications.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should first complete the following steps:

Additional Notes for Prerequisites:

If you do not unlock the OE, and HR users during Oracle Database 11g installation, perform the following:

a. Open a terminal window, and type the following command:

sqlplus sys/oracle as sysdba;

b. Once you get connected as sys, then, unlock the OE, and HR accounts.Then, grant dba role to OE, and HR. Grant SELECT_CATALOG_ROLE to OE. In the SQL*Plus window, type the following commands. Alternatively, run the script accounts.sql:

ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;
ALTER USER OE IDENTIFIED BY OE ACCOUNT UNLOCK ;
GRANT DBA to OE, HR;
GRANT SELECT_CATALOG_ROLE TO OE;

To set environment variables to connect to Oracle Database 11g, perform the following:

a. Open a terminal window, and search for .bash_profile.

b. Edit the .bash_profile to make sure of the following:

• ORACLE_HOME environment variable is set
• ORACLE_HOME/lib is added to LD_LIBRARY_PATH environment variable

For example, if your ORACLE_HOME is $ORACLE_BASE/product/11.1.0/db_1, and JAVA_HOME is /home/oracle/jdk1.5.0_05, make sure that your .bash_profile includes the following entries:

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
ORACLE_SID=orcl
JAVA_HOME=/home/oracle/jdk1.5.0_05
PATH=$JAVA_HOME/bin:$PATH
PATH=$ORACLE_HOME/bin:$PATH

CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc.jar:$ORACLE_HOME/jlib/orai18n.jar
export PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID JAVA_HOME LD_LIBRARY_PATH

c. Once you make the changes to .bash_profile, execute the script as follows:

./.bash_profile

Back to Topic List

Reviewing the XML Schema in Enterprise Manager

An XML Schema has been supplied as part of the OE Schema in the Sample Schema that is provided with Oracle Database 11g. In this section, you will review its contents using Enterprise Manager. Perform the following steps:

Back to Topic List

Creating Binary XML Table

In this section, you create a binary XML table, and insert rows with data selected from the PURCHASEORDER table.You use Oracle SQL Developer throughout this tutorial. Perform the following steps:

Back to Topic List

Start SQL Developer

Perform the following steps:

In the above section, you learned how to connect to SQL Developer, and set Autotrace parameters.

In the above topic, you learned how to create an XMLType table with binary XML storage. You also learned how to populate data into this table.

Back to Topic List

Improving Performance of XQuery Expressions through Index Creation

You can increase the performance of your XQuery by creating an index. In this section, you will create B-Tree indexes on object relational storage and XMLIndex index on binary storage table. You will then run the SQL/XML, XQuery expressions against both object-relational and binary XML tables to see the explain plan and note that the performance has improved. Perform the following steps:

In the above topic, you learned how to create B-Tree indexes on object relational storage and XMLIndex index on binary XML table. You also learned how to observe the performance of SQL/XML, XQuery expressions by viewing the explain plan.

Back to Topic List

Using XMLType Views with XQuery

You can use XQuery to generate XML from SQL data using Views. Perform the following steps:

select t.column_value from department_xql x, xmltable('for $i in .
	 where $i/Department/EmployeeList/Employee/LastName="Grant"
	 return $i/Department/Name'
	 passing value(x)) t
/

Back to Topic List

Using Relational Views over Binary XML Table

You can use the XMLTable() function to create and efficiently query relational views over binary XML tables. Perform the following steps:

In the above topic, you learned how to create a relational view over a binary XML table. You also learned how to use query the relational view.

Back to Topic List

Summary

In this tutorial, you learned how to:

	Review an XML Schema in Enterprise Manager
	Create a binary XML table and store data
	Add indexes to improve the performance of XQuery expressions
	Create an XMLType view with XQuery
	Use relational views over binary XML table

Back to Topic List

Place the cursor over this icon to hide all screenshots.