Oracle XML capabilities in Oracle Database 11gR2, part 1

New Oracle XML capabilities in Oracle Database 11gR2 allow for easier reading and manipulation of XML documents, according to columnist Jeff McCormick. In part 1, McCormick introduces the reader to XML capabilities in Oracle Database and sets up a scenario where a business wants to pull XML documents into its database.

By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.

You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.

the reader to XML capabilities in Oracle Database and sets up a scenario where a business wants to pull XML documents into its database. Part 2 details how that XML data can then be used and queried to gain business information.

The use of XML data continues to increase across enterprises, and databases will play a role in XML data management. Yet many Oracle database professionals have been reluctant to adopt Oracle XML capabilities into their management practices.

That should change with Oracle Database 11gR2. Oracle XML features in the newest version of its database product make it easier to introduce XML into your data management practices with minimal disruption to SQL applications. In this data integration use case, we will demonstrate relational access of XML data while pointing out new Oracle Database 11gR2 improvements along the way.

Relational access of XML data from Oracle Database 11gR2

XML data integration, which has become increasingly more common in IT shops, is relatively easy to implement in the database. It is still normal to find XML documents shredded into relational tables for SQL application access. Shredding is expensive, time consuming and oftentimes unnecessary. A better approach may be to eliminate the shredding step by directly ingesting the XML data into the database and placing relational views over the XML structures.

The following fictitious example demonstrates the use of native Oracle XML database capabilities supporting relational access of XML data.

The ABC Gum Company’s data integration hub receives source system purchase orders as XML documents, as shown below. The company is looking for a simple solution within Oracle Database to accept well-formed XML documents and present them relationally. Its SQL-based hub maintenance application can then perform data manipulation operations prior to the data being published to downstream XML applications, archived and eventually purged.

We’ll begin by creating a single schema-less XML table to store our complex purchase order structure.Notice the table is created as an XML Type data type. You can also create a table with an XML Type column. So far so good, as we’ve already simplified a multi-table relational data model to a single physical table.

Then we’ll add structured and unstructured indexes on commonly queried portions of our XML document to provide increased performance. New to Oracle 11gR2, structured XMLIndex components use relational content tables (relational index structures) to organize structured XML content. For non-partitioned tables, defining a XMLIndex index to include all fields in a relational view will ensure that a query result of a relational view query will come entirely from the underlying XMLIndex content table, theoretically achieving relational performance. Additionally, standard relational secondary indexes can be created on the columns of the structured index effectively indexing the index for even faster performance.

Jeff McCormick is an architecture director at a major health service company and president of theConnecticut 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.

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy