Under the Hood: Oracle Berkeley DB XML

May 7, 2008

Deepak Vohra

Storing an XML document in a relational database has its limitations. XML's hierarchical structures of elements and element attributes do not necessarily map well to relational database structures, which is where an embeddable (non-relational) XML database has its advantages over a relational database.

Oracle databases (since Oracle 9i database R2) provide 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. On the other hand, Oracle Berkley DB XML is an embeddable XML database for storing and retrieving XML documents, one which provides efficient querying of XML documents using XQuery. Oracle Berkley DB XML is built on the embeddable Oracle Berkley DB database and inherits all the features of the database. For a comparison of Oracle Berkeley DB and relational database systems refer to A Comparison of Oracle Berkeley DB and Relational Database Management Systems.

Oracle Berkeley DB XML database stores XML data in a container. A container is managed with a XmlManager object. XML documents may be stored in a Oracle Berkeley DB XML database as whole documents or a set of nodes. It's recommended to store whole documents if the documents are relatively small and loading performance is more important than query performance. Storing in nodes is recommended if query performance is more important than loading performance and documents are relatively large. BDB XML supports XQuery 1.0 and XPath 2.0 specifications to query an XML document in the database. XML documents in a BDB XML database may be modified, updated and deleted.

Installing Oracle Berkley DB XML

You need to download Oracle Berkley DB XML 2.3.10 or a later edition. Download the Oracle Berkeley DB XML Windows Installer .msi file. To install Oracle BDB XML, double-click on the dbxml-2.3.10.msi file. The Installer gets started. Click on Next. 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 Berkley DB XML features and click on Next.

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. Berkeley DB XML 2.3.10 gets installed. Click on the Finish button. The Oracle Berkeley DB XML jar files get added to the CLASSPATH environment variable. The following bin directory gets added to the PATH environment variable.

C:\Program Files\Oracle\Berkeley DB XML 2.3.10\bin

Berkeley DB XML binaries are not available for UNIX/POSIX systems. To build Berkeley DB XML on UNIX/POSIX refer Building Berkeley DB XML for UNIX/POSIX. Berkley DB XML may be accessed from a command line shell or with a Java API. You will learn about each of these methods in the following sections.

Using the Command Shell

The dbxml shell may be started with the command shown below from a command line.

C:/>dbxml

The command shell gets started and the following prompt gets displayed.

dbxml>

Berkley DB XML commands may be specified at the dbxml command prompt. Some of the commonly used commands are discussed in Table 1.

The first thing that you will need to do once the database itself is set up is to create a container to store the XML documents. For instance, to create a container called catalog for storing catalog entries for journals, use the command:

dbxml>createContainer catalog.dbxml

A node storage container with nodes indexed gets created and opened as shown in Figure 1.

Figure 1. 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, you’ll want to add XML documents to the BDB XML database using the putDocument command. For example, to add an XML document that represents a catalog entry to the database, use the following:

dbxml>putDocument catalog1 '<catalog 
title="Oracle Magazine" publisher="Oracle Publishing"> 
 <journal date="March-April 2006"> 
  <article>
   <title>Using Bind Variables</title>
   <author> Steve Muench </author>
  </article>
 </journal>
</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. The XML document gets added; the output from the command is shown in Figure 2.

Figure 2. Adding a Document

Similarly add catalog 'catalog2'.

dbxml>putDocument catalog2 '<catalog 
title="Oracle Magazine" publisher="Oracle Publishing"> 
 <journal date="May-June 2006">
  <article> 
   <title>From Application Express to XE</title>
   <author>David A. Kelly </author>
  </article>
 </journal>
</catalog>' s

The XML documents added may be retrieved with the following command.

dbxml>getDocuments

The output from the getDocuments command is as follows.

2  documents found.

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

dbxml>print

Querying XML Documents with XQuery

Once you have created the appropriate documents, you’ll be able to query them using XQuery, an XPath based SQL-like language for XML. XQuery queries retrieve subsets of data similar to 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. For example, container 'container1.dbxml' may be navigated to with the following XQuery expression.

collection("container1.dbxml") 

