XML Query Languages

A number of query languages exist today. All were developed to solve one problem or another. None solve all the problems or fit all the requirements previously discussed. In fact, there are a number of competing mechanisms for processing queries. In the remainder of this section, we will look at several different query languages and see how they attempt to achieve the goals and fit the previously mentioned requirements.

XML-QL

XML-QL, or the XML Query Language, is a query language submitted to the W3C by Microsoft, Texcel, and webMethod. At its simplest, XML-QL is seen as a superset of XSL. XML-QL query statements always take the form

WHERE XML-QL pattern CONSTRUCT output
					

pattern matches a given portion of an XML document and output specifies the resulting constructed output.

For example, consider the following XML snippet, adapted from the XML-QL submission:

<bib>
    <book year="1995">
        <title>An Introduction to Database Systems</title>
        <author><lastname>Date</lastname></author>
        <publisher><name>Addison-Wesley</name></publisher>
     </book>
     <book year="1998">
        <title>Foundation for Object/Relational Databases </title>
        <author><lastname>Date</lastname></author>
        <author><lastname>Darwen</lastname></author>
        <publisher><name>Addison-Wesley</name></publisher>
     </book>
     <book year="1999">
        <title>Java 2 and Javascript for C/C++ Programmers</title>
        <author><lastname>Daconta</lastname></author>
        <author><lastname>Saganich</lastname></author>
        <publisher><name>J. Wiley and Sons</name></publisher>
     </book>
</bib>

An example query against the sample might then be

WHERE <author><lastname>Saganich</lastname></author><book>$a</book>CONSTRUCT $a

This query is based on the original input structure, and matches exactly a portion of the original data.

This query might then produce

     <book year="1999">
        <title>Java 2 and Javascript for C/C++ Programmers</title>
        <author><lastname>Daconta</lastname></author>
        <author><lastname>Saganich</lastname></author>
        <publisher><name>J. Wiley and Sons</name></publisher>
     </book>

In this example, the variable $a contains the resultset of the query.

In general, patterns and filters appear in the XML-QL WHERE clause and constructors in the CONSTRUCT clause. Let's look at a slightly more complex example:

CONSTRUCT <bib> {
    WHERE
        <bib>
             <book year=$y><title>$t</title></book>
             <publisher></name>J. Wiley and Sons</name></publisher>
             </book>
        </bib> IN "www.booksonline.com/bib.xml", $y > 1998
    CONSTRUCT <book year=$y><title>$t</title></book>
} </bib>

In this example of a nested where clause, we construct <bib>...</bib> entries from any <book> element in the specified URL with a year value of > 1998. We specifically bind the title to $t and the year to $y for each element that matches. As a result, any entries thatmatch the given pattern and have a year greater than 1998 are formatted with the end CONSTRUCT element. Basically, the query returned a set of $y and $t pairs, which are used to generate the resulting output.

We could join multiple data sources with XML-QL. For example, assume a second XML document, structured like this:

<reviews>
    <entry>
        <title>Java 2 and Javascript for C/C++ Programmers</title>
        <price>54.99</price>
        <review>An excellent book for C/C++ programmers looking to learn Java!</review>
</entry>
. . .
</reviews>

We can then structure a query that joins the two data sources:

CONSTRUCT <priced-books> {
    WHERE
        <bib>
            <book>
                <title>$t</title>
                <price>$p</price>
            </book>
        </bib> IN "www.booksonline.com/bib.xml"
        <reviews>
               <entry>
                   <title>$t</title>
                   <review>$r</review>
               </entry>
        </review> IN www.reviewsonline.com/review.xml
        ORDERBY $p
    CONSTRUCT
        <priced-book>
             <title>$t</title>
             <price>$p</price>
             <review>$r</review>
        </priced-book>
} </priced-books>

In this example, we specified that the title ($t) and the price ($p) be bound from the first database and the matching review ($r) be bound from the second database. Additionally, for spice, we specified that the result be ordered by price.

As we can see, this query satisfies all our early requirements; it joins two data sets, uses intermediate variables to store the result, and sorts the given result.

Let's look now at two products currently available: DB2XML(a free set of JDBC wrapper classes for querying XML from a database) and the Oracle database system.

DB2XML

DB2XML is a tool for transforming database tables into XML documents. DB2XML is freely available for download from http://www.informatik.fh-wiesbaden.de/~turau/db2xml. DB2XML is 100% pure Java and contains a number of servlets as well as a Java programming API. DB2XML uses JDBC to access any given database to automatically generate XML from tables supporting many of the features discussed in previous sections.

