DB2 9 and pureXML : Part 1

I'm not going to start this post by speaking about how important XML is, or how it is the backbone of the service oriented architectures of so many enterprises. I'm not going to get into this, not because I disagree with these statements, but because I think they are generally accepted notions in today's IT industry. I think what is important to talk about is how we can more efficiently work with this technology, and how we can further integrate it into the enterprise information archictecture.

IBM is not about to be left behind and they have recently released DB2 9 (code-named Viper) which includes something they have coined 'pureXML'.

XML data requires the same coveted qualities of service that relational databases provide: high availability, reliability, protection, and performance. The pureXML™ technology in DB2 9 unlocks the latent potential of XML by providing simple efficient access to XML with the same levels of security, integrity, and resiliency taken for granted with relational data. With pureXML innovation in DB2 9, your organization can expect breakthrough increases in availability, speed, and versatility of your XML information, along with dramatically reduced development and administrative costs.

With the addition of a new 'xml' datatype you can now store XML content in a table. Having this option allows you to overcome many issues that arise when working with XML. Having the ability to store XML in a table, rather than files, we can now write more scaleable applications, and deal with concurrency and security issues more efficiently.

Besides these advantages, the addition of pureXML let's us actually query the XML data that is stored in the table with regular SQL (SQL/XML a.k.a. SQL with XML Extensions) or XQuery. In this first post of a two part series, I will explain how to query data stored in an XML column of a DB2 9 table using SQL and SQL/XML.

Let's say that we have a table defined as follows:

CREATE TABLE Catalog (
   id INT PRIMARY KEY NOT NULL,
   catalogname VARCHAR(40),
   company VARCHAR(40),
   contents XML
)

Now let's say that the'contents' XML column contains the following data:

<?xml version="1.0" encoding="UTF-8"?>
<Catalog>
   <Product>
      <ProductID>100</ProductID>
      <ProductName>Columbian Coffee</ProductName>
      <ProductDescription>A bold blend of Columbia's finest beans.</ProductDescription>
      <Price>7.99</Price>
   </Product>
   <Product>
      <ProductID>200</ProductID>
      <ProductName>Kona Coffee</ProductName>
      <ProductDescription>A mild blend of Hawaii's tropical java.</ProductDescription>
      <Price>10.99</Price>
   </Product>
</Catalog>

Despite having the new XML column as part of the 'Catalog' table, you can still make regular SQL queries such as:

   SELECT * FROM Catalog
   WHERE company = 'World Coffes Inc.'

Obviously this query will return all of the rows where 'company' is equal to 'World Coffee Inc.'. Keep in mind that the data as stored in the XML column, 'contents' will also be returned by the query, letting the programmer handle parsing the markup in some traditional manner - which frankly, isn't very exciting.

But would you say if I could return the names of all of the products contained in the 'contents' XML offered by 'World Coffee Inc.'. Now that is pretty exciting!

Before we write a query to do this, let's define SQL/XML.

As the name implies, SQL/XML is designed to bridge between the SQL and XML worlds. It evolved as part of the SQL standard effort and now includes specifications for embedding XQuery or XPath expressions within SQL statements. XPath is a language for navigating XML documents to find elements or attributes. XQuery includes support for XPath.

Now, back to the query:

   SELECT xmlquery('$c/Catalog/Product/ProductName
   PASSING contents as "c")
   FROM Catalog
   WHERE company = 'World Coffee Inc.'

Now to explain what this query actually means. The first line uses the built in pureXML function, xmlquery to state what part of the XML we are interested in. We pass in the XPath query, Catalog/Product/ProductName, to specify what node we are interested in - in this case, the ProductName.

The PASSING contents as "c" simply lets us specify the table's 'contents' column as the source for the XML. The $c which is plugged in front of our XPath statement is simply a variable reference to the PASSING clause.

The rest of the query is nothing more than regular SQL syntax.

Let's look at another example where we want to return all 'catalognames' that contain products with prices over $10.

   SELECT catalogname
   FROM Catalog
   WHERE xmlexists(
      '$c/Catalog/Product[Price > 10]'
      PASSING Catalog.contents as "c"
   )

You can probably figure out the nuances of this query based on what you learned from the previous example. Something that you may not recognize is the xmlexists function which evaluates to 'true' if the XPath expression, Catalog/Product[Price > 10] is valid. So we are essentially ensuring that we are retrieving only the 'catalogname' where the product's price is greater than $10.

These two examples only scratch the surface of what can be accomplished in DB2 9 and pureXML, but I hope it has given you a taste of just how effective they can be. By downloading some of this XML processing to the database level, developers can take advantage of the database engine's power.

