Lesson 1: 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 40 minutes.

Topics

This tutorial covers the following topics:

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

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.

Prerequisites

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

1.	Check your Oracle Database 11g installation and make sure the OE, HR users are unlocked.
2.	Check your Oracle SQL Developer (version 1.2.1) installation
3.	Check your Oracle Jdeveloper 10.1.3 installation
4.	Check the files in your working directory (/HOL/xmldb_1/files)

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://localhost:1158/em Enter the following details, and accept the default value for Connect As. User Name: system Password: oracle 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.

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.

Reviewing the XML Schema in JDeveloper

You need to perform the following tasks:

	Create a WebDAV Connection
	Review the XML Schema

Create a WebDAV Connection

Before you access the XML Schema documents in XML DB, you need to create a WebDAV connection. Perform the following steps:

Click on the JDeveloper icon on the desktop to start the application

Click the Connections Navigator tab. In the Connections window, right-click WebDAV Server, and select New WebDAV Connection...

3.	The Create WebDav Connection window appears. Enter the following details, then click Test Connection. URL location: localhost:8080/ Connection Name: WebDavConnection1 User Name: oe Password: oe

4.	The test connection status shows successful. Click OK.

5.	Expand WebDavConnection1 connection that you just created.

Review the XML Schema

Now you can review the XML Schema . Perform the following steps:

1.	Expand home - OE - PurchaseOrders - 2002 - Apr. In the list, right click the first document ,and select Open. This opens the XML document.

2.	Scroll down in the Connections window, and right-click purchaseOrder.xsd. Then, select Open.

3.	The XML Schema is shown in a graphical layout. Expand PurchaseOrder.

4.	Expand Actions.

5.	Expand Action and you see that there are two type objects, User and Date.

6.	Now, go back to the XML Document to see the definition. Click the XML Document tab. Note that the Action object contains a user type of KPARTNER.

Creating a 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

Start SQL Developer

Perform the following steps:

Click on the SQL Developer icon on the desktop to start the application.

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
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.

3.	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.

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 create_s1_bix_table.sql. 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 SECUREFILE BINARY XML
(compress cache);
/
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.

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 your SQLDeveloper session, connect as OE user. Then, execute the script createXMLIndexes.sql.

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')
/

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.sql. 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.sql:
--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.sql , and click the Autotrace icon. Note the usage of XMLIndex index.

Code in XQuery03b.sql:
-- 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)
/

View the execution plan of the query that reviews all the purchase orders having a particular part id.

a. Open the file XQuery04a.sql. The code is displayed in the Enter SQL Statement box. Now, click the Autotrace icon. Note the usage of IPARTNUMBER.

Code in XQuery04a.sql:
--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.sql, and click the Autotrace icon. Note the usage of XMLIndex index.

Code in XQuery04b.sql:
--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)
/

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.sql. The code is displayed in the Enter SQL Statement box. Now, click the Autotrace icon.

Code in XQuery05a.sql:
-- 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.sql. The code is displayed in the Enter SQL Statement box. Now, click the Autotrace icon.

Code in XQuery05b.sql:
--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)
/

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.sql. The code is displayed in the Enter SQL Statement box. Now, click the Autotrace icon.

Code in XQuery06b.sql:
--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.

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.sql

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.sql.

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.

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.sql.

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
/

2.	Now you can use SQL queries on the relational view. Execute the script queryXMLTableView.sql. 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.

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

Lesson 2: Performing In-Place XML Schema Evolution

Purpose

This tutorial shows you how to make changes to an XML schema without requiring that existing data to be copied, deleted, and reinserted.

Time to Complete

Approximately 20 minutes.

Topics

This tutorial covers the following topics:

	Overview
	Prerequisites
	Performing In-Place XML Schema Evolution
	Summary

Overview

Schema Evolution

A major challenge for developers using an XML schema with Oracle XML DB is how to deal with changes in the content or structure of XML documents. In some environments, the need for changes may be frequent or extensive, arising from new regulations, internal needs, or external opportunities. For example, new elements or attributes may need to be added to an XML schema definition, a data type may need to be modified, or certain minimum and maximum occurrence requirements may need to be relaxed or tightened.

In such cases, you need to "evolve" the XML schema so that new requirements are accommodated, while any existing instance documents (the data) remain valid (or can be made valid), and existing applications can continue to run.

