Using XQuery with DB2
All this is interesting, but to move beyond the academic stage, you will need to have some sort of XQuery engine against which to run your queries. Fortunately, IBM DB2 version 9 includes PureXML, a native XML engine, which you can use to run your XQuery queries. Let's look at setting up DB2 for XML.
To take advantage of the XML features of DB2, you need to configure the new database deliberately, so you will go through this creation process step by step.
- Download DB2 and install it according to the instructions. From there, you will need to create a database to store your XML data.
- On your Microsoft® Windows® task bar, in the system tray, you should have an icon representing your instance of the DB2 server, which should be named DB2.
- Right-click the icon and select DB2 Control Center to open the DB2 Control Center.
- On the right hand side, you will see a branching menu with two options, All Systems and All Databases. Right-click All Databases and select Create
Database>Standard. This action opens the database creation wizard, shown in Figure 1.
Figure 1. Database creation wizard, step 1
- Here you are asked what you want to name the database and where you want to store it. For this tutorial, use mashup_1 for the name and alias of the database. You can add any comment name you like.
- Next, select where you will store the database files. Databases can hold tremendous amounts of data. Select a location that can handle the volume of data for your DB2 applications. For this application on your development machine, you should only need a minimal mount of space. For now, select a location which has more than 10MB of space available and create a folder there. Type that folder path in the Default path field. You can also click Browse and select a folder that way.
- Click to enable the Enable database for XML checkbox, which requires that you set code type to UTF-8. This is the key for your requirements.
- Leave the remaining settings at the defaults and click Next (see Figure 2).
Figure 2. Database creation wizard, step 2
- Again, the installer asks you where to store the data for the DB. Leave the Use the database path as a storage path checkbox enabled and click Next (see Figure 3).
Figure 3. Database creation wizard, step 3
- Again you encounter a critical selection for your new database, the locale settings. For XML tables to function, they require UTF-8 encoding. You can select any country code, but you must select UTF-8 in the Code set drop-down list. Leave Collating Sequence at its default value and click Next (see Figure 4).
Figure 4. Database creation wizard, step 4
- Finally, you will be rewarded with a summary screen and a Finish button. Click Finish and wait a moment as the system creates the initial files and starts the processes for your new database. Once the database is complete, you will find it under the All Databases tree. You can expand the mashup_1 item and see the components.
Now it's time to create a table to hold your data. The easiest way to do this is to execute queries in the DB2 Control Center.
In this tutorial, you are only interested in executing XQuery against specific XML, but one of the strengths of DB2 is the ability to combine XML and relational data. For example, you might have a table structured like this (see Listing 9).
Listing 9. A simple table combining XML and relational data
create table create table monthlyReports ( id INT generated always as identity, reportDate TIMESTAMP, reportData XML)
This would allow you to execute a query such as shown in Listing 10.
Listing 10. A simple query
select xmlquery('$sales/total' passing reportData as "sales") from monthlyReports where reportDate < '1-1-2007'
This query would select a specific XML node from any record in the table with a
reportDate less than January 1, 2007. In this case, however, you are going to concentrate on the XQuery part of the equation, so you will create a table with only an XML column, and you will insert just one record (see Listing 11).
Listing 11. The table creation SQL
create table bookinfo (bibliography XML)
You can then insert the data into the table (see Listing 12).
Listing 12. Inserting the data
insert into bookinfo (bibliography) values (XMLPARSE(document cast ('<?xml version="1.0"?> <bib> <book year="1994"> <title>TCP/IP Illustrated</title> <author> <last>Stevens</last> <first>W.</first> </author> <publisher>Addison-Wesley</publisher> <price> 65.95</price> </book> <book year="1992"> <title>Advanced Programming in the UNIX environment</title> <author> <last>Stevens</last> <first>W.</first> </author> <publisher>Addison-Wesley</publisher> <price>65.95</price> </book> <book year="2000"> <title>Data on the Web</title> <author> <last>Abiteboul</last> <first>Serge</first> </author> <author> <last>Buneman</last> <first>Peter</first> </author> <author> <last>Suciu</last> <first>Dan</first> </author> <publisher>Morgan Kaufmann Publishers</publisher> <price> 39.95</price> </book> <book year="1999"> <title>The Economics of Technology and Content for Digital TV</title> <editor> <last>Gerbarg</last> <first>Darcy</first> <affiliation>CITI</affiliation> </editor> <publisher>Kluwer Academic Publishers</publisher> <price>129.95</price> </book> </bib>' as clob) preserve whitespace))
(If you are executing this command from the command line, you will need to remove all of the line feeds.)
Now that you have an XQuery engine and some data,you can start looking at queries.