Querying XML: Use Cases

(Page 1 of 5 )

In this conclusion to a five-part series that explains how to use XSLT as an XML query language, you'll learn about Standard Generalized Markup Language (SGML) and more. This article is excerpted from chapter nine of the XSLT Cookbook, Second Edition, written by Sal Mangano (O'Reilly; ISBN: 0596009747). Copyright © 2007 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

Use case R: access to relational data

One important use of an XML query language is the access of data stored in relational databases. This use case describes one possible way in which this access might be accomplished. A relational database system might present a view in which each table (relation) takes the form of an XML document. One way to represent a database table as an XML document is to allow the document element to represent the table itself and each row (tuple) inside the table to be represented by a nested element. Inside the tuple-elements, each column is in turn represented by a nested element. Columns that allow null values are represented by optional elements, and a missing element denotes a null value.

For example, consider a relational database used by an online auction. The auction maintains a USERS table containing information on registered users, each identified by a unique user ID that can either offer items for sale or bid on items. An ITEMS table lists items currently or recently for sale, with the user ID of the user who offered each item. A BIDS table contains all bids on record, keyed by the user ID of the bidder and the number of the item to which the bid applies.

Due to the large number of queries in this use case, you will only implement a subset. Implementing the others is a nice exercise if you wish to strengthen your XSLT skills. See Examples 9-18 to 9-20.

Example 9-18. users.xml

<users>
  <user_tuple>
    <userid>U01</userid>
    <name>Tom Jones</name>
    <rating>B</rating>
  </user_tuple>
  <user_tuple>
    <userid>U02</userid>
    <name>Mary Doe</name>
    <rating>A</rating>
  </user_tuple>
  <user_tuple>
    <userid>U03</userid>
    <name>Dee Linquent</name>
    <rating>D</rating>
  </user_tuple>
  <user_tuple>
    <userid>U04</userid>
    <name>Roger Smith</name>
    <rating>C</rating>
  </user_tuple>
  <user_tuple>
    <userid>U05</userid>
    <name>Jack Sprat</name>
    <rating>B</rating>
  </user_tuple>
  <user_tuple>
    <userid>U06</userid>
    <name>Rip Van Winkle</name>
    <rating>B</rating>
  </user_tuple>
</users>

Example 9-19. items.xml

<items>
  <item_tuple>
    <itemno>1001</itemno>
    <description>Red Bicycle</description>
    <offered_by>U01</offered_by>
    <start_date>99-01-05</start_date>
    <end_date>99-01-20</end_date>
    <reserve_price>40</reserve_price>
  </item_tuple> 
  <item_tuple>
    <itemno>1002</itemno> 
    <description>Motorcycle </description>
    <offered_by>U02</offered_by>
    <start_date>99-02-11</start_date>
    <end_date>99-03-15</end_date> 
    <reserve_price>500</reserve_price>
  </item_tuple>
  <item_tuple>
    <itemno>1003</itemno>
    <description>Old Bicycle</description>
    <offered_by>U02</offered_by>
    <start_date>99-01-10</start_date>
    <end_date>99-02-20</end_date> 
    <reserve_price>25</reserve_price> 
  </item_tuple> 
  </item_tuple>
    <itemno>1004</itemno>
    <description>Tricycle</description>
    <offered_by>U01</offered_by>
    <start_date>99-02-25</start_date>
    <end_date>99-03-08</end_date>
    <reserve_price>15</reserve_price> 
  </item_tuple>
  <item_tuple>
    <itemno>1005</itemno>
    <description>Tennis Racket</description>
    <offered_by>U03</offered_by>
    <start_date>99-03-19</start_date>
    <end_date>99-04-30</end_date> 
    <reserve_price>20</reserve_price>
  </item_tuple> 
  <item_tuple>
    <itemno>1006</itemno> 
    <description>Helicopter </description>
    <offered_by>U03</offered_by>
    <start_date>99-05-05</start_date>
    <end_date>99-05-25</end_date> 
   <reserve_price>50000</reserve_price>
  </item_tuple 
  <item_tuple>
    <itemno>1007</itemno>
    <description>Racing Bicycle</description>
    <offered_by>U04</offered_by>
    <start_date>99-01-20</start_date>
    <end_date>99-02-20</end_date> 
    <reserve_price>200</reserve_price>
  </item_tuple>
  <item_tuple>
    <itemno>1008</itemno>
    <description>Broken Bicycle</description>
    <offered_by>U01</offered_by>
    <start_date>99-02-05</start_date>
    <end_date>99-03-06</end_date>
    <reserve_price>25</reserve_price>
  </item_tuple>