If you do not care about any existing documents, you can of course simply drop the XMLType tables that are dependent on the XML schema, delete the old XML schema, and register the new XML schema at the same URL. In most cases, however, you need to keep the existing documents, possibly transforming them to accommodate the new XML schema.

Oracle XML DB supports two kinds of schema evolution. Each approach has its own PL/SQL procedure: DBMS_XMLSCHEMA.copyEvolve for copy-based evolution and DBMS_XMLSCHEMA.inPlaceEvolve for in-place evolution, which is introduced in the Oracle Database 11g release.

In-place Schema Evolution

In-place XML schema evolution makes changes to an XML schema without requiring that existing data be copied, deleted, and reinserted. In-place evolution is therefore much faster than copy-based evolution. In general, in-place evolution is permitted if you are not changing the storage model and if the changes do not invalidate existing documents (that is, if existing documents are conformant with the new schema or can be made conformant with it).

In-place XML schema evolution constructs a new version of an XML schema by applying changes specified in a diffXML document, validates that new XML schema (against the XML schema for XML schemas), constructs DDL statements to evolve the disk structures used to store the XML instance documents associated with the XML schema, executes these DDL statements, and replaces the old version of the XML schema with the new,in that order.

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.	Check your Oracle SQL Developer (version 1.2.1) installation
3.	Check the files in the working directory (/HOL/xmldb_2/files) for this lesson.

Note: If you use an earlier version of Oracle JDeveloper, the screenshots may sligthly differ.

Performing In-Place XML Schema Evolution

You will use the DBMS_XMLSCHEMA.inPlaceEvolve procedure to perform in-place XML schema evolution. Using this procedure, you identify the changes to be made to an existing XML schema by specifying an XML schema-differences document.

Perform the following tasks:

	Start SQL Developer
	Use In-Place XML Schema Evolution

Start SQL Developer

In this tutorial, you use the SQL Developer tool. After creating a database connection, you set autotrace parameters and script pathing reference in SQL Developer. Perform the following steps:

Click on the SQL Developer icon on the desktop to start the application.

In SQL Developer, 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
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.

3.	Set the Autotrace parameters. Perform the following steps: a. Go to Tools > Preferences. b.Expand Database, and select Autotrace Parameters. c. In the Preferences window, make sure to select the following check boxes. Then, click OK. Object_Name Cost Cardinality

To run the scripts by using the @ syntax, you can set the script pathing reference in SQL Developer. Perform the following steps:

a. Select Tools > Preferences > Database > Worksheet Parameters. Then, click Browse.

b. Browse to the location of your working directory that has the SQL scripts. Then, click Open.

c In the Preferences window, verify the script path in the Select default path to look for scripts field. Click OK.

In the above section, you learned how to connect to SQL Developer, set autotrace parameters and set script pathing reference.

Use In-Place XML Schema Evolution

Create a new version of an XML schema from an existing one. Execute the script createSchemaV2.sql.

@createSchemaV2.sql

declare
new_schema xmltype;
res boolean;
begin
if (dbms_xdb.existsResource('/home/OE/purchaseOrder.v2.xsd')) then
dbms_xdb.deleteResource('/home/OE/purchaseOrder.v2.xsd');
end if;
select appendChildXML (xdbUriType('/home/OE/purchaseOrder.xsd').getXML(),
'/xs:schema/xs:complexType[@name="LineItemType"]/xs:sequence',
xmltype('<xs:element xmlns:xs="http://www.w3.org/2001/XMLSchema" name="Unit" type="xs:string" minOccurs="0" />'),
'xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb"')
into new_schema from dual;
res := dbms_xdb.createResource('/home/OE/purchaseOrder.v2.xsd',new_schema);
commit;

end;
/

To execute a SQL script in SQL Developer, you can use the @ syntax. Then, click Run Script or press [F5].

Alternatively, right-click in the Enter SQL Statement box to open the file. Then, click Run Script or press [F5].

