Page 2 of 2   « previous page

Xerces-J is my personal favorite. I used it for parsing the sample XML file shown in Figure 1. You might use a DOM parser instead of a SAX parser. A DOM parser will scan the XML and construct the object model for the whole document in the memory, creating a structure similar to Figure 2. Generally speaking, it's more convenient to work with DOM parsers, because they make the whole object model of the XML available in the memory. For large documents this is a problem because an object models of a big XML document may not fit into the main memory, therefore I prefer working with SAX parsers.

Querying XML Using SQL
Most of the RDBMSs do not support XQuery or XPath. In this section, I will show you how to use SQL for responding to some XPath queries. After shredding the XML document we have the table shown in Figure 5.

In the following examples I will offer several XPath queries and convert them to SQL queries. Note that each parent-child or ancestor-descendant relation corresponds to a self join of the Edge table (that's joining Edge table with itself). The representation in Figure 5 is not the most space efficient one, but it can handle mixed XML content nicely. Although values of the attributes and elements do not have unique names, note that we named them as "text" for better performance.

<book isbn='XY023'>
    <title>XML in Use</title>
    <authors>
      <author>Author1</author>
      <author>Author2</author>
    </authors>
</book>

Example 1: Find the title of the book.

XPath:
/book/title/text()

SQL:
select
    a.value
from
    Edge a, Edge b, Edge c
where
    a.name = 'text' and
    a.parentid = b.id and
    b.name = 'title' and
    b.parentid = c.id and
    c.name = 'book' and
    c.id = 1

There are two self joins for two parent-child relations: the first one is between "title" and its text, and the second one is between the root element "book" and "title."

Example 2: Find all of the authors of the book.

XPath:
/book/authors/author/text()

SQL:
select
    a.value
from
    Edge a, Edge b, Edge c, Edge d
where
    a.name = 'text' and
    a.parentid = b.id and
    b.name = 'author' and
    b.parentid = c.id and
    c.name = 'authors' and
    c.parentid = d.id and
    d.name = 'book' and
    d.id = 1

Example 3: This example is similar to Example 2. It demonstrates the use of lastdesc column. It skips the "authors" element by using // (double slash) instead. Find all of the authors of the book.

XPath:
/book//author/text()

SQL:
select
    a.value
from
    Edge a, Edge b, Edge c
where
    a.name = 'text' and
    a.parentid = b.id and
    b.name = 'author' and
    b.id > c.id and b.id <= c.lastdesc and
    c.name = 'book' and
    c.id = 1

Note that in this example we have b.id greater than c.id and less than or equal to c.lastdesc. That's because all of the descendants of c (root "book") will be between c.id and c.lastdesc.

Acknowledgements
Thanks to Glenn Hoffman for reading and commenting on this article.

Page 2 of 2   « previous page