| |
Using Oracle XML Database to Manage XML Documents
Module Objectives
Purpose
This module describes how to load an XML schema
into the database and manage XML documents that are stored in the database.
Objectives
Upon completion of this module, you should be able to:
Prerequisites
The prerequisites for this lesson are:
Reference Material
The following list represents some useful reference material should you
need additional information on the topics in this module:
 |
Documentation: Application Developers Guide - XML
|
|
Education: D13852GC10 Oracle9i Database Release 2: Oracle XML DB
Components eStudy
|
Overview
What is the Oracle XML Database?
Oracle XML DB is a high-performance XML storage and retrieval technology
available with the Oracle9i Release 2 server. It fully absorbs the W3C
XML data model into the Oracle server, and provides new standard access
methods for navigating and querying XML. With Oracle XML DB, you get all
the advantages of relational database technology and XML technology at
the same time.
Oracle XML DB is a native XML database. It provides a storage independent,
content independent, and programming language independent infrastructure
to manage data. Although it is closely integrated with the Oracle SQL
engine, it introduces newer concepts never before present in relational
databases. For example, it builds the XML Document Object Model (DOM)
into the Oracle kernel so that most of the operations on XML data can
be done as a part of regular database processing.
Oracle XML DB is designed to store and retrieve XML objects using relational
and hierarchical access APIs. It provides fine access control and configuration
management for the XML objects. To improve data access performance, Oracle
XML DB also implements popular protocols like FTP, HTTP and WebDAV as
a part of the database server. It is designed around open standards and
implements them to provide different views and intuitive access to a single
Repository. Oracle XML DB hence helps you build standards-based XML applications
and implementations on Oracle9i database.
Oracle XML Database Components
There are two key components to the Oracle XML Database. They are:
|
XML Type: A datatype that stores XML content in the database.
It can be used as the datatype of a column in a table or view. |
|
XML Repository: An XML-enabled file system that maps XMLType
objects anywhere in the database to Internet-enabled storage. The
XML Repository provides foldering, access control, versioning, and
other services for XML resources. It allows you fast access and manipulation
based on path names, to XML data stored in the database. |
Benefits of Oracle XML Database
The integration of a native XML capability within the database brings
a number of benefits.
|
Provides a unique ability to store and manage both structured and
unstructured data, under a standard W3C XML data model. Oracle XML
DB provides complete transparency and interchangeability between the
XML and SQL metaphors. You can perform XML operations over table data
and SQL operations over XML documents. |
|
Provides valuable Repository functionality – foldering, access
control, FTP and WebDAV protocol support with versioning – enabling
applications to retain the file abstraction when manipulating XML
data stored in the Oracle Database. |
|
Provides superior performance and scalability for XML operations |
|
Provides better management of unstructured XML data through piecewise
updates, indexing, search, multiple views on the data, managing intra-document
and inter-document relationships. |
|
Enables data and documents from disparate systems to be accessed
(e.g. through gateways, external tables) and combined into a standard
data model. This integrative aspect reduces the complexity of developing
applications that must deal with data from different stores. |
Tasks to Setup and Manage an Oracle XML Database
Environment
The steps to setup and manage XML documents in an Oracle XML Database
environment are as follows:
Setup the XML Database Environment
Go Back to List of Tasks
There are a few steps you will need to perform to setup the XML database
environment. They include:
| 1. |
Make sure that your Oracle HTTP Server is running.
Since you are operating more than one database on a single laptop
or machine you will want to change the XML Database configuration
settings for each database to make sure the HTTP port and FTP port
numbers are unique. Open the Enterprise Manager Console and
login to the OMS as sysman password of sysman. Expand
Databases and connect to oms.world
as sys password
of oracle. Expand XML Database and click on Configuration.
Change ftp-port from 2100 to 2121 and change http-port from
8080 to 8181 then click Apply. When done, close the
Enterprise Manager Console.
|
| 2. |
Setup the users and their privileges. Open a DOS window, change
directory to your working directory d:\wkdir
and execute the following:
set ORACLE_SID=ORCL
sqlplusw /nolog
@globalsetup
|
| 3. |
Open Internet Explorer and select Tools > Internet Options.
Select the Connections tab and then click the LAN Settings...
button and click Advanced in the Proxy Server section. Add
your hostname to the Proxy Settings Exceptions and click OK.
|
| 4. |
Open Windows Explorer and select My Network Places
on the left Navigator. Double-click on Add Network Place.
Note: To create a Folder on NT, the steps are slightly different.
|
| 5. |
In the Type the location to add field, enter http://<hostname>:8080/
and click Next.
|
| 6. |
Login as oe/oe and click OK.
|
| 7. |
In the Enter the name for this Web Folder field, enter your
hostname and click Finish.
|
| 8. |
Login as oe/oe and click OK.
|
| 9. |
You will see the folders that have been created.
|
Create Directories where XML Schema and Documents will
be stored in the database
Go Back to List of Tasks
Before you can load XML documents into your database, you must create
the directories where the xml will be loaded. They include:
| 1. |
From a SQL*Plus session, execute the following:
@makeDirectories
|
| 2. |
Switch to Windows Explorer and expand your Web Folder or My
Network Places to see that the directories were created. When
asked to log in, make sure OE/OE is specified.
|
Load the XML Schema in the Database
Go Back to List of Tasks
Now you can load the XML Schema into the database. The XML Schema will
store the metadata of the XML documents that you load into the database.
| 1. |
In Windows Explorer go to the d:\wkdir\xmlfiles\xsd
directory. Also expand your Network or Web Folder /home/OE/
directory. Drag and drop ord.xsd
into the xsd directory in your database.
|
| 2. |
Go to the d:\wkdir\xmlfiles\xsl
directory. Drag and drop all the xsl files into the xsl
directory in your database.
|
| 3. |
Switch to Windows Explorer to view what is now contained in the
xsd and xsl
web folders.
|
View, Validate and Register the XML Schema
Go Back to List of Tasks
You will want to take a look at the XML Schema and validate it before
you register the Schema for use. To do this, perform the following:
| 1. |
Select Start > Programs > XML Spy Suite > XML Spy IDE.
|
| 2. |
Select the Project pulldown window, then select Open
Project. Select d:\wkdir\xmlfiles\schema.spp
then click Open.
|
| 3. |
In the Project navigator, expand DTD/Schemas. Double-click
on ord.xsd. You
will see a list of all the elements in your schema in the middle
window. If you would like to see the XML, click the Text View
icon 
|
| 4. |
The XML text will appear. At this point you would also like to
look at the Oracle specific parameters, click on the Schema Design
View icon 
to switch back to the list of elements.
|
| 5. |
Select the Vendor element and select the Oracle tab
in the Details box to learn about its definition in the Oracle database.
|
| 6. |
Right-click on the Schemas Project in the Project navigator.
Select Validate. Once the validation completes successfully,
click OK. Then Exit from XMLSpy IDE.
|
| 7. |
Now you will need to register the schema you just validated. Open
a DOS Prompt and execute the following commands from your working
directory d:\wkdir:
set oracle_sid=orcl
set instance=orcl
sqlplusw /nolog
@registerSchema oe oe /home/OE/xsd/ord.xsd
"http://www.oracle.com/xdb/ord.xsd"
|
| 8. |
To view the schema you just registered, execute the following command
in the same sqlplus session:
@describePO
Your schema is now ready for access.
|
Load the XML Documents into the XML Database
Go Back to List of Tasks
Once the XML Schema is registered, you can load the XML documents into
the database. To do this, perform the following:
| 1. |
Switch to Windows Explorer and go to the d:\wkdir\xmlfiles
directory. Also expand your Network (Web Folder)
/home/OE/ directory. Drag the 2002
folder into the purchaseOrders
web folder.
|
| 2. |
Switch to Windows Explorer to view what is now contained in the
2002/Apr/ web folder.
|
Create and Validate any Unique or Referential Integrity
Constraints
Go Back to List of Tasks
At this point you will create a constraint on the purchase order table
and then copy some files to the database that violate those constraints.
| 1. |
From your sqlplus session, execute the following command:
@addConstraint
set echo on
connect oe/oe@orcl.world
alter table purchaseorders
add constraint REFERENCE_IS_UNQIUE
unique (xmldata."Reference")
/
alter table purchaseorders
add constraint USER_IS_VALID
foreign key (xmldata."EmployeeID") references employees(employee_id)
/
--quit
|
| 2. |
To test your constraint you will need to use an FTP program. In
this case, you will use FTP OnNet but any FTP program will work.
Select Start > Programs > WS_FTP > WS_FTP95 LE.
Leave the profile name as xdblocal1, enter your hostname
and specify the User ID of oe and the Password oe.
Then click the Advanced tab.
|
| 3. |
Enter 2100 in the Remote Port field and click OK.
|
| 4. |
For the Local System, set the directory to D:\wkdir\xmlfiles\Invalid
and for the Remote System set the directory to /home/OE/PurchaseOrders.
Click the Options button.
|
| 5. |
Click the Save Current Directories as Connection Directories
button and then click OK. Then click the Exit button
to exit from Ws_ftp95.
|
| 6. |
Go back into Ws_ftp95 once again and select OK when prompted
for the xdblocal1
profile. Select the Duplicate - Abel xml document on the
local system and click the -> to copy the file to the
directory over on the remote system. You will notice that you get
a unique constraint violation.
|
| 7. |
You can also test the referential integrity constraint by copying
over the HACKER xml document. Select the document on the
local system and click the -> to copy the file to the
directory over on the remote system. Notice that you get an error
indicating that there has been an integrity constraint violation.
When done, click Exit.
|
Run a simple query to access the XML Documents
Go Back to List of Tasks
To see the data in the XML Database, you will perform a set of queries
as follows:
| 1. |
From a SQL*Plus session, execute the following command:
@simpleQueries-1
The above query indicates that there are 16 purchase orders and
2 of which were made by KING. Then a particular purchase order made
by Abel is displayed.
|
| 2. |
Then execute another query to see all the purchase orders who ordered
a particular product.
@simpleQueries-2
|
Improve Performance of a Simple Query through Index
Creation
Go Back to List of Tasks
To improve the performance of a query against your XML Schema you will
want to create an Index. To see the performance improvement an index can
make, perform the following:
| 1. |
You will take a look at how the query is executed currently using
the explain plan. From a SQL*Plus session, execute the following:
@explainQueryPlans
SQL> @explainqueryplans
SQL> set echo on
SQL> truncate table plan_table;
Table truncated.
SQL> explain plan for
2 select value(x) from purchaseorders x
3 where existsNode(value(x),
'/PurchaseOrder[Reference="Abel-20021127121040707PST"]') = 1;
Explained.
SQL> set linesize 120
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID| PURCHASEORDERS | | | |
|* 2 | INDEX UNIQUE SCAN | REFERENCE_IS_UNQIUE | | | |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
2 - access("PURCHASEORDERS"."SYS_NC00009$"='Abel-20021127121040707PST')
Note: rule based optimization
15 rows selected.
SQL> explain plan for
2 select count(*) from purchaseorders x
3 where existsNode(value(x),'/PurchaseOrder[EmployeeID=201]') = 1;
Explained.
SQL> set linesize 120
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | TABLE ACCESS FULL | PURCHASEORDERS | | | |
------------------------------------------------------------------------
Note: rule based optimization
10 rows selected.
SQL> --quit
|
| 2. |
Now you can run the following command to create the index:
@createXMLIndexes
|
| 3. |
And finally, lets rerun the explain plan to see the performance
improvement the index will have.
@explainQueryPlans
SQL> @explainqueryplans
SQL> set echo on
SQL> truncate table plan_table;
Table truncated.
SQL> explain plan for
2 select value(x) from purchaseorders x
3 where existsNode(value(x),
'/PurchaseOrder[Reference="Abel-20021127121040707PST"]') = 1;
Explained.
SQL> set linesize 120
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1825 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| PURCHASEORDERS | 1 | 1825 | 2 |
|* 2 | INDEX UNIQUE SCAN | REFERENCE_IS_UNQIUE | 138 | | 1 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
2 - access("PURCHASEORDERS"."SYS_NC00009$"='Abel-20021127121040707PST')
Note: cpu costing is off
15 rows selected.
SQL> explain plan for
2 select count(*) from purchaseorders x
3 where existsNode(value(x),'/PurchaseOrder[EmployeeID=201]') = 1;
Explained.
SQL> set linesize 120
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1825 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| PURCHASEORDERS | 1 | 1825 | 2 |
|* 2 | INDEX UNIQUE SCAN | REFERENCE_IS_UNQIUE | 138 | | 1 |
| 2 | TABLE ACCESS FULL | PURCHASEORDERS | 1 | 1825 | 6 |
| 1 | SORT AGGREGATE | | 1 | 1825 | |
|* 2 | INDEX UNIQUE SCAN | REFERENCE_IS_UNQIUE | 138 | | 1 |
| 2 | TABLE ACCESS FULL | PURCHASEORDERS | 1 | 1825 | 6 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1825 | 6 |
| 1 | TABLE ACCESS BY INDEX ROWID| PURCHASEORDERS | 1 | 1825 | 2 |
|* 2 | INDEX UNIQUE SCAN | REFERENCE_IS_UNQIUE | 138 | | 1 |
| 2 | TABLE ACCESS FULL | PURCHASEORDERS | 1 | 1825 | 6 |
| 1 | SORT AGGREGATE | | 1 | 1825 | |
|* 2 | INDEX UNIQUE SCAN | REFERENCE_IS_UNQIUE | 138 | | 1 |
| 2 | TABLE ACCESS FULL | PURCHASEORDERS | 1 | 1825 | 6 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
2 - access("PURCHASEORDERS"."SYS_NC00009$"='Abel-20021127121040707PST')
2 - access("PURCHASEORDERS"."SYS_NC00009$"='Abel-20021127121040707PST')
2 - access("PURCHASEORDERS"."SYS_NC00009$"='Abel-20021127121040707PST')
2 - access("PURCHASEORDERS"."SYS_NC00009$"='Abel-20021127121040707PST')
Note: cpu costing is off
29 rows selected.
SQL> --quit
|
Make Changes to the XML Documents Stored in the Database
Go Back to List of Tasks
In this section you will make changes to an XML document stored in the
database. To do this, performa the following:
| 1. |
Open a SQL*Plus session and execute the following to view an XML
document in the database:
@showTargetDocument
|
| 2. |
You can make a change to the document using any editor. Open Microsoft
Word. Open the following XML document:
http://<hostname>:8080/home/OE/purchaseOrders/2002/Apr/
Abel-20021127121040707PST.xml
|
| 3. |
Make a change. For example, change the requestor to your name.
Then save the document. You have just saved your change in the database.
|
| 4. |
To verify that the change was actually made to the database, switch
to your SQL*Plus session and run @showTargetDocument.sql
again. You will notice that your change was made.
|
Add Views to Optimize Data Retrieval
Go Back to List of Tasks
You will create a MASTER and DETAIL view and then retrieve data from
the database. To do this, perform the following:
| 1. |
From a SQL*Plus session, execute the following:
@makeViews
|
| 2. |
Now you can query using the view you just created. Execute the
following:
@queryViews
|
| 3. |
You can also perform a rollup to perform various calculations very
quickly. Execute the following:
@rollupquery
|
Retrieving Information in the XML DB Repository using
Resource Views
Go Back to List of Tasks
You can retrieve information from the XML DB Repository about the XML
Documents by using Resource Views. To do this, perform the following:
| 1. |
You can retrieve the number of files contained in the XML DB Repository.
From a SQL*Plus session, execute the script @countDocuments
or type in the following command:
select count(*) from resource_view;
|
| 2. |
Now you will retrieve the number of files contained in the purchaseOrder
directory in the XML DB Repository. From a SQL*Plus session, execute
the script @countPODocuments
or type in the following command:
select count(*) from resource_view r
where any_path like '/home/OE/purchaseOrders%';
|
| 3. |
You can also find out the other directories other than /home/SCOTT/purchaseOrders
contained in your XML Database by executing the script @selectDirectories
or type in the following command:
select any_path from resource_view r
where any_path not like '/home/OE/purchaseOrders%';
|
| |
You can also find out the next directory under/home/SCOTT/purchaseOrders.
Execute the script @selectYearDir
or type in the following command:
select extractValue(r.res,'/Resource/DisplayName') "./purchaseOrders"
from resource_view r
where under_path(r.res, 1, '/home/OE/purchaseOrders', 1) = 1
order by extractValue(r.res,'/Resource/DisplayName');
|
| |
You can then find out all the directories in the 2002 directory
you just located. Execute the script @selectMonthDir
or type in the following command:
select extractValue(r.res,'/Resource/DisplayName') "./purchaseOrders/2002"
from resource_view R
where under_path(r.res, 1, '/home/OE/purchaseOrders/2002', 1) = 1
order by extractValue(r.res,'/Resource/DisplayName');
|
| |
You can then find out the all the XML files in the Apr. Execute
the script @selectXMLDocs
or type in the following command:
select extractValue(r.res,'/Resource/DisplayName') "./purchaseOrders/2002/Apr"
from resource_view R
where under_path(r.res, 1, '/home/OE/purchaseOrders/2002/Apr', 1) = 1
order by extractValue(r.res,'/Resource/DisplayName');
|
| |
You can then find out where all your XML Stylesheets are located.
Execute the script @selectXSL
or type in the following command:
select any_path
from resource_view r
where extractValue(r.res,'/Resource/DisplayName') like '%.xsl';
|
| |
You can find out what Purchase Orders were placed by the user Abel.
Execute the script @selectAbelPOs
or type in the following command:
select any_path
from resource_view r
where any_path like '/home/OE/purchaseOrders%'
and extractValue(r.res,'/Resource/DisplayName') like 'Abel%';
|
| |
You can find out what Purchase Orders were placed for a particular
part id. Execute the script @selectpopart
or type in the following command:
select any_path
from resource_view r, purchaseorders p
where ref(p) = extractValue(res,'/Resource/XMLRef')
and existsNode(value(p),
'/PurchaseOrder/LineItems/LineItem/Part[@Id="037429139523"]') = 1;
|
| |
You can retrieve the CLOB value that is contained in the XML DB.
Execute the script @selectclobvalue
or type in the following command:
set long 10000
select r.res.getClobVal()
from resource_view r
where any_path like '/home/OE/purchaseOrders/2002/Apr%'
and existsNode(r.res,
'/Resource[DisplayName="Abel-20021127121040707PST.xml"]') = 1;
|
Transforming XML Documents using Stylesheets
Go Back to List of Tasks
In addition to using Web Folders to access XML Documents, you can also
retrieve an XML Document using the oradb servlet inside the database.
Once the XML document is retrieved, you can then apply a stylesheet to
view the document as HTML. To do this, perform the following:
| 1. |
You will retrieve a purchase order as XML oradb servlet. Open Internet
Explorer and enter the following URL:
http://<hostname>:8080/oradb/OE/PURCHASEORDERS/ROW/
PurchaseOrder[Reference="Abel-20021127121040707PST"]?contenttype=text/xml
You will be asked to login. Login as oe/oe.
|
| 2. |
You will want to transform the XML into HTML using a Stylesheet.
Enter the following URL:
http://<hostname>:8080/home/OE/xsl/ord.xsl
|
| 3. |
Now you will apply the stylesheet. Enter the following URL:
http://<hostname>:8080/oradb/OE/PURCHASEORDERS/ROW/
PurchaseOrder[Reference="Abel-20021127121040707PST"]?contenttype=text/html
&transform=/home/OE/xsl/ord.xsl
|
Creating an XML View and Transforming the Data using
a Stylesheet
Go Back to List of Tasks
You can also create an XML View which can be displayed as an XML Document
and then transformed into HTML using a stylesheet. This can be done using
the oradb servlet. To do this, perform the following steps:
| 1. |
Open a SQL*Plus session and execute the following script:
@deptview
|
| 2. |
To see the definition of the view as an XML document, open your
browser and execute the following URL:
http://<hostname>:8080/oradb/PUBLIC/USER_VIEWS/ROW[VIEW_NAME="DEPTVIEW"]
You will be asked to login. Login as oe/oe.
|
| 3. |
To show all departments for the view as an XML Documents, enter
the following URL:
http://<hostname>:8080/oradb/OE/DEPTVIEW?contenttype=text/xml
&rowsettag=Departments
|
| 4. |
You will want to apply a stylesheet to the XML Document to transform
the document to HTML. To see what the stylesheet looks like, enter
the following URL:
http://<hostname>:8080/home/OE/xsl/empdept.xsl
|
| 5. |
To apply the stylesheet, enter the following URL:
http://<hostname>:8080/oradb/OE/DEPTVIEW?contenttype=text/html
&rowsettag=ROWSET
&transform=/home/OE/xsl/empdept.xsl
|
Module Summary
In this module, you should have learned how to:
Copyright © 2002 Oracle Corporation. All Rights Reserved.
Close Window
|
Bookmark