2.	Insert a new XML document conforming to the new version of the XML schema. Execute the script insertNewXMLV2.sql. Observe the error in the script output pane. @insertNewXMLV2.sql declare new_xml xmltype; res boolean; begin select column_value into new_xml from xmltable('for $i in ora:view("PURCHASEORDER") where $i/PurchaseOrder/Reference = "SBELL-2002100912333601PDT" return $i'); select appendChildXML (new_xml, '/PurchaseOrder/LineItems/LineItem[@ItemNumber="1"]', xmltype('<Unit>Box</Unit>')) into new_xml from dual; select appendChildXML (new_xml, '/PurchaseOrder/LineItems/LineItem[@ItemNumber="2"]', xmltype('<Unit>Carton</Unit>')) into new_xml from dual; select appendChildXML (new_xml, '/PurchaseOrder/LineItems/LineItem[@ItemNumber="3"]', xmltype('<Unit>Case</Unit>')) into new_xml from dual; select updateXML (new_xml, '/PurchaseOrder/Reference/text()', 'SBELL-2002100912333601PDT-V2') into new_xml from dual; insert into PURCHASEORDER values new_xml; commit; end; /
3.	Now, perform in-place XML schema evolution. Execute evolveSchema.sql. Notice the xmlDiff SQL function call in the script that compares the differences between the old and the new schemas. @evolveSchema.sql set timing on declare schemaDiff XMLType; res boolean; begin select xmlDiff ( xdburitype('/home/OE/purchaseOrder.xsd').getXML(), xdburitype('/home/OE/purchaseOrder.v2.xsd').getXML() ) into schemaDiff from dual; if (dbms_xdb.existsResource('/home/OE/poSchemaDiff.xml')) then dbms_xdb.deleteResource('/home/OE/poSchemaDiff.xml'); end if; res := dbms_xdb.createResource('/home/OE/poSchemaDiff.xml',schemaDiff); dbms_xmlschema.inPlaceEvolve('http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd', schemaDiff, 1); end; /
4.	Try inserting again the a new XML document conforming to the new version of the XML schema. Execute the script insertNewXMLV2.sql. Observe that there are no errors this time. @insertNewXMLV2.sql declare new_xml xmltype; res boolean; begin select column_value into new_xml from xmltable('for $i in ora:view("PURCHASEORDER") where $i/PurchaseOrder/Reference = "SBELL-2002100912333601PDT" return $i'); select appendChildXML (new_xml, '/PurchaseOrder/LineItems/LineItem[@ItemNumber="1"]', xmltype('<Unit>Box</Unit>')) into new_xml from dual; select appendChildXML (new_xml, '/PurchaseOrder/LineItems/LineItem[@ItemNumber="2"]', xmltype('<Unit>Carton</Unit>')) into new_xml from dual; select appendChildXML (new_xml, '/PurchaseOrder/LineItems/LineItem[@ItemNumber="3"]', xmltype('<Unit>Case</Unit>')) into new_xml from dual; select updateXML (new_xml, '/PurchaseOrder/Reference/text()', 'SBELL-2002100912333601PDT-V2') into new_xml from dual; insert into PURCHASEORDER values new_xml; commit; end; /
5.	Verify that the new XML document has been successfully inserted. Execute verifyEvol.sql. @verifyEvol.sql select column_value from XMLTable ('for $i in ora:view("PURCHASEORDER") where exists($i/PurchaseOrder/LineItems/LineItem/Unit) return $i/PurchaseOrder/Reference');

Summary

In this tutorial, you learned how to use in-place XML schema evolution.

Lesson 3: Using Oracle XML DB Web Services for Service-Oriented Architecture

Purpose

This tutorial also shows you how to use Oracle XML DB Web services for Service-Oriented Architecture.

Time to Complete

Approximately 20 minutes.

Topics

This tutorial covers the following topics:

	Overview
	Prerequisites
	Creating a Binary XML Table
	Using Oracle XML DB Web Services for Service-Oriented Architecture
	Summary

Overview

Oracle XML DB Web Services

Web Services have become an industry- standard way for both exchanging information and giving access to business logic. The new Oracle XML DB web services capability provides access to SQL, XQuery, PL/SQL, and other database elements as a web service. For example, one web service allows users to issue SQL and XQuery queries and receive the results as XML, and another can provide access to all PL/SQL functions and procedures stored inside the DB as web services.

Prerequisites

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

1.	Check your Oracle Database 11g installation and make sure the OE, HR users are unlocked.
2.	Check your Oracle SQL Developer (version 1.2.1) installation
3.	Check your Oracle Jdeveloper 10.1.3 installation
4.	Check the files in your working directory (/HOL/xmldb_3/files)

Note: If you use an earlier version of Oracle JDeveloper, the screenshots may sligthly differ.

Creating a Binary XML Table

In this tutorial, you use the SQL Developer tool. After creating a database connection, you set the script pathing reference in SQL Developer. You create a binary XML table and insert rows with data selected from the PURCHASEORDER table.

Perform the following steps:

Click on the SQL Developer icon on the desktop to start the application.

In SQL Developer, 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
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.

To run the scripts by using the @ syntax, you can set the script pathing reference in SQL Developer. Perform the following steps:

a. Select Tools > Preferences > Database > Worksheet Parameters. Then, click Browse.

b. Browse to the location of your working directory that has the SQL scripts. Then, click Open.

c In the Preferences window, verify the script path in the Select default path to look for scripts field. Click OK.

Run the script create_sl_bix_table.sql.

@create_sl_bix_table.sql

DROP TABLE po_sl_bix_table PURGE;

CREATE TABLE PO_SL_BIX_TABLE OF XMLType
XMLType STORE AS SECUREFILE BINARY XML
(compress cache)
/
INSERT INTO po_sl_bix_table
SELECT object_value FROM purchaseorder;
/

SELECT COUNT(*) FROM po_sl_bix_table
/

COMMIT
/

In the above section, you learned how to connect to SQL Developer and set script pathing reference. You also learned how to create an XMLType table with binary XML storage, and populate the table with data.

Using Oracle XML DB Web Services for Service-Oriented Architecture

You can implement a service-oriented architecture using a new web service endpoint native to Oracle XML DB. Perform the following tasks:

Enable the database-native Web service end point in Oracle XMLDB

Develop a Web service client application

View Web service request and response using Http Analyzer

Enabling the database-native Web service end point in Oracle XML DB

Open a terminal window and enter the following commands:

cd wkdir
sqlplus sys/oracle as sysdba

Enable the database-native Web service endpoint in Oracle XML DB. Execute the following script:

@setWebServiceEvent11g.sql

create or replace procedure addServletMapping (pattern varchar2,
servletname varchar2,
dispname varchar2,
servletclass varchar2,
servletschema varchar2,
language varchar2,
description varchar2,
securityRole xmltype) as
xdbconfig xmltype;
begin
xdbconfig := dbms_xdb.cfg_get();
select deleteXML
(
xdbconfig,
'/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list/servlet[servlet-name="' || servletname || '"]'
)
into xdbconfig
from dual;

if (language = 'C') then
select insertChildXML
(
xdbconfig,
'/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list',
'servlet',
xmlElement
(
"servlet",
xmlAttributes('http://xmlns.oracle.com/xdb/xdbconfig.xsd' as "xmlns"),
xmlForest
(
servletname as "servlet-name",
language as "servlet-language",
dispname as "display-name",
description as "description"
),
securityRole
)
)
into xdbconfig
from dual;
else
select insertChildXML
(
xdbconfig,
'/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list',
'servlet',
xmlElement
(
"servlet",
xmlAttributes('http://xmlns.oracle.com/xdb/xdbconfig.xsd' as "xmlns"),
xmlForest
(
servletname as "servlet-name",
language as "servlet-language",
dispname as "display-name",
description as "description",
servletclass as "servlet-class",
servletschema as "servlet-schema"
)
)
)
into xdbconfig
from dual;
end if;

select deleteXML
(
xdbconfig,
'/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-mappings/servlet-mapping[servlet-name="' || servletname || '"]'
)
into xdbconfig
from dual;

select insertChildXML
(
xdbconfig,
'/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-mappings',
'servlet-mapping',
xmltype
(
'<servlet-mapping xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">
<servlet-pattern>'||pattern||'</servlet-pattern>
<servlet-name>'||servletname||'</servlet-name>
</servlet-mapping>'
)
)
into xdbconfig
from dual;

dbms_xdb.cfg_update(xdbconfig);

end;
/

call addServletMapping(
'/orawsv/*',
'orawsv',
'Oracle Query Web Service',
null,
null,
'C',
'Web Services Servlet',
xmltype(
'<security-role-ref>
<role-name>XDB_WEBSERVICES</role-name>
<role-link>XDB_WEBSERVICES</role-link>
</security-role-ref>'
)
)
/

