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

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:

1.	Install Oracle Database 11g and make sure the OE, HR users are unlocked.
2.	Set environment variables to connect to Oracle Database 11g.
3.	Download and unzip the xmldb_trg.zip file into your working directory (i.e.wkdir)

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 ACCOUNT UNLOCK IDENTIFIED BY HR;
ALTER USER OE ACCOUNT UNLOCK IDENTIFIED BY OE;
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:

1.	Open your browser and enter the following URL: https://<hostname>:1158/em Enter the following details, and accept the default value for Connect As. User Name: system Password: oracle Then, click Login.
2.	Oracle Enterprise Manager 11g Database Control window is displayed. Click the Schema tab.
3.	Under XML Database, select the Configuration link.
4.	Enter 2100 for FTP Port and 8080 for HTTP port. Then, click OK.
5.	Your configuration has been set. Click Database.
6.	Go to Schema > XML Database, and select the Resources link.
7.	All the resources are displayed. Expand home.
8.	Expand OE - PurchaseOrders - 2002 - Apr to show the list of XML documents. Click on the first XML document in the list.
9.	General information about the document is shown. To see the actual contents of the document, click Display Contents.
10.	The file contents are shown. Click Show formatted XML Content.
11.	Review the formatted XML document. When you are done, close the window.
12.	Click Database
13.	Go to Schema > XML Database. Then, click XMLType Tables
14.	In the XMLType Tables search window, the Object Name displays SYSTEM. Delete SYSTEM, and click Go.
15.	Click the table name PURCHASEORDER.
16.	The table definition is displayed. Scroll down to see more information. Then, click OK.
17.	To log out of Oracle Enterprise Manager 11g, click Logout.

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:

Start SQL Developer
Create Binary XML Table

Back to Topic List

Start SQL Developer

Perform the following steps:

Open a terminal window, and go to the location of SQL Developer install. Then, start SQL Developer. Use the following commands.

cd /u01/app/oracle/product/11.1.0/db_1/sqldeveloper
sh sqldeveloper.sh

You must create a database connection as OE user. Perform the following steps.

a. In the Connections tab, right-click Connections and select New Connection.

b. The New/Select Database Connection window appears. Enter the following details, and click Test to make sure that the connection has been set correctly.

Connection Name: oe
UserName: oe
Password:oe
Hostname: localhost or <hostname> if you are using a remote machine
Port: 1521
SID: orcl

If you select the Save Password check box, the password is saved to an XML file. Therefore, once you close SQL Developer connection and open again, you will not be prompted for the password.

c. The test status shows success. Click Connect.

Set the Autotrace parameters. Perform the following steps:

a. Go to Tools > Preferences.

b.Expand Database, and select Autotrace Parameters.

c. Make sure to select the following check boxes and click OK.

Object_Name
Cost
Cardinality

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

Back to Topic

Create Binary XML Table

Create an XMLType table with binary XML storage, and populate the table with data selected from the PURCHASEORDERS table. Run the script create_s1_bix_table.sql. Perform the following steps:

a. Right-click in the Enter SQL Statement box, and select Open File.

b. Browse to the location of your working directory, and select the file <filename>. Then, click Open.

c. The code is displayed in the Enter SQL Statement box of the SQL Worksheet. Click the Run Script icon. Alternatively, you can press F5. Note the results that are displayed under the Script Output pane.

create_s1_bix_table.sql

DROP table po_sl_bix_table purge;
/
CREATE TABLE PO_SL_BIX_TABLE OF XMLType
XMLType STORE AS BINARY XML;
/
INSERT INTO po_sl_bix_table
SELECT object_value from purchaseorder;
/

Note:Throughout this tutorial, to execute the script files in SQL Developer, you must follow the above steps a through c. If you want to run a single statement at the mouse pointer, click the Execute Statement icon. Alternatively, move the cursor over the statement, and press F9.

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:

