Original URL: https://www.theregister.com/2007/07/18/berkeley_db_xml/

An embedded XML Database: Oracle Berkeley DB XML

There is more to life than RDBMS

By Deepak Vohra

Posted in Software, 18th July 2007 11:31 GMT

Tutorial Storing an XML document in a relational database has its limitations. XML’s hierarchical structures do not map well to relational database structures. That’s where an embeddable (non-relational) XML database has advantages over a relational database.

Oracle database (since Oracle 9i database R2) provides the Oracle XML DB feature to store and query XML documents in an XMLType data type column, but you still need a DBA to manage the Oracle database.

Oracle Berkeley DB XML, however, is an embeddable XML database specifically designed for storing and retrieving XML documents. Berkeley DB (BDB) was developed at U.C Berkeley and acquired by Oracle in February 2006; Oracle Berkeley DB XML provides efficient querying of XML documents using Xquery and does not require much administration. XQuery is an SQL-like query language for XML and is based on XPath. BDB XML supports XQuery 1.0 and XPath 2.0 specifications. Oracle Berkeley DB XML is built on top of the embeddable Oracle Berkeley DB database and inherits all the features of the database.

Installing Oracle Berkeley DB XML

We need to download Oracle Berkeley DB XML 2.3.10 Windows Installer. To install Oracle BDB XML, double-click on the dbxml-2.3.10.msi file. The Installer gets started. Click on Next.

Berkeley DB XML Windows Installer.

Accept the Open Source License and click on Next. Select the default installation folder, C:\Program Files\Oracle\Berkeley DB XML 2.3.10, and click on Next. Select the default Berkeley DB XML features and click on Next.

Berkeley DB XML Features.

Berkeley DB XML requires some environment variables (CLASSPATH and PATH) to be modified. Select the “Set the values in the environment variables” checkbox and click on Next. Click on the Install button to install Berkeley DB XML. Click on the Finish button. Berkeley DB XML may be accessed from either the command line shell or with a Java API.

Using the Command Shell

The dbxml shell may be started with the dbxml command:

C:/>dbxml

The following prompt gets displayed:

dbxml>

Berkeley DB XML commands may be specified at the dbxml command prompt. Let’s first create a container to store XML documents using the createContainer command:

dbxml>createContainer catalog.dbxml

A node storage container with nodes indexed gets created and opened (see Figure 3):

Creating a Container.

The BDB XML database is just a .dbxml file created in the directory in which the dbxml command was issued.

Adding XML Documents

Next, we’ll add XML documents to the container, using the putDocument command. For example, add an XML document that represents a catalog entry:

dbxml>putDocument catalog1 '<catalog title="Oracle Magazine" publisher="Oracle Publishing"> …
</catalog>' s

Single quotes are used for any command parameters that span multiple lines or contain spaces. The character ‘s’ used to terminate the command indicates that the XML document is added using a string.

Adding an XML Document.

Similarly add another catalog ‘catalog2’. The XML documents added may be retrieved with the getDocuments command:

dbxml>getDocuments

The documents retrieved may be output to stdout with the print command:

dbxml>print

Outputting Added Document.

Querying XML Documents with Xquery

XQuery queries retrieve subsets of data similarly to the use of SELECT statement in SQL. Each query has two parts; the first part identifies the set of documents to be queried using an XQuery navigation function such as collection() or doc(). The collection() function is used to navigate to a container. The doc() function may be used to navigate to a specified XML document in a container. As an example of a query select all the article titles in the catalog.dbxml container using the query command:

dbxml>query 'collection("catalog.dbxml")/catalog/journal/article/title/text()'

To output the results of the query use the print command:

dbxml>print
Using Bind Variables
From Application Express to XE

Modifying XML Documents

The Oracle BDB XML command shell provides various commands such as append, insertAfter and insertBefore to modify an XML document in the database. The append command won’t append an attribute if the context has not been set. The query expression for modifying nodes should be relative; it should navigate from the context item “.” rather than evaluating an expression with the collection() or doc() function. The context is set prior to the modifications with the query command. For example, set the context to the catalog.dbxml container:

dbxml> query 'collection("catalog.dbxml")'

Having set the context we may run an append command. As an example add an ‘article’ node:

dbxml> append 
 './catalog/journal[article/title="Using Bind Variables"]' 
 'element' 'article'
 '<title>Commanding 
 ASM</title><author>Arup Nanda</author>'

Retrieve the modified “catalog1” document with the getDocuments command and output the document with the print command – see Figure 6.

Outputting Modified Document.

Using the Berkeley DB XML API