call addServletMapping(
'/orawsdl/*',
'orawsdl',
'Oracle WSDLs',
null,
null,
'C',
'WSDL Servlet',
xmltype(
'<security-role-ref>
<role-name>XDBWEBSERVICES</role-name>
<role-link>XDBWEBSERVICES</role-link>
</security-role-ref>'
)
)
/

grant XDB_WEBSERVICES to oe
/

-- For 11g only
grant XDB_WEBSERVICES_OVER_HTTP to oe
/
grant XDB_WEBSERVICES_WITH_PUBLIC to oe
/

-- Clean up afterward
drop procedure addServletMapping
/

Developing a Web service client Application

1.	Switch to JDeveloper.In the navigator window, click the Add to Applications icon.

In the Select application(s) to add window, go to your <working directory>/DBWS directory. Then, select OBE_DBWS.jws, and click Open.

At this point, if you see a warning window, click Yes. You may see this warning because of the application migration to the latest JDeveloper version file format.

3.	Right-click OBE_DBWS, and select New Project...

4.	The New Gallery window appears. From the Items list, select Empty Project, and click OK.

In the Create Project window, enter XQueryWS for Project Name. Accept the default Directory Name, and click OK.
Note: The default directory name will be <working directory>/DBWS/XQueryWS.

Expand OBE_DBWS. Right-click XQueryWS, and select New...

7.	In the Categories section, Expand Business Tier, and select Web Services.

8.	The list of Items is displayed in the right window pane. Select Web Service Proxy, and click OK.

9	This launches a Create Web Service Proxy wizard, in which you generate a java proxy for calling the web service. Click Next.

10.	You should select a WSDL document containing service descriptions, and then choose service in the document to create a proxy for. Browse in your <working directory>, and select orawsdl.xml file. Then, click Open.

11.	Click Next.
12.	The Building Model window pops up for a while and shows the building model status for service orawsdl.xml.The next step is to select the endpoint URL that the proxy will use to accept the ports of the service. Accept the default selection of the radio button Run against a service deployed to Embedded OC4J. Click Next.
13.	Click Next.
14.	In this step, you see ORAWSVPort under the Ports node. Click Next.
15.	Accept the default mapping options, and select the Reuse Existing Type Classes check box. Click Next.
16.	Click Next. Then, click Finish. You have finished creating your proxy. Now, you see the Generation in Progress window.

17.

JDeveloper generates code for invoking web services defined in the WSDL file. In this lesson, the main client program file is the ORAWSVPortClient.java.

Open the file ORAWSVPortClient.txt. Use a text editor to copy the content of this file. In the JDeveloper window, paste this code after the line that reads //Add your own code here.

myPort.setUsername("oe");
myPort.setPassword("oe");
String endPoint = myPort.getEndpoint();
xqueryws.proxy.Query_text xq_text = new Query_text();
xqueryws.proxy.Query xq = new Query();
xq_text.setType(xqueryws.proxy.Query_textType.XQUERY); xq_text.set_value("ora:view(\"PO_SL_BIX_TABLE\")/PurchaseOrder/Reference");
xq.setQuery_text(xq_text);
System.out.println("Invoking the web service");
SOAPElement res = myPort.XMLFromQuery(xq);
System.out.println("Done. You can now check your result with HTTP Analyzer.");

18.	To compile the code successfully, you should import javax xml.soap.SOAPElement.At this point, you receive a code assist in JDeveloper to import javax.xml.soap.SOAPElement.Press Alt-Enter to import.

19.	Now, expand the import..., node. You see the javax.xml.soap.SOAPElement added.

20.

In the navigator, expand OBE-DBWS > XQueryWS > Application Sources > xqueryws.proxy. Click ORAWSVServiceProxy to see the structure at the bottom window pane.

21.

In the ORAWSVServiceProxy-Structure window pane, double-click ORAWSVBinding_Stub.java.

22.	The code in ORAWSVBinding_Stub.java is displayed in the right window. Make sure all occurences of the ENDPOINT_ADDRESS_PROPERTY are set to http://localhost:8080/orawsv.

23.	Now, switch to ORAWSVPortClient.java window pane. Right-click on the code, and select Make. At the bottom of the window, Messages- Log shows successful compilation.

