DB2 9.1 XML/XQuery support

DB2 9.1 XML/XQuery support

I investigated the XML and XQuery support in the latest RDBMS from IBM: DB2 9.1. The XML tooling in DB2 9.1 is called pureXML. I was quite happy with the way in which SQL and XML are integrated in DB2 9.1.

DB2 9.1 provides support for XML data type in SQL (which is now part of the ISO/IEC SQL specification). We can now define table columns, of type XML in DB2. This makes it possible to store an entire XML document in a particular field of a table. Within the field, the XML is stored in it's native hierarchical form (the XML is stored in a native XML store, whereas the pointer to the XML document is kept in the field).

The SQL and XML are now closely tied in DB2. We can write XML access code in SQL, which allows many interesting operations to be performed. XML within the database can be accessed using XQuery (either directly, or from within SQL).

Following are the three ways in which XML data can be accessed within DB2:

1) Through XQuery only
2) XQuery that can invoke SQL
3) Through SQL only

I developed a sample application in DB2 9.1, illustrating the features of XML and XQuery in the database. Following are the steps (along with screenshots) to design this application.

Step 1: Create a database with XML support
Use DB2 control center to do this.

Following is a screen shot to create a database in DB2 9.1:

The option, "Enable database for XML" must be selected.

Step 2: Register a XML Schema with XML Schema Repository (XSR)
Since we will be storing XML in the database (in a table named 'CUSTOMER'), we want to validate the XML document with a Schema each time we insert or update the XML data. Only a valid XML document can be stored in the database.

To do this, we must register the XML Schema in the database.

Store the Schema somewhere in the file system (I kept it on the Windows desktop).

The example Schema I used is (named customer.xsd):

<?xml version="1.0" encoding="UTF-8"?>
    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
        <xs:element name="customer">
            <xs:complexType>
                <xs:sequence>
                    <xs:element name="name" type="xs:string" />
                    <xs:element name="address">
                        <xs:complexType>
                            <xs:sequence>
                                <xs:element name="street" type="xs:string" />
                                <xs:element name="city" type="xs:string" />
                                <xs:element name="state" type="xs:string" />
                                <xs:element name="pin" type="xs:string" />
                                <xs:element name="country" type="xs:string" />
                            </xs:sequence>
                        </xs:complexType>
                    </xs:element>
                </xs:sequence>
                <xs:attribute name="id" type="xs:string" />
            </xs:complexType>
    </xs:element>
</xs:schema>

The XML Schema Repository looks like following (this is an empty XSR):

Perform following steps from DB2 Command Line Processor.

db2 => connect to sampledb

Database Connection Information

Database server = DB2/NT 9.1.0
SQL authorization ID = GANDHIMU
Local database alias = SAMPLEDB

db2 => register xmlschema 'http://mukul/customer' from 'file://C:/Documents and Settings/gandhimu/Desktop/customer.xsd' as gandhimu.customerschema
DB20000I The REGISTER XMLSCHEMA command completed successfully.

db2 => complete xmlschema gandhimu.customerschema
DB20000I The COMPLETE XMLSCHEMA command completed successfully.

Please note: The two commands, register xmlschema and complete xmlschema must be performed in sequence to register a Schema in XSR.

Now a Schema is registered in the XSR (which looks like below):

Step 3: Create a table having column of type XML
The table structure is shown below:

Please note: The column 'INFO' is of type XML.

Step 4: Store records in the table
Keep XML documents to be stored in the table somewhere (I kept it on the Windows desktop).

Following is a sample XML file (named customer.xml):

<?xml version="1.0" encoding="UTF-8"?>
<customer id="1000">
    <name>XYZ Ltd.</name>
    <address>
        <street>DLF City, Phase 2</street>
        <city>Gurgaon</city>
        <state>Haryana</state>
        <pin>122001</pin>
        <country>India</country>
    </address>
</customer>

To insert records in the table, we'll use a Java program. Following is a sample Java program, which will insert records in the table:

import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.sql.Connection;
import java.io.FileInputStream;
import java.io.File;

public class testXMLDb {

    public static void main(String[] args) {
        try {
            PreparedStatement insertStmt = null;
            String sqls = null;
            int cid = 1000;

            sqls = "INSERT INTO CUSTOMER (id, info) VALUES (?, XMLVALIDATE(? ACCORDING TO XMLSCHEMA ID gandhimu.customerschema))";

            String url = "jdbc:db2://localhost:50000/SAMPLEDB";

            Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
            Connection conn = DriverManager.getConnection(url, "uid", "pwd");

            insertStmt = conn.prepareStatement(sqls);
            insertStmt.setInt(1, cid);
            File file = new File("C:\\Documents and Settings\\gandhimu\\Desktop\\customer.xml");
            insertStmt.setBinaryStream(2, new FileInputStream(file), (int)file.length());
            insertStmt.executeUpdate();
        }
        catch(Exception ex) {
            ex.printStackTrace();
        }
    }

}

Please note: The insert statement uses a clause, XMLVALIDATE, which will cause validation of the XML document using the Schema stored in XML Schema Repository.

Insert two records in the table (for this sample). The populated table looks like following in the DB2 control center:

Step 5: Retrieve data from the table using XQuery
Following are the two SQL query examples:

1) SELECT id
FROM customer c
WHERE XMLCAST(XMLQUERY('$cust/customer/name/text()' PASSING c.info AS "cust") AS VARCHAR(100)) = 'XYZ Ltd.'

2) SELECT XMLCAST(XMLQUERY('$cust/customer/name/text()' PASSING c.info AS "cust") AS VARCHAR(100))
FROM customer c
where id = 1000

The 1st query retrieves a normal traditional SQL column. The where clause compares data stored in XML document with a normal BIGINT field.

The 2nd query retrieves the customer name stored in the XML document (in the column named INFO).

This illustrates that XQuery and SQL are closely tied in DB2.

Notes: To update XML data in DB2 9.1, we have to update the entire XML document (commonly in the client application, like Java). The upcoming DB2 version 9.5 supports the emerging W3C standard, "XQuery Update facility", which will allow updations of XML document in the database itself, at a fine grained level like element or attribute.

2008-11-03: I tried some of the XQuery update features in DB2 9.5, which is now available for the community. I found the XQuery update facility in DB2 9.5 to be quite good.

If you wish to know how XQuery update facility works in DB2, please read this article:
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0710nicola/.

Conclusion:
This hybrid SQL/XML database concept is a useful facility, and a paradigm shift in relational and XML storage, which will allow many useful applications to be built. I feel that software applications should exploit the combined power of SQL and XML into their applications.

References:
1. http://www-01.ibm.com/software/data/db2/
2. http://www-01.ibm.com/software/data/db2/xml/

Home

Last Updated: Nov 15, 2008