In BDB XML documents are stored in containers. Containers are of two types; Wholedoc containers and Node containers. A Wholedoc container stores the complete XML document without any modifications to line breaks or whitespaces. In a Node container, XML documents are stored as nodes. BDB XML also stores information about reassembling an XML document from the nodes stored in the database. The Node container is preferred and is the default type. A comparison of Node container and Wholedoc container is discussed in the Table below:

Container Type Storage Mode Query Performance Load Performance Application
Node Container XML document stored in nodes Faster to query Lower load performance Use Node container if faster query performance is required. Use Node container if document size is more than 1MB.
Wholedoc Container Whole XML document stored Lower query performance, because complete document has to be navigated Faster document loading, because an XML document does not have to be deconstructed into nodes Use Wholedoc container if load performance is more important than query performance. Use Wholedoc container if document is relatively small and requires to be frequently retrieved.

An XmlManager object is used to manage many of the objects used in a BDB XML application, including managing an XmlContainer and preparing and running XQuery queries. Create an XmlManager object and set the default container type to be Node container.

XmlManager xmlManager = new XmlManager();
xmlManager.setDefaultContainerType(XmlContainer.NodeContainer);

Next, create a container, catalog.dbxml. The container is the BDB XML database:

XmlContainer xmlContainer = xmlManager.createContainer("catalog.dbxml");

Adding XML Documents

Similar to the putDocument shell command, the BDB XML API provides a method putDocument() to store an XML document in a database. Create a String object that represents an XML document and specify a document name:

String docString =”<catalog>  … </catalog>”;
String docName = "catalog1";

Create an XmlUpdateContext object. An XmlUpdateContext object represents the context within which update operations are performed in a container:

XmlUpdateContext updateContext = xmlManager.createUpdateContext();

Now store the XML document:

xmlContainer.putDocument(docName, docString, updateContext, null);

Similarly, add another XML document as shown in BDBXML.java in the resources.zip file.

Querying XML Documents with Xquery

Next, we’ll query the XML documents in the catalog.dbxml BDXML database using XQuery. First, we create an XmlQueryContext object representing the context within which an XML document in a container is queried:

XmlQueryContext context = xmlManager.createQueryContext();

As an example, retrieve the values of all the titles in the BDB XML database. Specify the query string that represents the XQuery expression for the query:

String query = "collection('catalog.dbxml')/catalog/journal/article/title/text()";

Now compile the XQuery expression:

XmlQueryExpression qe = xmlManager.prepare(query, context);

Next, evaluate the XQuery expression:

XmlResults results = qe.execute(context);

Iterate over the results and output the titles retrieved:

while (results.hasNext()) {
XmlValue xmlValue = results.next();
System.out.println(xmlValue.asString());
            }

The output from the query is as follows:

Using Bind Variables
From Application Express to XE

Modifying XML Documents

Next, we’ll modify an XML document in the database. BDB XML API provides the XmlModify class to modify an XML document and the procedure to modify an XML document is as follows:

  1. Create an XmlModify object.
  2. Select the nodes to be modified.
  3. Specify the modification steps. Modifications are performed in the order specified.
  4. Run the modifications in the context of an XML document or a set of XML documents.

As in the previous sections, first create an XmlModify object, an XmlQueryContext object, and an XmlUpdateContext object. As an example, add a section attribute to the article element. Select the article node using an XQuery expression:

XmlQueryExpression select = xmlManager.prepare("/catalog/journal/article",qc);

Use the addAppendStep() method to append the section attribute to article element. The type of an object to be added may be an element (represented with XmlModify.Element), an attribute (XmlModify.Attribute), a comment (XmlModify.Comment), text (XmlModify.Text), or a processing instruction (XmlModify.ProcessingInstruction). We’ll add a section attribute with value “Developer”:

mod.addAppendStep(select, XmlModify.Attribute, "section", "Developer");

Next, add a journal element after the journal element in catalog1 document. Select the journal node in catalog1 document:

XmlQueryExpression 
 select = xmlManager.prepare("/catalog/journal 
 [article/title='Using Bind Variables']",qc);

Specify the element content to be added:

String objectContent = "<article>…</article>";

Add the journal element using the addInsertAfterStep() method:

mod.addInsertAfterStep(select, XmlModify.Element, "journal",objectContent);

The modifications are not complete yet. Obtain the XML document in which the modification is to be performed:

XmlDocument xmlDocument = xmlContainer.getDocument("catalog1");

Obtain the XmlValue object for the XML document and run the modifications:

XmlValue xmlValue = new XmlValue(xmlDocument);
 mod.execute(xmlValue, qc, uc);

Similarly, elements may be updated, renamed, and deleted.

The Berkeley DB XML database has advantages over a relational database for storing complete XML documents that may be queried, modified, and updated without having to retrieve the documents from the database. ®