View Web service request and response using Http Analyzer

To view the Webservice request and response using HTTP Analyzer, first, you should set HTTP Analyser settings in JDeveloper.

In the JDeveloper window, go to Tools, and select Preferences...

In the Preferences window, select Web Browser and Proxy. Make sure of the following, and click OK.

Use default HTTP Proxy Server check box is selected
Host Name is localhost
Port Number is 8099.

3.	In the JDeveloper window, go to View, and select Http Analyzer.
4.	The Http Analyzer appears at the bottom of the window. Click the Start Http Analyzer icon.
5.	Now, you run the project. In the JDeveloper window, go to Run, and select Run XQueryWS.jpr.
6.	In the Choose Default Run Target Window that appears, perform the following steps: a. Click Browse. b.Go to <working directory>/DBWS/XQueryWS/src/xqueryws/proxy. Then, select ORAWSVPortClient.java, and click Open. c. Click OK.

In the Log window that appears at the bottom, you see the following message.

calling http://localhost:8080/orawsv
Invoking the web service
Done. You can now check your result with HTTP Analyzer.
Process exited with exit code 0.

Switch to Http Analyzer at the bottom window pane. Double-click on the last item.

Alternatively, you can select the last item and click Data.

9.	In the Http Analyzer window, the bottom left pane displays the web service request, and the bottom right pane displays the response.

Reset the Proxy Server Setting in JDeveloper

1.	In the “Preferences” dialog box, select the “Web Browser and Proxy” from the list, and make sure the “Use HTTP Proxy Server” checkbox is unchecked.

Summary

In this tutorial, you learned how to:

	Create a binary XML table
	Review the XML Schema in JDeveloper
	Use XML DB Web Services for Service-Oriented Architecture

Lesson 4: Using Binary XML and XMLIndex to Aggregate and Query Unstructured XML Data Sources

Purpose

This tutorial shows you how to use binary XML storage model, XMLIndex, XQuery, SQL/XML, and many other salient capabilities of Oracle XML DB to aggregate and query unstructured XML data sources.

Time to Complete

Approximately 40 minutes

Topics

This tutorial covers the following topics:

	Overview
	Prerequisites
	Setting Environment Variables.
	Creating a new user and grant necessary privileges
	Creating a table to store information about RSS feeds
	Creating a table to store RSS news items
	Creating an XMLIndex on News Items
	Creating an RSS View with Aggregated and Normalized News Items from Diverse News Feeds
	Reading the Normalized RSS Feed with a Firefox 2.0 Browser
	Cleaning up the machine
	Summary

Overview

Oracle Database 11g XML dB introduces a new binary XML storage model, a new XMLIndex index, and many other salient new capabilities for high performance storage and retrieval of structured and unstructured XML documents. This tutorial uses a real world use case of an RSS feed aggregator to demonstrate these versatile and high performance capabilities.

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.

Prerequisites

Before you perform this tutorial, you should:

1.	Check your Oracle Database 11g installation
2.	Check your Firefox browser version 2.0 installation
3.	Check your Oracle SQL Developer 1.2.1 installation
4.	Check the files in your working directory (/HOL/xmldb_4/files)

NOTE: To accommodate the hands-on lab environment (i.e., no internet access), all the steps have been pre-run during installation to populate news items. You should focus on examining the scripts and running the queries.

Creating a new user and grant necessary privileges

Using the following steps you would create a new user and use the new security mechanism in Oracle Database 11g to grant the new user access to internet.

1.	Connect as user system 1. Open SQL Developer. 2. In the Connections tab select New Connection. 3. Connect as system/oracle.

2.	Open and execute the script 1_1_CreateUser.sql.

3.	Open and execute 1_2_httpAccessPriv.sql create a new Internet access control list (ACL) and assigned to the new user.

Creating a table to store information about RSS feeds

An RSS (Real Simple Syndication) feed is an URL pointing to an XML document in one of the many variants of RSS formats. The XML document of an RSS feed contains news items. In this section, you will create a table to store information about subscribed RSS feeds.

1.	Create a connection with user rss/rss Click on the “Connect” button to connect as user RSS.

2.	As user RSS, execute the script 2_1_CreateFeedsTable.sql to create a new rssfeeds table.