Multiple containers may be queried using the '|' operator. For example, containers container1.dbxml and container2.dbxml may be queried using the following expression.

collection(("collection1.dbxml")|
("collection2.dbxml"))

The doc() function may be used to navigate to a specified XML document in a container. The doc() function takes a base URI (dbxml: by default), a container name and an XML document name in the XQuery expression. For example, XML document xmldocument1.xml in container container1.dbxml may be navigated to with the following expression.

doc("dbxml:/container1.dbxml/xmldocument1.xml")

As an example of a query select all the article titles in the catalog.dbxml container. If Berkeley DB XML 2.3.8 is used, before you may query an XML document in a container you need to set the base URI.

dbxml> setBaseUri dbxml:/

The XQuery Expression to select all the titles is as follows.

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

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

dbxml>print

The results of the query are as follows.

Using Bind Variables
From Application Express to XE

As another example retrieve the author of the article with title 'Using Bind Variables'. The XQuery expression for retrieving the author is as follows.

dbxml>query 'collection
("catalog.dbxml")/catalog/journal/article[title=
"Using Bind Variables"]/author/text()'

Output the results with the print command. The output is as follows.

Steve Muench

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. For example, to append the 'section' attribute to the 'article' elements in the XML documents in the catalog.dbxml container, use the append command:

dbxml>append './catalog/journal/article' 
'attribute' 'section' 'Developer'

The append command in the previous example 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. You will use the catalog1 container context for the subsequent modification examples.

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

Having set the context you may run an append command. As an example add after the article node in catalog1 XML document another article node. The dbxml shell command to append an article node is as follows.

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

Output the modified catalog1 document with the getDocuments command.

dbxml>getDocuments catalog1

The output from the print command is as follows:

<catalog title="Oracle Magazine" 
publisher="Oracle Publishing"> 
 <journal date="March-April 2006"> 
  <article>
   <title>Using Bind Variables </title>
   <author> Steve Muench </author>
  </article>
  <article> 
   <title> Commanding ASM</title>
   <author> Arup Nanda</author>
  </article>
 </journal>
</catalog>

Replacing/Deleting XML Documents

Using the Oracle BDB XML command shell, nodes in an XML document in the database may be replaced, renamed, and removed. For example replace the article title 'Using Bind Variables' with 'Introduction to Bind Variables'. The shell command to replace the title is as follows.

dbxml>updateNodes  
'./catalog/journal/article[title=
"Using Bind Variables"]/title'   
'Introduction to Bind Variables'

As another example, rename the journal nodes to magazine. The shell command to rename journal nodes to magazine is as follows.

dbxml>renameNodes  './catalog/journal'   
'magazine'

Next, remove the magazine node with date attribute March-April 2006. The shell command to remove the magazine node is as follows.

dbxml>removeNodes './catalog/magazine
[@date="March-April 2006"]'

An XML document may be deleted from the database with the removeDocument command. For example, remove the catalog1 document.

dbxml>removeDocument catalog1

A database container may be removed with the removeContainer command. For example, remove the catalog.dbxml container.

dbxml>removeContainer catalog.dbxml

Using the Berkley DB XML API

In BDB XML documents are stored in containers. Containers are of two types; Wholedoc container and Node container. 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. Each element in an XML document is stored as a node in the database along with the element attributes, attribute values and text nodes. BDB XML also stores information about reassembling an XML document from the nodes stored in the database. The Node container is the preferred type and is the default type. A comparison of Node container and Wholedoc container is discussed in Table 2.

A container is represented with class com.sleepycat.dbxml.XmlContainer. Configuration settings for a container are specified in the XmlContainerConfig object. An XmlManager object is used to manage many of the objects used in a BDB XML application, including managing a XmlContainer and preparing and running XQuery queries. XmlManager is the main class in a BDB XML application. In this section you will create a container, add XML documents to the container, query the XML documents with XQuery, modify the XML documents, and update the XML documents, all with the BDB XML API in the com.sleepycat.dbxml package. First, create a 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

Next, you will add XML documents to the BDB XML database. 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 am XML document and specify a document name.