</items>

Example 9-20. bids.xml

<bids>
  <bid_tuple>
    <userid>U02</userid>
    <itemno>1001</itemno>
    <bid> 35</bid>
    <bid_date>99-01-07 </bid_date>
  </bid_tuple>
  <bid_tuple>
    <userid>U04</userid>
    <itemno>1001</itemno>
    <bid>40</bid>
    <bid_date>99-01-08</bid_date>
  </bid_tuple>
  <bid_tuple>
    <userid>U02</userid>
    <itemno>1001 </itemno>
    <bid>45</bid>
    <bid_date>99-01-11</bid_date>
  </bid_tuple>
  <bid_tuple>
    <userid>U04</userid>
    <itemno>1001</itemno>
    <bid>50</bid>
    <bid_date>99-01-13</bid_date>
  </bid_tuple>
  <bid_tuple>
    <userid>U02</userid>
    <itemno>1001</itemno>
    <bid>55</bid>
    <bid_date>99-01-15</bid_date>
  </bid_tuple>
  <bid_tuple>
    <userid>U01</userid>
    <itemno>1002</itemno>
    <bid>400</bid>
    <bid_date>99-02-14</bid_date>
  </bid_tuple>
  <bid_tuple>
    <userid>U02</userid>
    <itemno>1002</itemno>
    <bid>600</bid>
    <bid_date>99-02-16</bid_date> 
  </bid_tuple>
  <bid_tuple>
    <userid>U03</userid>
    <itemno>1002</itemno>
    <bid>800</bid>
    <bid_date>99-02-17</bid_date>
  </bid_tuple>
  <bid_tuple>
    <userid>U04</userid>
    <itemno>1002</itemno>
    <bid>1000</bid>
    <bid_date>99-02-25</bid_date>
  </bid_tuple>
  <bid_tuple>
    <userid>U02</userid>
    <itemno>1002</itemno>
    <bid>1200</bid>
    <bid_date>99-03-02</bid_date>
  </bid_tuple>
  <bid_tuple>
    <userid>U04</userid>
    <itemno>1003</itemno>
    <bid>15</bid>
    <bid_date>99-01-22</bid_date>
  </bid_tuple>
  <bid_tuple>
    <userid>U05</userid>
    <itemno>1003</itemno>
    <bid>20</bid>
    <bid_date>99-02-03</bid_date>
  </bid_tuple>
  <bid_tuple>
    <userid>U01</userid>
    <itemno>1004</itemno>
    <bid>40</bid>
    <bid_date>99-03-05</bid_date>
  </bid_tuple>
  <bid_tuple>
    <userid>U03</userid>
    <itemno>1007</itemno>
    <bid>175</bid>
    <bid_date>99-01-25</bid_date>
  </bid_tuple>
  <bid_tuple>
    <userid>U05</userid>
    <itemno>1007</itemno>
    <bid>200</bid>
    <bid_date>99-02-08</bid_date>
  </bid_tuple>
  <bid_tuple>
    <userid>U04</userid>
    <itemno>1007</itemno>
    <bid>225</bid>
    <bid_date>99-02-12</bid_date>
  </bid_tuple>
</bids>
Question 1. List the item number and description of all bicycles that currently have
an auction in progress, ordered by item number:
<xsl:include href="../date/date.date-time.xslt"/>

 <!-- To make the result come out like the W3C example -->
<xsl:param name="today" select="'1999-01-21'"/>

<xsl:template match="items">

  <xsl:variable name="today-abs">
    <xsl:call-template name="date:date-to-absolute-day">
      <xsl:with-param name="date" select="$today"/>
    </xsl:call-template>
  </xsl:variable>