1.	In your SQLDeveloper session, connect as OE user. Then, execute the script createXMLIndexes. set echo on -- B-tree indexes for O-R storage drop index iPurchaseOrderUser; drop index iPurchaseOrderRef; drop index iLineItemPartNumber; drop index iPartNumber; drop index iDESCRIPTION_FULL_TEXT; -- XMLIndex indexes for binary XML storage drop index po_sl_xmlindex_bix_ix force; drop index po_sl_bix_text_ix; create index iPurchaseOrderUser on PurchaseOrder (extractValue(object_value,'/PurchaseOrder/User')) / create index iPurchaseOrderRef on PurchaseOrder (extractValue(object_value,'/PurchaseOrder/Reference')) / create index iLineItemPartNumber on LINEITEM_TABLE (ITEMNUMBER, PART.PART_NUMBER, NESTED_TABLE_ID) compute statistics / create index iPartNumber on LINEITEM_TABLE (PART.PART_NUMBER, NESTED_TABLE_ID) compute statistics / -- Create XML index on the binary XML table create index po_sl_xmlindex_bix_ix on po_sl_bix_table(object_value) indextype is xdb.xmlindex parameters ('PATH TABLE po_sl_bix_path_table PATH ID INDEX po_sl_bix_path_id_ix ORDER KEY INDEX po_sl_bix_order_key_ix VALUE INDEX po_sl_bix_value_ix') / -- Create a secondary text index on the VALUE column of the path table CREATE INDEX po_sl_bix_text_ix ON po_sl_bix_path_table (VALUE) INDEXTYPE IS CTXSYS.CONTEXT parameters ('transactional') / call dbms_stats.gather_table_stats(USER,'PURCHASEORDER') / call dbms_stats.gather_table_stats(USER,'LINEITEM_TABLE') / call dbms_stats.gather_table_stats(USER,'po_sl_bix_table') / call dbms_stats.gather_index_stats(USER,'iPurchaseOrderUser') / call dbms_stats.gather_index_stats(USER,'iPurchaseOrderRef') / call dbms_stats.gather_index_stats(USER,'iLineItemPartNumber') / call dbms_stats.gather_index_stats(USER,'iPartNumber') / call dbms_stats.gather_index_stats(USER,'po_sl_xmlindex_bix_ix') / call dbms_stats.gather_index_stats(USER,'po_sl_bix_text_ix') /
2.	Now that you created indexes, you view the explain plan to observe the performance of SQL/XML, XQuery expressions. Observe that the explain plan picks up the applicable indexes. First, view the execution plan of the query that reviews a specific purchase order.In the Enter SQL Statement box, perform the following steps: a. Open the file XQuery03a. The code is displayed in the Enter SQL Statement box. Now, click the Autotrace icon. Note the usage of the index IPURCHASEORDERREF. Code in XQuery03a: --Xquery: Review a specific purchase order SELECT XMLQuery('/PurchaseOrder/ShippingInstructions/name' passing object_value returning content) FROM PURCHASEORDER WHERE XMLEXISTS('/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]' passing object_value) / b. Open the file XQuery03b , and click the Autotrace icon. Note the usage of XMLIndex index. Code in XQuery03b: -- Same query on a binary storage table SELECT XMLQuery('/PurchaseOrder/ShippingInstructions/name' passing object_value returning content).getStringVal() FROM PO_SL_BIX_TABLE WHERE XMLEXISTS('/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]' passing object_value) /
3.	View the execution plan of the query that reviews all the purchase orders having a particular part id. a. Open the file XQuery04a. The code is displayed in the Enter SQL Statement box. Now, click the Autotrace icon. Note the usage of IPARTNUMBER. Code in XQuery04a: --List the purchase orders References having a particular part id SELECT XMLQuery('/PurchaseOrder/Reference' passing object_value returning content) FROM PURCHASEORDER WHERE XMLExists('/PurchaseOrder/LineItems/LineItem/Part[@Id="717951002372"]' passing object_value) / b. Open the file XQuery04b, and click the Autotrace icon. Note the usage of XMLIndex index. Code in XQuery04b: --Same query on binary storage table SELECT XMLQuery('/PurchaseOrder/Reference' passing object_value returning content).getStringVal() FROM PO_SL_BIX_TABLE WHERE XMLExists('/PurchaseOrder/LineItems/LineItem/Part[@Id="717951002372"]' passing object_value) /
4.	View the execution plan of the query that lists the description for each line item on a particular purchase order. a. Open the file XQuery05a. The code is displayed in the Enter SQL Statement box. Now, click the Autotrace icon. Code in XQuery05a: -- 1. List the description for each item on the purchase order SELECT XMLQuery('/PurchaseOrder/LineItems/LineItem/Description' passing object_value returning content) FROM PURCHASEORDER WHERE XMLExists('/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]' passing object_value) / b. Open the file XQuery05b. The code is displayed in the Enter SQL Statement box. Now, click the Autotrace icon. Code in XQuery05b: --same query on binary storage table SELECT XMLQuery('/PurchaseOrder/LineItems/LineItem/Description' passing object_value returning content).getStringVal() FROM PO_SL_BIX_TABLE WHERE XMLExists('/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]' passing object_value) /
5.	View the execution plan of the query that lists the references for LineItem 20 with a description containing picnic on a particular purchase order. Open the file XQuery06b. The code is displayed in the Enter SQL Statement box. Now, click the Autotrace icon. Code in XQuery06b: --same query on binary storage table SELECT XMLQuery('/PurchaseOrder/Reference' passing object_value returning content).getStringVal() FROM PO_SL_BIX_TABLE WHERE XMLExists('/PurchaseOrder/LineItems/LineItem[@ItemNumber=20]/Description/text()[ora:contains(.,"Picnic") > 0]' passing object_value) /

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:

You first will create an XML view over relational tables by using a XMLTable() SQL/XML function and an XQuery expression. Execute the following script:

createXQueryView

create or replace synonym departments for hr.departments
/
create or replace synonym locations for hr.locations
/
create or replace synonym countries for hr.countries
/
create or replace synonym employees for hr.employees
/
create or replace synonym jobs for hr.jobs
/
create or replace view DEPARTMENT_XQL of xmltype
with object id
(extract(object_value, '/Department/@DepartmentId').getnumberVal())
as
select column_value from XMLTable
('for $d in ora:view("DEPARTMENTS"),
$l in ora:view("LOCATIONS"),
$c in ora:view("COUNTRIES")
where $d/ROW/LOCATION_ID = $l/ROW/LOCATION_ID
and $l/ROW/COUNTRY_ID = $c/ROW/COUNTRY_ID
return
<Department DepartmentId= "{$d/ROW/DEPARTMENT_ID/text()}" >
<Name>{$d/ROW/DEPARTMENT_NAME/text()}</Name>
<Location>
<Address>{$l/ROW/STREET_ADDRESS/text()}</Address>
<City>{$l/ROW/CITY/text()}</City>
<State>{$l/ROW/STATE_PROVINCE/text()}</State>
<Zip>{$l/ROW/POSTAL_CODE/text()}</Zip>
<Country>{$c/ROW/COUNTRY_NAME/text()}</Country>
</Location>
<EmployeeList>
{
for $e in ora:view("EMPLOYEES"),
$m in ora:view("EMPLOYEES"),
$j in ora:view("JOBS")
where $e/ROW/DEPARTMENT_ID = $d/ROW/DEPARTMENT_ID
and $j/ROW/JOB_ID = $e/ROW/JOB_ID
and $m/ROW/EMPLOYEE_ID = $e/ROW/MANAGER_ID
return
<Employee employeeNumber="{$e/ROW/EMPLOYEE_ID/text()}" >
<FirstName>{$e/ROW/FIRST_NAME/text()}</FirstName>
<LastName>{$e/ROW/LAST_NAME/text()}</LastName>
<EmailAddress>{$e/ROW/EMAIL/text()}</EmailAddress>
<Telephone>{$e/ROW/PHONE_NUMBER/text()}</Telephone>
<StartDate>{$e/ROW/HIRE_DATE/text()}</StartDate>
<JobTitle>{$j/ROW/JOB_TITLE/text()}</JobTitle>
<Salary>{$e/ROW/SALARY/text()}</Salary>
<Manager>{$m/ROW/LAST_NAME/text(), ", ", $m/ROW/FIRST_NAME/text()}</Manager>
</Employee>
}
</EmployeeList>
</Department>')
/

Now you can show XQuery over the XML view you just created.

a. Execute the script xqueryXQLView.

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
/

b. View the execution plan of the query.Click the Autotrace icon.

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:

You first will create a relational view over a binary XML by using a XMLTable() SQL/XML function. Execute the script createXMLTableView.

create or replace view PO_MASTER_DETAIL_VIEW
as
select m.REFERENCE,m.REQUESTOR,m.USERID,m.COSTCENTER,item.*
from PO_SL_BIX_TABLE,
xmltable
('/PurchaseOrder' passing object_value
COLUMNS
REFERENCE varchar2(30) path 'Reference',
REQUESTOR varchar2(128) path 'Requestor',
USERID varchar(10) path 'User',
COSTCENTER varchar2(4) path 'CostCenter',
ITEMS xmltype path 'LineItems'
) m,
xmltable
('/LineItems/LineItem' passing m.items
COLUMNS
ITEMNO number(38) path '@ItemNumber',
DESCRIPTION varchar2(1024) path 'Description',
PARTNO varchar2(56) path 'Part/@Id',
QUANTITY number(38) path 'Part/@Quantity',
UNITPRICE number(12,2) path 'Part/@UnitPrice'
) item
/

Now you can use SQL queries on the relational view. Execute the script queryXMLTableView.

select REFERENCE, ITEMNO, PARTNO, DESCRIPTION
from PO_MASTER_DETAIL_VIEW
where USERID = 'SBELL'
and PARTNO in ( '37429121726', '37429122129', '715515009058' )
/

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

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

Purpose

Topics

Viewing Screenshots

Overview

Prerequisites

Creating Binary XML Table

Improving Performance of XQuery Expressions through Index Creation

Using XMLType Views with XQuery

Using Relational Views over Binary XML Table

Summary

Place the cursor over this icon to hide all screenshots.