String docString =
"<catalog title='Oracle Magazine' publisher=
'Oracle Publishing'>" +
"<journal date='March-April 2006'>" + 
"<article>" +
"<title>Using Bind Variables</title>" +
"<author> Steve Muench </author>" + 
"</article>" +
"</journal>" + "</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();

Store the XML document using the putDocument method.

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

Similarly, add another XML document.

Querying XML Documents with XQuery

Next, you query the XML documents in the catalog.dbxml BDXML database using XQuery. XQuery is a SQL-like query language for XML and is based on XPath. First, create a XmlQueryContext object. A XmlQueryContext object represents the context within which a XML document in a container is queried.

XmlQueryContext context = 
xmlManager.createQueryContext(); 

As an example query, 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()";

Compile the XQuery expression into an XmlQueryExpression object. using the prepare method of XmlManager class.

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

Next, evaluate the XQuery expression using execute method of XmlQueryExpression object. When the XQuery query is run the results are represented by the XmlResults object.

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, you can modify an XML document in the database, using the XmlModify class provided by BDB XML API to modify an XML document. The procedure to modify an XML document is as follows.

1. Create a 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.

First, create a XmlModify object.

XmlModify mod = xmlManager.createModify();

Also, create a XmlQueryContext object, which represents the context in which a XQuery query is performed to select nodes for modification, and an XmlUpdateContext object, which represents the context within which update operations are performed.

XmlQueryContext qc = 
xmlManager.createQueryContext();
XmlUpdateContext uc = 
xmlManager.createUpdateContext();

The XmlModify class provides various methods to modify an XML document. These methods are discussed in Table 3.

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). In the example you 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>" +

      "<title>XML in Databases </title>" +

       "<author>Ari Kaplan </author>" + 

       "</article>";

Add the journal element using the addInsertAfterStep() method.

mod.addInsertAfterStep(select, XmlModify.Element, 

"journal",objectContent);

The modification is 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);

A section attribute gets added to article element and a journal element gets added after the journal element in catalog1 document.

Replacing/Deleting XML Documents

Next, you update, rename, and delete elements in the catalog1 XML document. As an example, update the title "Using Bind Variables" to "Introduction to Bind Variables". Rename the "journal" element to "magazine". And, you delete the "journal" node added in the previous section. As the renaming of "journal" nodes is performed before the deletion of a "journal" node, the node that you actually delete is a "magazine" node. First, create XmlModify, XmlQueryContext, and XmlUpdateContext objects.

XmlModify mod = xmlManager.createModify();

XmlQueryContext qc = xmlManager.createQueryContext();

XmlUpdateContext uc = xmlManager.createUpdateContext();

Select the title text node to be updated.

XmlQueryExpression select = 

