Before you start

About this series

This tutorial series teaches basic to advanced SQL and basic XQuery topics and shows how to express commonly asked business questions as database queries by using SQL queries or XQueries. Developers and database administrators can use this tutorial to enhance their database query skills. Academic Initiative members can use this tutorial series as a part of their database curriculum.

All the examples in this document are based on Aroma, a sample database that contains sales data for coffee and tea products sold in stores across the United States. Each example consists of three parts:

• A business question, expressed in everyday language
• One or more example queries, expressed in SQL or XQuery
• A table of results returned from the database

This guide is designed to allow participants to learn the SQL language and XQuery. As with any learning, it is important to supplement it with hands-on exercises. This is facilitated by the table definitions and data.

For students using this as part of an academic class, obtain from your instructor the instructions to connect to the Aroma database and learn about any differences between the guide and your local set up.

This tutorial was written for DB2 Express-C 9 for UNIX®, Linux® and Windows® (formerly known as Viper).

Back to top

About this tutorial

This tutorial introduces readers to pureXML and XQuery. The only place XML is used in the Aroma database is the Comments column. This column was mentioned in Part 1 of this series and it is the focus of this tutorial, part 7.

This tutorial starts out with a basic overview of the characteristics and advantages of the XML data type, and then compares it with standards relational tables. It then asks readers to write XQueries to retrieve XML elements, filter data based on XML values, transform XML output, and use various clauses to select data more precisely. The tutorial ends with a section on mixing XQuery and SQL and combining the power of the two languages.

Back to top

Connecting to a database

You need to connect to a database before you can use SQL statements to query or manipulate data. The CONNECT statement associates a database connection with a user name.

Find out from your instructor the database name that you will need to be connected to. For this series, the database name is aromadb.

To connect to the aromadb database, type the following command in the DB2 command line processor:

CONNECT TO aromadb USER userid USING password

Replace the user ID and password with the user ID and password that you received from your instructor. If no user ID and password are required, simply use the following command:

		
CONNECT TO aromadb

The following message tells you that you have made a successful connection:

Database Connection Information
Database server      = DB2/NT 9.0.0
SQL authorization ID = USERID
Local database alias = AROMADB

Once you are connected, you can start using the database.

1 of 19 | Next

Comments

Back to top

Table of contents

• Before you start
• About XML
• Creating a database that supports XML
• About the comments column
• About XQuery
• Retrieving XML elements
• Retrieving apecific XML elements
• The text() function
• Filtering on XML element values
• Transforming XML output
• The if-then-else expression
• The Order By clause
• The Let clause
• Built-in functions
• XQueries with embedded SQL
• Summary
• Download
• Resources
• About the authors

---

1 of 19 | Next

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.