DB2XML provides support for three major areas:

• Transforming database tables into XML

• Generating DTDs from the meta-data provided via JDBC

• Transforming XML documents via XSL stylesheets

DB2XML works by generating, for each table and field, a single element. For example, in our fictional database of real estate listings we have a REListing table containing a number of fields, one of which is price. DB2XML would generate a mapping of <!ELEMENT REListing.price> for the price field. The benefit of such a mapping is that it voids the problem of name clash within an XML document. Since XML is a flat name space—that is, each element is defined only once, although it can be used any number of times—two elements can have the same name and be differentiated by context. An automated mapping from a database table to a <!ELEMENT> element could very well generate the same name twice, resulting in an error.

When a query is executed, all records resulting from that query are grouped into a single result element. The user can choose the name of the element, or else it defaults to the table name. Additionally, primary keys and foreign keys are supported via the special keywords PKEY and FORKEY. Typically, the values of these two elements are a comma-separated list of data with the element's name representing the table's primary key columns.

Records are represented as elements within a top-level table element and DB2XML contains a number of default settings so that NULL values and the like still produce valid XML.

DB2XML supports simple queries against a database. For example

[tablePrefix] select * from orders when ORDERID >1000

generates an XML document that contains the result of the given select. Within that document, tablePrefix is used as the root element with the underlying child elements being represented by the fields in the orders table.

DB2XML does not support joins across tables, other than those supported by standard SQL. Nor does DB2XML support data transformations beyond those allowed by XSLT.

Even with these limitations, DB2XML has been tested and put to use on a number of operating systems, including several UNIX variants as well as Windows NT. DB2XML has also been tested with Oracle, SQL Server, MySQL, and Access. Because it's based on JDBC, DB2XML should be able to take advantage of just about any database that has a JDBC driver.

Output from DB2XML can come as,a file, a Java Stream–based object, or a DOM document, making DB2XML an easy addition to any development effort. Future versions of DB2XML promise support for database updates as well, although this feature is not available in version 1.3.

Programming with DB2XML

DB2XML provides an entire package for developing Java-based DB2XML applications. The main class of interest when developing with DB2XML is the db2xml.xml.JDBCXML class. JDBCXML has five constructors, all of which generate an XML representation of a database query into one of several forms. The five most common versions of the constructor are

• public JDBCXML(Database db, JDBCXMLProperties props) throws DB2XMLException

  Generates a JDBCXML object from a database query and a JDBCXMLProperties object(more on this in a moment). Overrides defaults with the properties from props.

• public JDBCXML(Database db, JDBCXMLProperties props, org.w3c.dom.Document empty) throws DB2XMLException

  Generates an XML DOM Document that represents the database query. Override defaults with the properties from props. Standard DOM Document methods can be performed on the result.

• public JDBCXML(JDBCXMLProperties props, org.w3c.dom.Document empty)throws DB2XMLException

  The same as the previous constructor except use all default property values. Overrides defaults with the properties from props.

• public JDBCXML(Database db, JDBCXMLProperties props, db2xml.xml.XMLDocument empty) throws DB2XMLException

  Generates a DB2XML XMLDocument that represents the database query. DB2XML provides a limited version of the W3C DOM API. Overrides defaults with the properties from props. Standard DOM Document methods can be performed on the result.

• public JDBCXML(JDBCXMLProperties props) throws DB2XMLException.

  Generates a JDBCXML object overriding any default with those provided in props.

The JDBCXML class uses the JDBCXMLProperties helper class, which simply wraps a standard properties object and provides some convenient methods for setting, getting, and querying properties.

Listing 10.1, adapted from one of the original sources provided with DB2XML, shows a simple servlet that generates an XML document from a database using the DB2XML classes. Listing 10.2 shows the resulting XML document.

Code Listing 10.1. db2xmlservlet.java—Simple Application to Dump Table Contents Using DB2XML

 1: package sams.chp10;
 2: import db2xml.xml.*;
 3: import db2xml.util.*;
 4: import db2xml.jdbc.*;
 5: import javax.servlet.http.*;
 6: import javax.servlet.*;
 7: import java.io.*;
 8: import java.util.*;
 9:
10: public class DB2XMLServlet extends HttpServlet
11: {
12:     private JDBCXMLProperties map;
13:     public void setProperty(String key, String value)
14:     {
15:         map.setProperty(key, value);
16:     }
17:     public void init(ServletConfig config) throws ServletException
18:     {
19:         super.init(config);
20:         try
21:         {
22:             map = new JDBCXMLProperties();
23:         }
24:         catch (DB2XMLException e)
25:         {
26:             throw new ServletException(e.getMessage());
27:         }
28:     }
29:
30:     public void doGet(HttpServletRequest req, HttpServletResponse res)
31:       throws IOException, ServletException
32:     {
33:         setProperty("out", "stream");
34:         setProperty("el.binfields", "ignore");
35:         map.setBooleanProperty("el.protectStrings", true);
36:         setProperty("genDTD", "intern");
37:
38:         res.setContentType("text/xml");
39:         PrintWriter out = res.getWriter();
40:
41:         res.setStatus(HttpServletResponse.SC_OK);
42:         try
43:         {
44:           JDBCXML jx = new JDBCXML(map);
45:           jx.generateXML();
46:           XMLOutput outServ = new XMLOutput(map,out,jx.getXMLDocument());
47:           outServ.writeToOutput();
48:           out.flush();
49:           jx.closeDatabase();
50:         }
51:         catch(Exception e)
52:         {
53:           out.println("DB2XMLServlet exception: " + e);
54:           e.printStackTrace(out);
55:           out.flush();
56:         }
57:     }
58: }
59:

Code Listing 10.2. XML Document Produced by db2xmlservlet.java

<?xml version="1.0" encoding="UTF-8"?>
<!--
   Generated with DB2XML version 1.3
   http://www.informatik.fh-wiesbaden.de/~turau/DB2XML/index.html
   Database: jdbc:cloudscape:D://Personal//book//sams//chp10//xmldb
   Date: Mon Mar 13 14:53:32 EST 2000
   Driver: Cloudscape Embedded JDBC Driver 2.0
   Database system: DBMS:cloudscape  2.0.1