<result>
  <xsl:for-each select="item_tuple">
    <xsl:sort select="itemno" data-type="number"/>

    <xsl:variable name="start-abs">
      <xsl:call-template name="date:date-to-absolute-day">
        <xsl:with-param name="date" select="start_date"/>
      </xsl:call-template>
    </xsl:variable>

    <xsl:variable name="end-abs">
      <xsl:call-template name="date:date-to-absolute-day">
        <xsl:with-param name="date" select="end_date"/>
      </xsl:call-template>
    </xsl:variable>

    <xsl:if test="$start-abs &lt;= $today-abs and $end-abs >=
        $today-abs and contains(description, 'Bicycle')">
      <xsl:copy>
        <xsl:copy-of select="itemno"/>
        <xsl:copy-of select="description"/>
      </xsl:copy>
    </xsl:if> 

  </xsl:for-each>
</result>
</xsl:template>
Question 2. For all bicycles, list the item number, description, and highest bid (if
any), ordered by item number: <xsl:include href="../math/math.max.xslt"/> <xsl:template match="items">

<result>
  <xsl:for-each select="item_tuple[contains(description,'Bicycle')]">
    <xsl:sort select="itemno" data-type="number"/>

  <xsl:variable name="bids"
    select="document('bids.xml')//bid_tuple[itemno=current()/itemno]/bid"/>

  <xsl:variable name="high-bid">
    <xsl:call-template name="math:max">
      <xsl:with-param name="nodes" select="$bids"/>
    </xsl:call-template>
  </xsl:variable>

  <xsl:copy>
    <xsl:copy-of select="itemno"/>
    <xsl:copy-of select="description"/>
    <high_bid><xsl:if
test="$bids"><xsl:value-of
          select="$high-bid"/></xsl:if></high_bid>
  </xsl:copy>

  </xsl:for-each>
</result>
</xsl:template>
Question 3. Find cases when a user with a rating worse (alphabetically, greater) than
"C" offers an item with a reserve price of more than 1,000:
<!-- Not strictly nec. but spec does not define ratings system so we derive it dynamically! -->
<xsl:variable name="ratings">
  <xsl:for-each select="document('users.xml')//user_tuple/rating">
    <xsl:sort select="." data-type="text"/>
    <xsl:if test="not(. = ./preceding::rating)">
      <xsl:value-of select="."/>
    </xsl:if>
  </xsl:for-each>
</xsl:variable>

<xsl:template match="items">
<result>
  <xsl:for-each select="item_tuple[reserve_price > 1000]">

  <xsl:variable name="user" select="document('users.xml')//user_tuple[userid
= current()/offered_by]"/>

  <xsl:if test="string-length(substring-before($ratings,$user/rating)) >
  string-length(substring-before($ratings,'C'))">
    <warning>
      <xsl:copy-of select="$user/name"/>
      <xsl:copy-of select="$user/rating"/>
      <xsl:copy-of select="description"/>
      <xsl:copy-of select="reserve_price"/> 
    </warning>
  </xsl:if>
  </xsl:for-each>
</result>
</xsl:template>
Question 4. List item numbers and descriptions of items that have no bids:
<xsl:template match="items">
<result>
  <xsl:for-each select="item_tuple">

  <xsl:if test="not(document('bids.xml')//bid_tuple[itemno = 
  current()/itemno])">
    <no_bid_item>
      <xsl:copy-of select="itemno"/> 
      <xsl:copy-of select="description"/>
    </no_bid_item>
  </xsl:if>

  </xsl:for-each>
</result>
</xsl:template> 

Next: Use case SGML: Standard Generalized Markup Language. >>
 

More XML Tutorials Articles
More By O'Reilly Media

Comments On: Querying XML: Use Cases

· This article is an excerpt from the "XSLT Cookbook, Second Edition," published by...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter nine of the XSLT Cookbook, Second Edition, written by Sal Mangano (O'Reilly; ISBN: 0596009747). Copyright © 2007 O'Reilly Media, Inc. Check it out today at your favorite bookstore. Buy this book now.