In Part 2 of DB2 9 and pureXML I will introduce some examples of querying XML data with XQuery and see why DB2 9 is considered a bilingual product. If you're interested in further reading, check out the IBM Redbook - DB2 9 : pureXML Overview and Fast Start.

Moving to XForms

In the early days of the world wide web, the user experience was more-or-less static - the internet was a way of sharing data among the academic community. As it matured, the web started to become more interactive and dynamic. Much of this was made possible by the user submitting information through HTML forms. Anyone familiar with HTML has no doubt come across forms.

In the early days of forms, data was usually passed to a CGI (Common Gateway Interface) program on a server, allowing programmers and webmasters to provdide the user with dynamic content. Today forms are still used, but programmers have stretched the technology, producing very complex interactions, especially in many e-commerce implementations.

Forms have come very far and have been successful in creating cutting-edge websites, but they do have limitations. The main problem with forms is that they mix presentation and content. Generally, good programmers try to separate presentation from data and behaviour (as dictated in the Model View Controller design pattern). With this lack of separation the forms will generally only render well in a web browser - forget about getting that form to look proper in a cell phone, PDA, or other devices.

In October of 2003, a W3C recommendation was made for XForms 1.0. The XForms standard is supported in many applications including Firefox, Mozilla, Seamonkey, and Internet Explorer if you install the XForms extension or related plugins. IBM defines XForms as :

...the next generation of Web-based data processing. It replaces traditional HTML forms with an XML data model and presentation elements.

W3 Schools notes these key factors of XForms.

• XForms is the next generation of HTML forms


• XForms is richer and more flexible than HTML forms


• XForms will be the forms standard in XHTML 2.0


• XForms is platform and device independent


• XForms separates data and logic from presentation


• XForms uses XML to define form data


• XForms stores and transports data in XML documents


• XForms contains features like calculations and validations of forms


• XForms reduces or eliminates the need for scripting


• XForms is a W3C Recommendation

Now that we've defined XForms, let's take a look at how it actually works through a simple example. Imagine that we had a traditional HTML form used for entering credit card information for an online order.

<html xmlns="http://www.w3.org/1999/xhtml">
   <head>
      <title>Enter Credit Card Information</title>
   </head>
   <body>
      <h1>Enter Credit Card Information</h1>
      <p>
         Enter your credit card information to complete the order.
      </p>
      <form action="http://localhost/imaginary-processing-engine" method="get">
         <p>
            Name : <input type="text" name="name" />
            <br>
            Credit Card Type :
            <select>
               <optgroup label="cardType">
                  <option value="1">Visa</option>
                  <option value="2">Mastercard</option>
                  <option value="3">American Express</option>
            </optgroup>
         </select>
         <br>
         Credit Card Number : <input type="text" name="cardNumber" />
         <br>
         Expiry Date : <input type="text" name="expiry" />
         <br>
         <input type="submit" value="Submit" />
         </p>
      </form>
   </body>
</html>

The form is pretty straight forward. It takes four data entries (Name, Credit Card Type, Credit Card Number, and Expiry Date) and submits them to an imaginary credit card processing engine running on the local server when the 'submit' button is clicked. This probably looks quite familiar to anyone who has used a traditional HTML form (I know it does to me).

When the 'submit' button is clicked the data is 'sent' to the server in a name / value pair. For example :

• name, Magic Johnson


• cardType, 2


• cardNumber, 4111111111111111


• expiry, 03-2008

This works fine - the programmer would just retrieve those values from the request object being passed to their program (such as a Java Servlet) and pass them along to the credit card processing engine. But what happens if the engine is fairly new and only understands XML messages? The programmer would have the daunting task of transforming those name / value pairs into XML. This isn't usually very fun.

That's where XForms comes to the rescue. Remember how they store and transport data in XML documents? Now your forms on your front end can send XML directly to your programs. Let's re-write our credit card form using XForms.