-->
<!DOCTYPE database [
  <!ELEMENT database (relistings)>
    <!ATTLIST database URL CDATA #REQUIRED>
  <!ELEMENT relistings (relistings_rec)*>
    <!ATTLIST relistings
             QUERY CDATA #REQUIRED
    >

XMLAnd Database Access
  <!ELEMENT relistings_rec (LISTINGBROKER, TYPE, LISTPRICE, ADDR, DESCRIPTION)>
  <!ELEMENT LISTINGBROKER (#PCDATA)>
    <!ATTLIST LISTINGBROKER
            TYPE CDATA #FIXED "CHAR"
            NAME CDATA #FIXED "LISTINGBROKER"
            ISNULL (true|false) #IMPLIED
  >
  <!ELEMENT TYPE (#PCDATA)>
    <!ATTLIST TYPE
            TYPE CDATA #FIXED "CHAR"
            NAME CDATA #FIXED "TYPE"
            ISNULL (true|false) #IMPLIED
  >
  <!ELEMENT LISTPRICE (#PCDATA)>
    <!ATTLIST LISTPRICE
            TYPE CDATA #FIXED "INT"
            NAME CDATA #FIXED "LISTPRICE"
            ISNULL (true|false) #IMPLIED
  >
  <!ELEMENT ADDR (#PCDATA)>
    <!ATTLIST ADDR
            TYPE CDATA #FIXED "CHAR"
            NAME CDATA #FIXED "ADDR"
            ISNULL (true|false) #IMPLIED
  >
  <!ELEMENT DESCRIPTION (#PCDATA)>
    <!ATTLIST DESCRIPTION
            TYPE CDATA #FIXED "CHAR"
            NAME CDATA #FIXED "DESCRIPTION"
            ISNULL (true|false) #IMPLIED
  >


]>
<database URL="jdbc:cloudscape:D://Personal//book//sams//chp10//xmldb">
 <relistings QUERY="select * from relisting"
>
  <relistings_rec>
   <LISTINGBROKER><![CDATA[Broker 1]]></LISTINGBROKER>
   <TYPE><![CDATA[land]]></TYPE>
   <LISTPRICE>10000</LISTPRICE>
   <ADDR><![CDATA[1 cheap land way]]></ADDR>
   <DESCRIPTION><![CDATA[a way cheap piece of land!]]></DESCRIPTION>
  </relistings_rec>
 </relistings>
</database>

Using DB2XML in a servlet is a relatively simple process.

First, choose whichever representation best fits your needs. In our example, we just present the generated XML to the client, so the simplest version of the JDBCXML constructor is appropriate (lines 44 and 45). Line 46 uses the db2xml.util.XMLOutput object to write the generated content to the normal output PrintWriter. XMLOutput has a number of interesting methods, so see the DB2XML documentation for a complete list. And that is all! We could have done something much more complex by obtaining an instance of a Document or XMLDocument and then using DOM methods on either to perform whatever processing was required.

DB2XML depends on a number of properties, which are loaded from a default db2xml.properties file. The DB2XML properties defaults file needs to reside in the top- level directory of your application server. If you install WebLogic Server into d:weblogic, you need to place the properties file into d:weblogicdb2xml.properties. Other application servers will have a different root but will behave similarly.

DB2XML uses a number of properties to control how it functions. You may choose all or none or some of the defaults by setting name=value pairs into the properties map using JDBCXMLProperties.setProperty(String name, String value). Some of the most common properties are listed in Table 10.1. See the DB2XML Web site for an exhaustive list of all properties. Example uses of properties are shown in Listing 10.1, lines 33–36.

DB2XML is a rudimentary but functional tool for generating XML from a JDBC database. Its strength lies in the fact that it can automatically apply a stylesheet as well as use any database that has a JDBC-compliant driver.

Using Oracle and XML

DB2XML is a publicly available free database access tool. On the other hand is the Oracle Database. Oracle is an industrial-strength database running on a large number of platforms from low-end Windows NT servers to the highest-end Sun/Sparc machines. Oracle is fast, robust, and expensive.

Oracle queries are defined by creating an XML document that looks something like this snippet:

<?xml version="1.0">
<?xml-stylesheet type="text/xml" href="somestylesheet.xsl"?>
<datapage connection="xml" cat="anyCatagory">
    <query doc-element="elementName" row-element="some row">
         select tableElements from tableName
         where whereClause
         order by orderClause
    </query>
. . .
    <query doc-elemement="anotherElement" row-element="subElement">
        another select statement...
    </query>
</datapage>

Oracle typically processes such a query via the XSQL Servlet. The processesing takes five steps:

Assuming that we have appropriate database tables, we could write a query to generate complete pages using this XML:

<?xml version="1.0">
<datapage connection="someConnection" >
    <query>
         select * from REListings
         where BrokerName like '%{ @find}%'
         order by { @sort}
    </query>
</datapage>

Assuming that the REListings table contains elements brokerName, description, and price, the XSQL servlet would generate an XML document that might look something like this:

<?xml version="1.0">
<datapage>
<relistings>
    <brokerName>A Broker</brokerName>
    <description>This is where the description should be</description>
    <price>1000000</price>
</relistings>
. . .
</datapage>

If we had specified a stylesheet, that stylesheet would have been applied by the servlet and we could have transformed the result into an HTML page or performed another appropriate transformation.

The XSQL servlet takes several possible parameters. If specified, the find='finderClause' is dynamically inserted into the where clause. Likewise, the sort='sortClause' can by used to sort the result.

The Oracle XML query language actually allows for quite sophisticated processing. A more general example follows:

<?xml version="1.0">
<?xml-stylesheet type="text/xsl" hreg="myStylesheet.xsl">
<datapage connection="someConnection" variable="value" ... variableN="valueN">
    <query doc-element="parent" row-element="child">
    <!--query to select the data

      The query could use any of the variables above
      By inserting @variableName strings into the query.
      Each of which would be run time replaced by the correct value
      as the query is processed.
      -->
    </query>
</datapage>

The Oracle database provides the following features that pertain to working with XML:

• XML-enabled object views over standard relational data— The previous examples show how existing relational data can be queried at runtime to produce well-formed XML.

• Intermediate XML searching— Oracle also supports indexing and searching XML documents and XML fragments and provides full search capabilities based on the underlying document structure using XSL-like queries.

• XML SQL utilities— Oracle provides a number of extensions to standard JDBC for inserting, deleting, updating, and querying XML from JDBC.

• Oracle XDK (developers kit)— Oracle provides a full suite of tools for developing XML-based applications such as an XML parser for C/C++, Java, and other languages. The developers kit also contains a robust XSLT transformation engine, a class generator, and a number of other tools all geared towards developing XML-enabled applications.

For a complete list of offerings, see the Oracle Web site at http://www.oracle.com.