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

Note: This OBE was tested on production software however some of the screenshots may still refer to the beta software.

Approximately 30 minutes.

Topics

This tutorial covers the following topics:

Overview
Prerequisites
Performing In-Place XML Schema Evolution
Summary

Viewing Screenshots

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

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

Overview

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

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.

 

Back to Topic List

Prerequisites

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

1.

Install Oracle Database 11g and make sure the OE, HR users are unlocked.
2. Set environment variables to connect to Oracle Database 11g.
3.

Download and unzip the xmldb2_a.zip file into your working directory (i.e.wkdir)

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

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;

You must set the HTTP port to 8080. Open a terminal window and execute the following statements.

sqlplus sys/oracle as sysdba;
exec dbms_xdb.sethttpport('8080');

If you have already performed the Using Oracle XML DB to Store, Query, and Access XML and Relational Data OBE, you do not need to set the HTTP port to 8080 again.

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:

For example, if your ORACLE_HOME is $ORACLE_BASE/product/11.1.0/db_1 make sure that your .bash_profile includes the following entries:

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
ORACLE_SID=orcl
PATH=$ORACLE_HOME/bin:$PATH

export PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH

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

./.bash_profile

 

Back to Topic List

Performing In-Place XML Schema Evolution

You 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

Back to Topic List

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:

 

1.

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

cd /u01/app/oracle/product/11.1.0/db_1/sqldeveloper
chmod u+x sqldeveloper.sh

sh sqldeveloper.sh
2.

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 or <hostname> if you are using a remote machine
Port: 1521
SID: orcl

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



c. The test status shows success. Click Connect.

 
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

 
4.

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.

Back to Topic

Use In-Place XML Schema Evolution

1.

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.

@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');

Back to Topic

Back to Topic List

 

 

Summary

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

Back to Topic List

Place the cursor over this icon to hide all screenshots.