<?xml version="1.0" encoding="UTF-8"?>
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:xf="http://www.w3.org/2002/xforms">
   <head>
      <title>Enter Credit Card Information</title>
      <!-- form model -->
      <xf:model id="creditcardprocessing">
         <xf:instance src="creditcardorder.xml" />
         <xf:submission action="http://localhost/imaginary-processing-engine" method="get" id="submit" />
      </xf:model>
   </head>
   <body>
      <h1>Enter Credit Card Information</h1>
      <p>
         Enter your credit card information to complete the order.
      </p>
      <p>
         <!-- define inteface controls -->
         <xforms:input id="txtname" model="creditcardprocessing" ref="/CreditCardOrder/Name">
            <xforms:label>Name</xforms:label>
            <xforms:hint>Enter the name as it appears on the credit card.</xforms:hint>
         </xforms:input>
         <xforms:select1 model="creditcardprocessing" ref="/CreditCardOrder/CardType" appearance="full">
            <xforms:label>Credit Card Type</xforms:label>
            <xforms:hint>Select the type of credit card. </xforms:hint>
            <xforms:item>
               <xforms:label>Visa</xforms:label>
               <xforms:value>1</xforms:value>
            </xforms:item>
            <xforms:item>
               <xforms:label>Mastercard</xforms:label>
               <xforms:value>2</xforms:value>
            </xforms:item>
            <xforms:item>
               <xforms:label>American Express</xforms:label>
               <xforms:value>3</xforms:value>
            </xforms:item>
         </xforms:select1>
         <xforms:input id="txtcardnumber" model="creditcardprocessing" ref="/CreditCardOrder/CardNumber">
            <xforms:label>Credit Card Number</xforms:label>
            <xforms:hint>Enter the credit card number.</xforms:hint>
         </xforms:input>
         <xforms:input id="txtexpirydate" model="creditcardprocessing" ref="/CreditCardOrder/ExpiryDate">
            <xforms:label>Expiry Date</xforms:label>
            <xforms:hint>Enter the credit card expiry date (MM-YYYY).</xforms:hint>
         </xforms:input>
         <xforms:submit submission="submit">
            <xforms:label>Submit</xforms:label>
            <xforms:hint>Submit the order.</xforms:hint>
         </xforms:submit>
      </p>
   </body>
</html>

In the <head> element we define the form model and give it an 'id' to which we can refer to later. The <xf:instance src="creditcardorder.xml" /> node defines the data we are collecting. As you can see, I am referring to an external document called 'creditcardorder.xml'. This file looks like:

   <?xml version="1.0" encoding="UTF-8"?>
   <CreditCardOrder>
      <Name />
      <CardType />
      <CardNumber />
      <ExpiryDate />
   </CreditCardOrder>

This is what the XML will look like when we submit the form (but will not include empty tags - actual data will be in there). We could have also defined this XML structure inline, but I prefer having it in a separate file.

The <xf:submission action="http://localhost/imaginary-processing-engine" method="get" id="submit" /> node allows us to define how the form submission will be handled. As you've probably guessed, it includes the URL to where the form is to be submitted, as well as the submission method. Note that you can replace the URL with an actual file location, allowing you to save the form XML to a file. This could be handy if you want to let the user save a partially entered form that they can return to at a later time.

Let's take a look at how we actually build the form that will be rendered. The XForms standard provides a number of different form elements, such as text fields, radio buttons, checkboxes, etc. My example makes use of the <input>, <select1>, and <submit> controls. XML.com put together a nice table cross-referencing the traditional form controls to XForms controls. I suggest you take a look at it here.

Take notice of the ref attribute in each form control. It's value is an XPath - /CreditCardOrder/Name. The data that is entered for this form element by the user will be 'plugged' into the XML structure we defined in the XForm model. So if the user enters 'Magic Johnson', that value will be injected into the XML document at the referenced XPath, when the form is submitted.

One thing to note in the <xforms:submit submission="submit"> node is that we are referencing the xf:submission model we defined in the <head> with an id="submit". The significance of this is that we could have defined multiple models that submit to different servers. Or as I stated earlier, we could have included an additional button called 'save' that would save the form data to a file. Traditional forms only allow one submission scheme per form.

So how does this all come together? Well, if we were to enter the same data as I illustrated earlier in my name / value pairs, submitting the form would send an XML document to the server to be processed. That document would look like :

<?xml version="1.0" encoding="UTF-8"?>
   <CreditCardOrder>
   <Name>Magic Johnson</Name>
   <CardType>2</CardType>
   <CardNumber>4111111111111111</CardNumber>
   <ExpiryDate>03-2008</ExpiryDate>
</CreditCardOrder>

Now when our XML enabled program receives the request, no ugly transformations from name / value pairs have to be made. We can pass the XML straight through to our credit card processing engine.

As you can see through my simple example, XForms are a necessity in a software environment that has quickly, and decisively adopted XML. The XForms standard lets us create an abstraction between a form's data model and its presentation. It also adds the ability to integrate with XML schemas, providing simple, and accurate, data validation (which I didn't illustrate). I encourage you to read more about XForms, and start to think about integrating this technology into your current web solutions.

Here are some links I found quite useful while experimenting with XForms:

XForms Tutorial at W3Schools
XForms Basics at DevShed.com
What are XForms at XML.com
Introduction to XForms at IBM DeveloperWorks