Oracle XML capabilities in Oracle Database 11gR2, part 2

In the second and final part of this column on XML capabilities in Oracle Database 11gR2, columnist Jeff McCormick details how XML data can be used in the database and queried to gain business information. 

This is Part 2 of a two-part series on Oracle XML capabilities in Oracle Database 11gR2. In part 1, McCormick introduced the reader to XML capabilities in Oracle Database and set up a scenario where a business wanted to pull XML documents into its database. In this part, McCormick details how that XML data can then be used and queried to gain business information.

Now that our XML and relational structures are defined, we can query and manipulate the XML data. Notice the following insert, update and delete code statements operate directly against the XML table. To completely abstract XML coding from SQL applications, you can create “Instead of” triggers on relational views for these data manipulation language (DML) operations.

First, XML data is ingested directly into the XML table using an SQL insert statement with a XML Type function. XML documents can be complex. A single XML insert can replace dozens of relational inserts when dealing with a normalized data model.

 insert into abc_purchase_order values
(xmltype(
'<?xml version="1.0"?>
<PurchaseOrder>
<OrderNumber>11111</OrderNumber>
<OrderDate>2010-01-15</OrderDate>
<CustomerName>Joe Smith</CustomerName>
<User>JSMIT</User>
<SpecialInstructions>Air Mail</SpecialInstructions>
<LineItems>
<LineItem ItemNumber="1">
<Description>Bubble Gum</Description>
<Part Id="7155" UnitPrice="19.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="2">
<Description>Blow Pops</Description>
<Part Id="3742" UnitPrice="15.95" Quantity="2"/>
</LineItem>
</LineItems>
</PurchaseOrder>'
));

Next, our SQL application can search and retrieve our purchase order elements by querying our previously created relational view. Remembering our master-detail structure, there will be one row for each PurchaseOrder (XML document) and one row for each LineItem (collection) within the XML document. With our relational view, we now view the XML data relationally as columns and rows in tables.

The explain plan shows that our structured XMLIndex index has been used to optimize the query on the relational view. Notice also that our purchase order table has been referenced (Id 8). This is to support partition-wise joins and partition pruning of our partitioned table and index. As mentioned earlier, had we created a non-partitioned purchase order table, the query result would have come entirely from the underlying XMLIndex content table.

 select order_number
,order_date
,customer_name
,description
,partno
,quantity
,unitprice
from abc_po_master_detail_view
where customer_name = 'Joe Smith';
ORDER_NUMBER ORDER_DATE CUSTOMER_NAME DESCRIPTION PARTNO QUANTITY UNITPRICE
------------ ---------- ------------- ----------- ------ -------- ---------
11111 15-JAN-10 Joe Smith Bubble Gum 7155 2 19.95
11111 15-JAN-10 Joe Smith Blow Pops 3742 2 15.95
-- Explain (statistics have been previously gathered)
select * from table(dbms_xplan.display_cursor(null,null,'allstats cost last'));
------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | PX COORDINATOR | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | | |
| 3 | NESTED LOOPS | | | |
| 4 | NESTED LOOPS | | 1 | 2 (0)|
| 5 | NESTED LOOPS | | 1 | 2 (0)|
| 6 | PX BLOCK ITERATOR | | 1 | 2 (0)|
|* 7 | TABLE ACCESS FULL | ABC_PO_IDX | 1 | 2 (0)|
|* 8 | TABLE ACCESS BY USER ROWID | ABC_PURCHASE_ORDER | 1 | 1 (0)|
|* 9 | INDEX RANGE SCAN | SYS108405_108413_PKY_IDX | 1 | 0 (0)|
| 10 | TABLE ACCESS BY GLOBAL INDEX ROWID| ABC_PO_LINEITEM_IDX | 1 | 1 (0)|
------------------------------------------------------------------------------------------------

Oracle XML database also supports piecewise updates of our purchase order. The modification can either be an update to an element’s content or a replacement of an element altogether. The following UPDATE SQL statements are two examples of changing the SPECIALINSTRUCTIONS element text node value. Notice the WHERE clause can be coded with an XMLExists function or the previously created virtual column. Be sure to balance the simplicity of virtual columns with potential performance gains of direct XML functions.

 -- Use XMLExists function
UPDATE abc_purchase_order po
SET po.OBJECT_VALUE =
updateXML(po.OBJECT_VALUE, '/PurchaseOrder/SpecialInstructions/text()', 'Priority Overnight')
WHERE XMLExists('$po2/PurchaseOrder[OrderNumber="11111"]'PASSING OBJECT_VALUE AS "po2");
-- Use virtual column
UPDATE abc_purchase_order
SET OBJECT_VALUE =
updateXML(OBJECT_VALUE, '/PurchaseOrder/SpecialInstructions/text()', 'Priority Overnight')
WHERE Order_Number=11111;

Lastly, analogous to relationally removing rows from a table, we can remove our XML structured purchase order. Here we use a virtual column for coding simplicity.

 delete from abc_purchase_order where order_number = 11111;

Archive and purge XML data

Having sent XML formatted purchase orders downstream by extracting them from a single XML table as opposed to several relational tables with an additional transformation step, we are now ready to support our ILM strategy. Our date-based partitioning scheme is used to migrate our now inactive purchase orders to lower tiered, less expensive storage for a period of time before removing the data to meet regulatory and compliance requirements.

 -- Archive Data
alter table abc_purchase_order move partition  yr_2010 tablespace <tablespace_name>;
-- Purge Data
alter table abc_purchase_order drop partition yr_2010 update global indexes;

Jeff McCormick is an architecture director at a major health service company and president of the Connecticut Oracle User Group. McCormick has worked in IT for more than 20 years as a data and infrastructure architect/administrator.He holds several Oracle, Microsoft and Sybase professional certifications.

This was first published in February 2011

Dig deeper on Oracle XML

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close