3.	Open and execute the script 2_2_InsertFeeds.sql to insert a list of news feeds into the rssfeeds table.

4.	Open and execute the script 2_3_VerifyFeeds.sql to verify that a list of news feeds have been inserted.

Creating a table to store RSS news items

Different RSS formats used different XML structures to represent news items. This type of highly variable XML structures are best stored with the binary XML storage model. By taking advantage of the binary XML storage model along with the SECUREFILE LOB storage format, you can gain improved performance while reducing storage space.

1.	As user RSS, execute the script 3_1_CreateRSSItemsTable.sql to create a new newsItemsTable. Notice the creation of “virtual columns” to enforce unique constraints. Also notice how Oracle XML dB can handle different namespaces defined by different RSS formats.

Open 3_2_InsertRSSItems.sql. Depending on whether you are accessing the Internet via a proxy server, modify the script to change the parameter of the utl_http.set_proxy() call.

You can now execute 3_2_InsertRSSItems.sql to insert news items into the newsItemsTable. Notice how the script uses the XMLTable() function to extract news items for insertion into the newItemsTable.

4.	With a unique constraint on the virtual column of news item title, Oracle XML DB can enforce the uniqueness of a news item title. You can execute 3_3_InsertTestItems.sql to insert news items with duplicate item titles to confirm the existence of a unique constraint on news item title.

Creating an XMLIndex on News Items

With news items stored with binary XML storage model, creating an XMLIndex on news item titles will provide much improved query performance. To allow full text searches on news item titles, a secondary text index can be created on the path table of XMLIndex.

1.	As user RSS, execute the script 4_CreateIndexes.sql to create an XMLIndex using a subset of XPath pointing to news item titles. A secondary text index is also created. Notice how Oracle XML DB handles multiple namespaces used by different RSS formats.

Creating an RSS View with Aggregated and Normalized News Items from Diverse News Feeds

The main function of a RSS (Real Simple Syndication) aggregator is to aggregate and normalize news items from multiple feeds using diverse RSS formats. In this section, you will see how you can create an RSS view normalizing diverse RSS formats.

As user RSS, execute the script 5_1_CreateRSSViews.sql to create a relational view newsItemsRelView which normalizes news items using diverse RSS formats. Notice the UNION ALL clause in the SQL statement for creating this view. Also notice the usage of ora:contains() XQuery function for full text searches on news item titles. An XMLType view newsItemsRSSView is also created to complete the normalization of diverse news feed RSS formats into a single RSS 2.0 format.

2.	Open and execute 5_2_ExplainRssViews.sql to see if the SQL/XML queries in the normalized views result in optimized query execution where the XMLIndex and the secondary text index are used.

3.	Open and execute 5_3_CreateRSSResouce.sql to create an Oracle XML dB repository resource to represent a normalized RSS feed with aggregated and normalized news items from diverse RSS feeds.

Reading the Normalized RSS Feed with a Firefox 2.0 Browser

There are several news readers capable of processing RSS 2.0 format. This section uses Firefox 2.0 browser to read the normalized RSS feed.

You can open a Firefox 2.0 browser and point to the URL http://localhost:8080/home/RSS/news2007.xml. You will see a list of news items aggregated from multiple news feeds. Enter rss/rss when prompted for User Name and Password.

Note :The screen shot will be different from what a user gets due to changes in the news.

Cleanup

1.	As user System, execute the script cleanup.sql

Summary

In this tutorial, you learned about the following:

Creating a new user and grant necessary privileges
Creating a table to store information about RSS feeds
Creating a table to store RSS news items
Creating an XMLIndex on News Items
Creating an RSS View with Aggregated and Normalized News Items from Diverse News Feeds

Table of Content

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

Purpose

Time to Complete

Topics

Lesson 2: Performing In-Place XML Schema Evolution

Purpose

Time to Complete

Topics

Lesson 3: Using Oracle XML DB Web Services for Service-Oriented Architecture

Purpose

Time to Complete

Topics

Prerequisites

View Web service request and response using Http Analyzer

Reset the Proxy Server Setting in JDeveloper

Lesson 4: Using Binary XML and XMLIndex to Aggregate and Query Unstructured XML Data Sources

Purpose

Time to Complete

Topics

Prerequisites

Cleanup