Using Oracle XML DB to Store, Query, and Access XML and Relational Data
This tutorial shows you how to store, query, and access XML and relational data in Oracle XML DB.
Time to Complete
Approximately 30 minutes.
This tutorial covers the following topics:
|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|
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.
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.
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:
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.
Before you perform this tutorial, you should first complete the following steps:
Install Oracle Database 11g and make sure the OE , HR users are unlocked.
|2.||Set environment variables to connect to Oracle Database 11g.|
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 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
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:
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:
Open your browser and enter the following URL:
Enter the following details, and accept the default value for Connect As.
Then, click Login.
Oracle Enterprise Manager 11g Database Control window is displayed. Click the Schema tab.
Under XML Database, select the Configuration link.
Enter 2100 for FTP Port and 8080 for HTTP port. Then, click OK.
Your configuration has been set. Click Database.
Go to Schema > XML Database, and select the Resources link.
All the resources are displayed. Expand home.
Expand OE - PurchaseOrders - 2002 - Apr to show the list of XML documents. Click on the first XML document in the list.
General information about the document is shown. To see the actual contents of the document, click Display Contents.
The file contents are shown. Click Show formatted XML Content.
Review the formatted XML document. When you are done, close the window.
Go to Schema > XML Database. Then, click XMLType Tables
In the XMLType Tables search window, the Object Name displays SYSTEM. Delete SYSTEM, and click Go.
Click the table name PURCHASEORDER.
The table definition is displayed. Scroll down to see more information. Then, click OK.
To log out of Oracle Enterprise Manager 11g, click Logout.
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:
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.
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.
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.
In the above section, you learned how to connect to SQL Developer, and set Autotrace parameters.
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.
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.
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 your SQLDeveloper session, connect as OE user. Then, execute the script createXMLIndexes.
set echo on
-- XMLIndex indexes for binary XML storage
create index iPurchaseOrderUser on PurchaseOrder
create index iLineItemPartNumber on LINEITEM_TABLE
-- Create XML index on the binary XML table
-- Create a secondary text index on the VALUE column of the path table
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.
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:
SELECT XMLQuery('/PurchaseOrder/ShippingInstructions/name' passing object_value returning content)
b. Open the file XQuery03b , and click the Autotrace icon. Note the usage of XMLIndex index.
Code in XQuery03b:
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:
SELECT XMLQuery('/PurchaseOrder/Reference' passing object_value returning content)
b. Open the file XQuery04b, and click the Autotrace icon. Note the usage of XMLIndex index.
Code in XQuery04b:
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:
SELECT XMLQuery('/PurchaseOrder/LineItems/LineItem/Description' passing object_value returning content)
b. Open the file XQuery05b. The code is displayed in the Enter SQL Statement box. Now, click the Autotrace icon.
Code in XQuery05b:
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:
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.
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:
create or replace synonym departments for hr.departments
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 .
b . View the execution plan of the query.Click the Autotrace icon.
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
Now you can use SQL queries on the relational view. Execute the script queryXMLTable View.
select REFERENCE, ITEMNO, PARTNO, DESCRIPTION
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.
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|