Home Services Partners Company
XQuery for the SQL programmer – Introduction

XQuery for the SQL programmer – Introduction

June 29, 2008 0 Comments

At DataDirect we often get XQuery coding questions from SQL programmers. It can probably be explained by two main reasons. First there are many developers with a SQL background, not surprising they are looking for some analogy with SQL during their first XQuery steps. Second, with DataDirect XQuery you can use XQuery against relational databases, DataDirect XQuery is a natural inroad for SQL programmers into the world of XML and XQuery.

 

As explained last week, we have good experiences teaching XQuery. I thought it would be a good idea to start a series "XQuery for the SQL programmer" - a light introduction to XQuery from a SQL perspective. This series is not a general introduction to XQuery. We assume you have already some notions of XQuery (or XPath). You can always refresh your XQuery knowledge reading Learn XQuery in 10 Minutes: An XQuery Tutorial.

 

The following essays are available in this series. This list is updated as posts become available, bookmark it if you want to have the update-to-date and complete list at first hand.

  • Introduction (this post)
  • The Data Model
  • Joining Data
  • Operators, functions and conditions
  • Grouping and aggregation
  • Publishing data as XML
  • Updating your database
  • Using parameters
  • Why XQuery?
  • And performance?

 

This series includes a lot of examples, based on the schema of Use Case "R" from the XML Query Use Cases. It’s a simple auction system, consisting of three tables. Here is the description taken from the XML Query Use Cases document.

 

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

 

The three tables look as follows: USERS ITEMS BIDS

 

As said, this series make extensive use of examples, all the XQuery examples are fully functional and have been tested with DataDirect XQuery.

 

To get started, let’s look at a first but simple SQL query. Get the item numbers of all motorcycle items.

 

[cc lang="sql"]select ITEMNO from ITEMS where DESCRIPTION = 'Motorcycle'[/cc]

 

Or in XQuery you could write.

 

[cc lang="xquery"]for $item in collection("ITEMS")/ITEMS where $item/DESCRIPTION = "Motorcycle" return $item/ITEMNO[/cc]

 

This is a FLWOR expression, which stands for "for-let-where-orderby-return". I guess you see the analogy with a SQL select statement. Suppose we want to sort the results by item number. In SQL we add an order by clause.

 

[cc lang="sql"]select ITEMNO from ITEMS where DESCRIPTION = 'Motorcycle' order by ITEMNO[/cc]

 

In XQuery it’s similar to add an order by clause.

 

[cc lang="xquery"]for $item in collection("ITEMS")/ITEMS where $item/DESCRIPTION = "Motorcycle" order by $item/ITEMNO return $item/ITEMNO[/cc]

 

This was of course not much more than a quick introduction. In our next post we’ll start with the real work, and discuss the Relational and XML data models. Stay tuned!

Tech Tags:

Marc Van Cappellen

View all posts from Marc Van Cappellen on the Progress blog. Connect with us about all things application development and deployment, data integration and digital business.

Read next SAS ODBC Access to Salesforce, SQL Server and Marketo
Comments
Comments are disabled in preview mode.