xmlManager.prepare("/catalog/journal/article

[title='Using Bind Variables']/title/text()",qc);

Specify the update content.Add a update modification to the XmlModify object using the addUpdateStep() method.

mod.addUpdateStep(select, updateContent);

String updateContent = 

"Introduction to Bind Variables";

Next, rename the "journal" nodes to "magazine". Select the "journal" nodes with an XQuery expression. Add a renaming modification to the XmlModify object using the addRenameStep() method.

mod.addRenameStep(select, "magazine");

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

Next, delete the "journal" node added in the previous section. As the "journal" nodes have been renamed to "magazine" delete a "magazine" node. Select the second "magazine" node in the catalog1 document.Add a remove modification to the XmlModify object using the addRemoveStep() method.

mod.addRemoveStep(select);

mod.addRenameStep(select, "magazine");

Obtain the catalog1 document from the BDB XML database and run the modifications on the document.

XmlDocument xmlDocument = 

xmlContainer.getDocument("catalog1");

XmlValue xmlValue = new XmlValue(xmlDocument);

 mod.execute(xmlValue, qc, uc);

A title text gets updated, "journal" node gets renamed to "magazine", and a 'magazine" node gets deleted. The BDB XML application used to access the BDB XML database is listed below.

import com.sleepycat.dbxml.XmlContainer;

import com.sleepycat.dbxml.XmlDocument;

import com.sleepycat.dbxml.XmlException;

import com.sleepycat.dbxml.XmlManager;

import com.sleepycat.dbxml.XmlModify;

import com.sleepycat.dbxml.XmlQueryContext;

import com.sleepycat.dbxml.XmlQueryExpression;

import com.sleepycat.dbxml.XmlResults;

import com.sleepycat.dbxml.XmlUpdateContext;

import com.sleepycat.dbxml.XmlValue;





public class BDBXML {

 XmlManager xmlManager = null;

 XmlContainer xmlContainer = null;



 public void createContainer() {

   try {

        xmlManager = new XmlManager();

        xmlManager.setDefaultContainerType

        (XmlContainer.NodeContainer);

        xmlContainer = xmlManager.createContainer

        ("catalog.dbxml");

        } catch (XmlException e) {

            System.out.println("XmlException" + 

            e.getMessage());

        } catch (java.io.FileNotFoundException e) {

            System.out.println("FileNotFoundException" 

            + e.getMessage());

        }

    }



    public void addDocument() {

        try {

            String docString =

                "<catalog title='Oracle Magazine' 

                publisher='Oracle Publishing'>" +

                "<journal date='March-April 2006'>" + 

                "<article>" +

                "<title>Using Bind Variables</title>" +

                "<author> Steve Muench </author>" + 

                "</article>" +

                "</journal>" + "</catalog>";



            String docName = "catalog1";



            XmlUpdateContext updateContext = 

               xmlManager.createUpdateContext();

            xmlContainer.putDocument(docName, 

               docString, updateContext, null);



      docString = "<catalog title='Oracle Magazine' 

      publisher='Oracle Publishing'>" +

                "<journal date='May-June 2006'>" + 

                "<article>" +

                "<title>From Application Express to XE

                 </title>" +

                "<author>David A. Kelly </author>" + 

       "</article>" +

                "</journal>" + "</catalog>";

            docName = "catalog2";

            xmlContainer.putDocument(docName, 

            docString, updateContext, null);

            XmlResults results = 

            xmlContainer.getAllDocuments(null);

            while (results.hasNext()) {

            XmlValue xmlValue = results.next();

            System.out.println(xmlValue.asString());

            }

        }

        catch (XmlException e) {

         System.out.println("XmlException" + 

            e.getMessage());

        }

    }



    public void queryDocument() {

        try {

            XmlQueryContext context = 

                   xmlManager.createQueryContext();



            String query = "collection

            ('catalog.dbxml')/catalog/journal/article

            /title/text()";

            XmlQueryExpression qe = xmlManager.prepare

            (query, context);

            XmlResults results = qe.execute(context);



            while (results.hasNext()) {

              XmlValue xmlValue = results.next();

              System.out.println(xmlValue.asString());

            }

        } catch (XmlException e) {

            System.out.println("XmlException" + 

            e.getMessage());

        }

    }



    public void modifyDocument() {

        try {

            XmlQueryContext qc = 

                   xmlManager.createQueryContext();

            XmlUpdateContext uc = 

                   xmlManager.createUpdateContext();

          XmlModify mod = xmlManager.createModify();

            XmlQueryExpression select = 

       xmlManager.prepare("/catalog/journal/article",

                    qc);

            mod.addAppendStep(select, 

                      XmlModify.Attribute, "section",

                "Developer");



            String objectContent = "<article>" +

                "<title>XML in Databases </title>" +

                "<author>Ari Kaplan </author>" + 

            "</article>";



            select = xmlManager.prepare("/catalog/

       journal[article/title='Using Bind Variables']",

                    qc);

       mod.addInsertAfterStep(select, 

       XmlModify.Element, "journal",objectContent);



            XmlDocument xmlDocument = 

                xmlContainer.getDocument("catalog1");

            XmlValue xmlValue = new XmlValue(xmlDocument);

            mod.execute(xmlValue, qc, uc);



       System.out.println("XML Documents after 

                                   modification");

       XmlResults results = 

                 xmlContainer.getAllDocuments(null);



            while (results.hasNext()) {

              xmlValue = results.next();

              System.out.println(xmlValue.asString());

            }

        } catch (XmlException e) {

           System.out.println("XmlException" + 

            e.getMessage());

        }

    }



    public void updateDocument() {

        try {

            XmlQueryContext qc = 

                   xmlManager.createQueryContext();

            XmlUpdateContext uc = 

                  xmlManager.createUpdateContext();

            XmlModify mod = xmlManager.createModify();



            String updateContent = "Introduction to 

            Bind Variables";

            XmlQueryExpression select = 

            xmlManager.prepare("/catalog/journal

            /article[title='Using             

              Bind Variables']/title/text()",qc);

            mod.addUpdateStep(select, updateContent);



            select = xmlManager.prepare("/catalog/

            journal", qc);

            mod.addRenameStep(select, "magazine");



            select = xmlManager.prepare("/catalog/

            magazine[2]", qc);

            mod.addRemoveStep(select);



            System.out.println("XML Documents 

            after Update");



            XmlDocument xmlDocument = 

            xmlContainer.getDocument("catalog1");

            XmlValue xmlValue = 

                         new XmlValue(xmlDocument);

            mod.execute(xmlValue, qc, uc);



            XmlResults results = 

                 xmlContainer.getAllDocuments(null);



            while (results.hasNext()) {

                xmlValue = results.next();

                System.out.println(xmlValue.asString());

            }

        } catch (XmlException e) {

            System.out.println("XmlException" + 

            e.getMessage());

        }

    }



    public static void main(String[] argv) {

        BDBXML bdbXML = new BDBXML();



        bdbXML.createContainer();

        bdbXML.addDocument();

        bdbXML.queryDocument();

        bdbXML.modifyDocument();

        bdbXML.updateDocument();

    }

}

The output from BDBXML.java is shown below.

<catalog title="Oracle Magazine" 

publisher="Oracle Publishing"><journal 

date="March-April 2006"><article>

<title>Using Bind Variables</title>

<author>Steve Muench </author></article></journal>

</catalog><catalog title="Oracle Magazine" 

publisher="Oracle Publishing"><journal 

date="May-June 2006"><article><title>

From Application Express to XE</title>

<author>David A. Kelly </author></article></journal>

</catalog>



Using Bind Variables

From Application Express to XE



XML Documents after modification



<catalog title="Oracle Magazine" publisher=

"Oracle Publishing"><journal date=

"March-April 2006"><article section="Developer">

<title>Using Bind Variables</title>

<author> Steve Muench </author>

</article></journal>

<journal><article><title>

XML in Databases</title><author>Ari

Kaplan</author></article></journal>

</catalog><catalog title="Oracle Magazine" 

publisher="Oracle Publishing"><journal date="May-June

2006"><article><title>From Application 

Express to XE</title><author>David A. Kelly 

</author></article>

</journal></catalog>



XML Documents after Update



<catalog title="Oracle Magazine" publisher=

"Oracle Publishing"><magazine date=

"March-April 2006"><article section=

"Developer"><title>Introduction to Bind 

Variables</title><author> Steve Muench </author>

</article></magazine>

</catalog>

<catalog title="Oracle Magazine" publisher=

"Oracle Publishing"><journal date="May-June 2006"

><article><title>From Application Express to XE

</title><author>

David A. Kelly </author></article></journal>

</catalog>

Conclusion

A relational database has its limitations when it comes to storing XML documents. The Berkeley DB XML database stores complete XML documents and provides the provision to query, modify and update documents without having to retrieve the documents from the database.

Oracle acquired Berkeley DB from Sleepycat Software in February 2006 and overhauled the database. Oracle Berkeley DB XML Release 2.4 supports the Candiadate Recommendation of W3C's XQuery Update 1.0.

References

• Oracle Buys Open Source Software Company Sleepycat
• Oracle Berkeley DB XML
• Oracle Berkeley DB XML Benefits

Deepak Vohra (dvohra09@yahoo.com) is a Oracle Certified Associate, Sun Certified Java Programmer and Web Component Developer. Deepak is the author of the book Ajax in Oracle JDeveloper.