Writing Java applications that access XML data stored natively in DB2 Viper isn't much different from writing Java applications that access relational data. Indeed, if you're familiar with Java Database Connectivity (JDBC), you already know much of what you need to begin writing your first DB2 XML application.

In this article, we'll step through several common programming scenarios, such as inserting XML data, querying XML and non-XML data, updating XML data, deleting XML data, and creating stored procedures that access XML data. But first, let's review a few fundamental guidelines for developing any type of DB2 database application.

Follow typical programming "best practices"

Although DB2's native XML support is new, good database application programming practices haven't changed. Before diving into the details of DB2's XML technology, keep these general principles in mind:

• Ask only for what you need: Don't retrieve the entire contents of a table -- or the entire contents of many XML documents -- if you only need a subset of this information. You'll just drive up processing costs and slow runtime performance.
• Avoid duplicating the work of a database server: Instruct DB2 to filter and process data according to your needs rather than doing this work in your application. For example, if you have DB2 return results in a specified order, you won't need to sort the data yourself. Similarly, if you have DB2 ensure that only distinct results are returned, you won't have to double-check for duplicates. Data-centric processing is best performed by the database server, not your application.
• Make your code easy to maintain: Include comments or Javadoc in your code, particularly if your application contains complex queries.
• Consider the scope of your transactions carefully: By default, JDBC treats each query as an independent transaction. Determine if this is appropriate for your needs, and also consider how the scope (and isolation level) you define for your transactions can impact overall concurrency requirements.
• Minimize traffic in networked environments: You'll enjoy better runtime performance if you avoid transferring data unnecessarily between your application and DB2. Retrieving only the data you need is one way to do this. Invoking database stored procedures can also help, depending on the nature of your work.

Configure your environment

DB2 doesn't require any special configuration to enable you to develop or run Java applications that work with XML data. Indeed, you can write, test, and debug your Java programs using the integrated development environment (IDE) of your choice or by working directly with a supported Java Developer Kit (JDK) from the command line. However, because DB2 Viper ships with a Developer Workbench, the examples in this article use its development environment. This section discusses how to configure the Developer Workbench, reviews some sample data, and explores database configuration parameters that may be of interest to you.

DB2 Developer Workbench

The DB2 Developer Workbench is based on the Eclipse 3.1 platform, an open source project available for free download. To compile and run any DB2 XML application with this workbench, you need to create a project and include appropriate DB2 libraries in the project's build path, including the libraries that support DB2's JDBC 3.0-compliant driver. To configure your environment, complete the following steps:

1. Launch the DB2 Workbench. For example, from the Windows Start menu, select DB2 > IBM DB2 Developer Workbench V9.1 > Developer Workbench.
2. Create a new project. We'll use a simple project initially. Switch to the Java perspective (Window > Open Perspective -> Java), and select File > New > Project. Follow the wizards to specify a project name. For other items, retain the default settings.
3. Add the DB2 libraries into your project's build path. Highlight your project, right mouse click, and select Properties. Select Java Build Path, and click the Libraries tab. Add the appropriate DB2 external .jar files, such as db2jcc.jar, db2jcc_javax.jar, and db2jcc_license_cu.jar.
4. Optionally, create a package for your application. Highlight your project, right mouse click and select New > Package.

For details on creating projects and packages, consult the online help information.

Sample data

The examples in this article work with the "clients" table created in "Get off to a fast start with DB2 Viper" (developerWorks, March 2006). As a quick review, this table was defined as:

				
create table clients(
  id    		int primary key not null, 
  name  		varchar(50), 
  status 		varchar(10), 
  contactinfo 	xml
)

Figure 1 depicts a sample XML file that will be inserted into the "contactinfo" column of this table shortly.

Database configuration parameters

The examples in this tutorial are simple and work with a small amount of XML data, so you shouldn't need to alter default database configuration parameters to get them to run. However, default values may not be sufficient for some production environments. In particular, settings for the log size, Java heap, query statement heap, and application heap may need to be increased. If these values are set inappropriately, your runtime performance may be slow or you may be unable to insert large XML documents into DB2 tables due to insufficient log space.

You can review and change DB2 database configuration parameters from the DB2 Control Center (select Tools > Configuration Assistant) or the DB2 command line processor. Consult the product manuals for details.

Connect to your database

Working with DB2 XML data requires establishing a connection to the database that contains your data. There's nothing special about this code -- it's the same logic that you'd write to connect to any DB2 database.

Listing 2 contains a helper class with methods for establishing and closing a DB2 database connection.

				

public class Conn {
  // for simplicity, I've hard-coded account and URL data.
  private static String user = "user1";
  private static String pwd = "mypassword";
  private static String url = "jdbc:db2:test";

  // this method gets a database connection 	
  public static Connection getConn(){
    Connection conn=null;
		
    //  load the appropriate DB2 driver and 
    //  get a connection to the “test” database  
    try {
       Class.forName("com.ibm.db2.jcc.DB2Driver");
       conn = DriverManager.getConnection(url, user, pwd);
       . . . 	
    }
    catch (Exception e) { e.printStackTrace();	}
    return conn;
		
  }   // end getConn();
	
  // this method closes a database connection 
  public static void closeConn(Connection conn){
    try {
      if(conn == null) { return; }
      conn.close();
    }
    catch (Exception e) { e.printStackTrace(); }
    finally { 
      try { conn.close();  }
      catch (Exception e) { } 
    }
  }  // end closeConn();
}  // end class 

You will call these methods in applications that perform broader tasks, such as inserting and querying XML data.

Insert XML data

Because the initial XQuery specification did not address database write operations (such as inserting data), DB2 relies on familiar SQL INSERT statements to enable programmers to write new XML data to tables that contain XML columns. DB2 can store any well-formed XML document of up to 2 GB.

Often, Java programmers need to insert XML data contained in files into DB2, although it's also possible to insert XML data from character strings, from binary data (including large objects), and from SQL sub-select statements. A review of how to insert XML data from files and from simple character strings is provided here. Consult the DB2 Viper manuals for details on other insert scenarios.

DB2 Viper also enables you to insert XML documents with or without validating them against previously registered XML schemas. The samples in this article cover both approaches.

Insert file without validation

The insertFile() method in Listing 3 illustrates how to insert data from an XML file into the "clients.contactinfo" column. This method begins by defining several variables for later use. The first three correspond to the ID, name, and status columns in the "clients" table. The fourth is the name of the XML file to be inserted into the "contactinfo" column. For simplicity, values have been hard-coded in this method; in a production environment, input values would be obtained differently.

After establishing a database connection, create a simple string for your INSERT statement. As you can see, it looks like any other DB2 INSERT statement and uses parameter markers for your four input column values. The INSERT statement is prepared as usual, and its four parameter markers are set. To set the marker for the XML column, open a FileInputStream, passing in the location of our XML file. Also obtain the length of this file, and use this information as input to the setBinaryStream() method. Finally, execute the statement, check for errors, and close the connection.

				
public static void insertFile(){
  try {
    // for simplicity, I've defined variables with input data 
    int id = 1885;
    String name = "Amy Liu";
    String status = "Silver";
    String fn = "c:/XMLFiles/Client1885.xml";  // input file

    // get a connection 
    Connection conn = Conn.getConn();

    //   define string that will insert file without validation
    String query = "insert into clients (id, name, status, contactinfo) values (?, ?, ? ,?)";

    // prepare the statement
    PreparedStatement insertStmt = conn.prepareStatement(query);
    insertStmt.setInt(1, id);
    insertStmt.setString(2, name);
    insertStmt.setString(3, status);
    File file = new File(fn);
    insertStmt.setBinaryStream(4, new FileInputStream(file), (int)file.length());

    // execute the statement 
    if (insertStmt.executeUpdate() != 1) {
        System.out.println("No record inserted.");
    }
    . . . 
    conn.close();
  }
  catch (Exception e) { . . . }
}

Insert file with validation

Inserting an XML file with validation requires very little additional programming effort. Assuming you have created and registered the ClientInfo.xsd file as discussed in "Get off to a fast start with DB2 Viper" (developerWorks, March 2006), you only need to modify one line of code in Listing 3 to instruct DB2 to insert the XML file with validation. This code involves the definition of the query string.

As shown in Listing 4, the revised INSERT statement invokes the XMLValidate function before specifying a parameter marker for the XML data. This function also requires that you specify the XML schema identifier to be used for validation. Here, a previously-registered schema known as "user1.mysample" is referred to.

				
String query = "INSERT INTO clients (id, name, status contactinfo) " +
    "VALUES (?, ?, ?, xmlvalidate(? according to xmlschema id user1.mysample))";

If your input XML file contains data that is valid according to the specified schema, DB2 inserts the row. If not, the entire statement fails, and no data for this row is inserted.

Insert character string without validation

The insertString() method shown in Listing 5 illustrates how you can insert a well-formed XML document assigned to a character string variable into DB2. The logic is not much different from the previous example of inserting data from a file. Instead of using the setBinaryStream() method of your prepared statement, use the setString() method. For simplicity, the XML document in the xml variable definition has been hard-coded in this example.

Note: Escape characters (backward slashes) are included before quotation marks that are part of the XML document (such as the XML version number in the example below).

				
public static void insertString(){
  try {
    // for simplicity, I've defined variables with input data 
    int id = 1885;
    String name = "Amy Liu";
    String status = "Silver";
    String xml = 
      "<?xml version=\"1.0\"?>" + 
      "<Client>" + 
      "<Address> " +
        "<street>54 Moorpark Ave.</street>" +
        "<city>San Jose</city>" +
        "<state>CA</state>" +
        "<zip>95110</zip>" +
      "</Address>" +
      "<phone>" +
        "<work>4084630110</work>" +
        "<home>4081114444</home>" +
        "<cell>4082223333</cell>" +
      "</phone>" +
      "<fax>4087776688</fax>" +
      "<email>sailer555@yahoo.com</email>" +
      "</Client>";

    // get a connection 
    Connection conn = Conn.getConn();

    //   define string that will insert file without validation
    String query = "insert into clients (id, name, status, contactinfo) values (?, ?, ? ,?)";

    // prepare the statement
    PreparedStatement insertStmt = conn.prepareStatement(query);
    insertStmt.setInt(1, id);
    insertStmt.setString(2, name);
    insertStmt.setString(3, status);
    insertStmt.setString(4, xml); 

    // execute the statement 
    if (insertStmt.executeUpdate() != 1) {
        System.out.println("No record inserted.");
    }
    . . . 
    conn.close();
  }
  catch (Exception e) { . . . }
}

Insert character string with validation

As you might expect, validating XML documents that are provided as character strings requires little extra programming effort. Indeed, only one line of code needs to be modified -- the definition of the query variable. You simply need to change the INSERT statement to invoke the XMLValidate function, just as you did in Listing 4.

Here's the revised statement:

				
String query = "INSERT INTO clients (id, name, status contactinfo) " +
    "VALUES (?, ?, ?, xmlvalidate(? according to xmlschema id user1.mysample))";

Query XML data

Now that you know how to insert XML data into DB2 using a Java program, you're ready to query XML data. There are several examples in this section to step through, starting with a simple task (such as retrieving a full XML document) and progressing to more difficult tasks (such as returning portions of XML documents based on XML and relational query predicates).

Although DB2 supports both SQL and XQuery as top-level languages, XQuery doesn't provide a means to resolve parameter markers. As a practical matter, this means that any XQueries in your application that require more than hard-coded query predicates must be wrapped in a SQL statement using a SQL/XML function such as XMLQuery or XMLExists. "Query DB2 XML data with SQL" (developerWorks, March 2006) discusses these functions in greater detail. Here, you'll see how to use them in a Java program. And, just for fun, you'll also see how to include an XQuery with hard-coded query predicates in an application.

Retrieve full XML documents

Our first query-based method is rather simple. It merely retrieves the full contact information for a given client. A query of this nature can be expressed easily in SQL. So, if you're familiar with JDBC, this code should be easy for you to understand.

The simpleQuery() method in Listing 7 declares several variables and then establishes a database connection using a helper method defined in Listing 2. The query string contains a simple SQL statement to select all contact information for a specific client. After executing the statement, the application prints the results that have been fetched into a character string variable (stringDoc).

				
import java.sql.*;
 . . . 
public static void simpleQuery() {
  PreparedStatement selectStmt = null;
  String query = null, stringDoc = null;
  ResultSet rs = null;
  int clientID = 1885;
		
  try{	
     // get a connection 
    Connection conn = Conn.getConn(); 
    
    // define, prepare, and execute the query
    // this will retrieve all XML data for a specific client 
    query = "select contactinfo from clients where id = " + clientID
    selectStmt = conn.prepareStatement(query);
    rs = selectStmt.executeQuery();

    // check for results 
    if (rs.next() == false) {
        System.out.println("Can't read document with id " + clientID);
    }

     // fetch XML data as a string and print the results 
    else {
         stringDoc = rs.getString(1);
         System.out.println(stringDoc);
      }
      . . . 
      conn.close(); 
    }
    catch (Exception e) { . . . }	    
}

This program prints a single row of data containing all the XML contact information for the specified customer.

Although not shown here, it's also possible to use XQuery to retrieve one or more entire XML documents, provided you don't need to incorporate parameter markers in your XQuery. Later in this article, you'll see a Java excerpt that uses XQuery to retrieve XML data.

Retrieve portions of XML documents

A common programming task involves retrieving portions of XML documents. The Java code in this example retrieves the names and primary email addresses of customers with a status of "Silver." Customer name and status information are stored in SQL VARCHAR columns, while email addresses are contained in XML documents in the "contactinfo" column.

In the interest of brevity, I've omitted code previously shown, including only those lines that are new or different.

				
. . . 
String status = "Silver";
		
try{	
     // get a database connection
    . . . .
    // define, prepare, and execute a query that includes 
    // (1) a path expression that will return an XML element and  
    // (2) a parameter marker for a relational column value 
   String query = "SELECT name, xmlquery('$c/Client/email[1]' " + 
       " passing contactinfo as \"c\") " + 
       " from clients where status = ?";
   PreparedStatement selectStmt = conn.prepareStatement(query);
   selectStmt.setString(1, status);
   ResultSet rs = selectStmt.executeQuery();
   
   // iterate over and print the results 
   while(rs.next() ){
        System.out.println("Name: " + rs.getString(1) +
           "   Email:  " + rs.getString(2));
    }
    . . . 
   // release resources 
}  
catch (Exception e) { . . . }	    

This code issues a SQL/XML statement that calls the XMLQuery function. It supplies a path expression to this function that causes DB2 to navigate to the first "email" element beneath the root "Client" element of the target XML documents. (Note that the path expression is case-sensitive.) The $c variable and the SQL FROM clause indicate where these target documents can be found -- in the "contactinfo" column of the "clients" table. The SQL WHERE clause further restricts the target XML documents to those found only in rows in which the client's "status" is of a certain value ("Silver," in this method).

Output from this program may appear similar to:

				
Name: Lisa Hansen   Email:  

Name: Amy Liu   Email:  <email>sailer555@yahoo.com</email>
. . . . 

In this sample output, no email information was returned for a qualifying customer (Lisa Hansen) because this element didn't exist in her XML "contactinfo" document.

Filtering on relational and XML predicates

Java programs can also instruct DB2 to filter query output based on conditions that apply to both XML and non-XML data. The following example builds on the previous one, returning the names and primary email addresses of "Silver" customers who live in San Jose, California. This single query is projecting data from XML and non-XML columns as well as restricting data based on the contents of both XML and non-XML columns.

The excerpt below includes only portions of code that have changed from the previous example. In this case, the SELECT statement now invokes XMLExists as part of the WHERE clause to restrict results to customers who live in the specified city and state (defined in the city and state variables, respectively).

				
. . . 
String status = "Silver";
String state = "CA";
String city = "San Jose";
. . . 	
try{	
    . . . .
   String query = "SELECT name, xmlquery('$c/Client/email[1]' " + 
       " passing contactinfo as \"c\") " + 
       " from clients where status = ?" 
       " and xmlexists('$c/Client/Address[state=$state][city=$city]' " +
       " passing contactinfo as \"c\", " + 
       " cast(? as char(2)) as \"state\", " + 
       " cast(? as varchar(30)) as \"city\" )";
   PreparedStatement selectStmt = conn.prepareStatement(query);
   selectStmt.setString(1, status);
   selectStmt.setString(2, state); 
   selectStmt.setString(3, city); 
   . . . 
}

Most of the query should be familiar to you, so this section just concentrates on its final four lines. The XMLExists function instructs DB2 to determine if a given XML document contains a client address that includes a specific city and state. The PASSING clause specifies where XML documents can be found: in the "contactinfo" column. The CAST function is called twice to cast the values of the input parameters (for city and state) to appropriate data types.

The output from this program is similar to the output shown in Listing 9, assuming both Lisa Hansen and Amy Liu live in San Jose, California.

Use XQuery as a top-level language

Although DB2 fully supports XQuery as a top-level language, the initial XQuery specification didn't address parameter markers. As a practical matter, this restricts the use of XQueries in Java applications. Previous sections illustrated how you can embed XQueries in SQL (using the XMLQuery and XMLExists functions, for example) to incorporate parameter markers. This section explores what you can do with pure XQuery in your Java applications.

The following example contains an XQuery similar to one presented in an "Query DB2 XML Data with XQuery" (developerWorks, April 2006). This XQuery determines which customers live in San Jose, California. For each such customer, it constructs an XML fragment containing an "emailList" that includes all the email addresses for that customer. Finally, it returns a sequence of emailLists.

				
try{	
    // get a database connection 
    Connection conn = Conn.getConn();

   // define, prepare, and execute an XQuery (without SQL). 
   // note that we must hard-code query predicate values. 
   String query = "xquery for $y in db2-fn:xmlcolumn" + 
      "('CLIENTS.CONTACTINFO')/Client " +
      "where $y/Address/city=\"San Jose\" and $y/Address/state=\"CA\"  " +  
      "return <emailList> { $y/email } </emailList>";
    PreparedStatement selectStmt = conn.prepareStatement(query);
    ResultSet rs = selectStmt.executeQuery();
   
     // iterate over all items in the sequence and print results.
     while(rs.next() ){
         System.out.println(rs.getString(1)); 
      }

      // release all resources 
      . . .  
     // catch and handle any exceptions 
     . . . 
}

Two aspects of this query are worth noting. First, the query string begins with the keyword "xquery." This instructs DB2 to use its XQuery parser to process the query. You need to do this whenever you use XQuery as the outermost language. Second, the query refers to the table and column names in upper case. XQuery is a case-sensitive language. Since DB2 typically folds table and column names into upper case when writing this information to its internal catalogs, the XQuery must match this information.

Sample output from this program is shown in Listing 12. Because one "emailList" item is returned per qualifying customer, a quick scan of this output indicates that four customers qualified. The first qualifying record contains one email address. The second contains none (perhaps because the customer didn't supply this information); as a result, its emailList is empty. The third qualifying record indicates that there are two email addresses on record for this customer. The fourth contains one email address for the customer.

				
<emailList><email>newemail@someplace.com</email></emailList>

<emailList/>

<emailList><email>beatlesfan36@hotmail.com</email>
<email>lennonfan36@hotmail.com</email></emailList>

<emailList><email>sailer555@yahoo.com</email></emailList>

You may wonder why the names of each qualifying customer were not included in our results. The answer is simple: XQuery works with XML data, and the customer names are stored in a SQL VARCHAR column. So, if you want the output to include the names of qualifying customers as well as their email addresses, you would have to write a query that includes both SQL and XQuery.

Update and delete XML data

To update and delete XML data stored in DB2, you use SQL UPDATE and DELETE statements. These statements can include SQL/XML functions that restrict the target rows and columns based on XML element values stored within XML columns. For example, you can delete rows containing information about customers who live in specific zip code or update XML (and non-XML data) only for customers who live in a given state.

Because the syntax for using SQL/XML functions in UPDATE and DELETE statements is the same syntax for using them in SELECT statements, the full code samples won't be repeated here. Instead, just brief excerpts are included. Let's consider the DELETE operations first.

Delete examples

Deleting a row that contains XML data is simple. Just use the SQL DELETE statement with a WHERE clause (if desired) to restrict the rows to be deleted. For example, the following code deletes the row for client ID 1885:

				
. . . 
 int clientID = 1885;
String query = "delete FROM clients WHERE id = ?";
 . . .  
PreparedStatement stmt = conn.prepareStatement(query);
stmt.setInt(1, clientID);
if (stmt.executeUpdate() == 0) {
   System.out.println("No records deleted.");
}
else { System.out.println("Record(s) deleted."); }
 . . . 

If you want to restrict your DELETE operations based on XML element values, simply invoke the appropriate SQL/XML functions in your WHERE clause. Listing 14 uses the XMLExists function to specify that information about all clients who live in Maine (abbreviated "ME") should be deleted:

				
String state = "ME";
String query = "delete from clients " + 
" where xmlexists('$y/Client/Address[state=$state]' " +
" passing clients.contactinfo as \"y\", " + 
" cast(? as char(2)) as \"state\" )";
. . . 
PreparedStatement stmt = conn.prepareStatement(query);
stmt.setString(1, state);
. . .

Update examples

You can update data in an XML column using the SQL UPDATE statement or a stored procedure, such as DB2XMLFUNCTIONS.XMLUPDATE. In both cases, updates to the XML column occur at a document level rather than an element level. However, programmers who update using the stored procedure don't need to supply the full XML document to DB2. They only need to specify the XML elements to be updated, and DB2 preserves the unchanged document data as well as update the specified elements. Programmers issuing UPDATE statements need to specify the full document (not just the elements they want to change).

A forthcoming article will discuss the XMLUPDATE stored procedure and provide you with sample code, so it won't be discussed here. Instead, this section reviews two code samples that issue UPDATE statements. You should find the logic of both examples familiar. One uses an XML file to update the clients table, while another uses a character string containing XML.

Listing 15 updates the contact information for client ID 1333 by using XML data contained in a file. Note that the new XML data is validated against a registered schema as part of the update operation:

				
int clientID = 1333;
String fn = "c:/XMLFiles/Client1333.xml";  // input file
String query = "update clients set contactinfo = " +
  "xmlvalidate(? according to xmlschema id user1.mysample) " + 
  "where id = ?";
. . . 
PreparedStatement stmt = conn.prepareStatement(query);
stmt.setInt(2, clientID);
File file = new File(fn);
stmt.setBinaryStream(1, new FileInputStream(file), (int)file.length());
. . . 

Of course, you can also use an XML query predicate to specify the customer contact record(s) that you want to update. Again, you need to use SQL/XML functions to do so. Imagine that a customer wants you to update her fax number but doesn't remember her client ID. Instead, she supplies her home phone number to help us locate her information. The following code excerpt uses XMLExists to restrict updates only to the record containing her home phone number. Note that the customer's full contact information is supplied as a Java string containing the revised XML document.

				
String homeph = "4081114444";
String xml = 
   "<?xml version=\"1.0\"?>" +
   "<Client>" +
   "<Address> " +
      "<street>54 Moorpark Ave.</street>" +
      "<city>San Jose</city>" +
      "<state>CA</state>" +
      "<zip>95110</zip>" +
   "</Address>" +
   "<phone>" +
      "<work>4084630110</work>" +
      "<home>4081114444</home>" +
      "<cell>4082223333</cell>" +
   "</phone>" +
      "<fax>4087773111</fax>" +
   "<email>sailer555@yahoo.com</email>" +
   "</Client>";

String query =  "update clients set contactinfo = ?" + 
   "where xmlexists('$y/Client/phone[home=$homeph]' " +
   " passing clients.contactinfo as \"y\", " + 
   " cast(? as varchar(11)) as \"homeph\" )";
. . . 
PreparedStatement stmt = conn.prepareStatement(query);
stmt.setString(1, xml);
stmt.setString(2, homeph);
. . . 

Query builder

If you need help writing queries for your application, the Developer Workbench provides wizards that generate SQL/XML and XQueries. Because most Java programmers write applications that require parameter markers, they frequently use SQL/XML. This section steps through a brief example of how to use the SQL query builder to generate a SQL/XML statement similar to one included in Listing 8.

To generate an SQL/XML statement, perform the following steps:

1. Prepare your workspace.
2. Specify the characteristics of your query.
3. Execute your query.

Let's step through each of these in turn.

Prepare your workspace

SQL statements are created as part of a "Data project" accessible from the workbench's Data perspective. To create such a project, complete the following steps:

1. Open the Data perspective. Select Window > Open Perspective > Other > Data.
2. Create a connection to your target database. Right mouse click inside the Database Explorer pane in the lower left corner. Select New Connection and specify your database name, user name, and password.
3. Create a new Data project. Right mouse click inside the Data Project Explorer pane in the upper left corner. Select New > Project > Data > Data Development Project. When prompted, give the project a name of your choice, and associate it with the database connection you created previously.

With a database connection open and a Data project created, you're ready to build queries.

Build your query

To keep this tutorial simple, create a SQL/XML statement that returns the primary email address of clients who have a certain status. The query will be similar to the following:

				
SELECT name, xmlquery('$c/Client/email[1]'  
passing contactinfo as "c")   
from clients where status = ? 

Follow these steps to generate your query:

1. Launch the SQL Builder. Within your Data project, highlight the SQL Scripts folder and right mouse click. Select New > SQL Statement. When prompted, accept the default for your project name and specify a name for your SQL statement. Accept the default for the statement type (SELECT) and elect to use the SQL builder. Click Finish.
2. Specify the table to be queried. Right mouse click in the center pane and select Add Table. Expand your schema folder and select the "clients" table.
3. Specify the columns of interest. For this example, you need to include one column and the output of one function (XMLQuery) in the result set. To do so, complete the following steps:
1. Check the "names" column displayed in the center pane.
2. Click on the first row displayed in the Column tab beneath the center pane. Click the far right corner of this cell to display an arrow key and select Build Expression. Press Enter.
3. Select Function from the displayed menu.
4. Select XML as the function category and XMLQuery as the function. Next to Parameter Value 1, click the arrow in the Value cell and select Edit Expression.
5. Specify the appropriate path expression in the String Constant Builder: $c/Client/email[1], and click Finish twice.
6. Alter the generated SQL statement to include a PASSING clause in the XQuery function. The final XQuery function should read: '$c/Client/email[1]' passing contactinfo as "c"
4. Specify the query predicate (WHERE clause). For this example, you need to add one query predicate for a relational column.
1. Under the Conditions tab beneath your SQL/XML statement, click on the first row displayed in the Column tab. Click on the arrow key in the far right of this cell and select the status column.
2. Click on the Operator cell and select the equality ("=") operator.
3. Click on the arrow key in the far right of the Value cell and select Build Expression. Press Enter.
4. Select Constant and then String Constant when prompted.
5. Specify a host variable name for user input (such as "status"). Click Finish.

Execute your query

After building your query, you're ready to run it.

1. Locate the query in your Data project, right mouse click and select Run SQL.
2. When prompted, specify an input value for the customer status (such as "Gold" or "Silver"), and click OK.
3. Review the results in the Data Output pane.

Stored procedures

In networked environments, stored procedures often reduce the communication required between client applications and DB2. This, of course, improves runtime performance. With Viper, stored procedures may include XML parameters and variables.

While it's beyond the scope of this article to discuss stored procedure development in detail, a review is provided for one simple scenario so you can see how a DB2 stored procedure can be written to retrieve portions of XML documents. This scenario uses wizards in the Developer Workbench to generate, deploy, and run the necessary SQL stored procedure code. If desired, you can develop and deploy an equivalent SQL stored procedure using DB2's command line processor. In addition, you can write XML-based stored procedures in Java.

For this example, you will write a stored procedure that retrieves the names and primary email addresses of clients with a certain status, just as you did earlier. Although this procedure is quite simple, it helps you understand how to generate SQL-based procedures that query and return XML data using built-in wizards.

To create this procedure, perform a few simple steps:

1. Prepare your workspace.
2. Specify the contents of your procedure.
3. Deploy and test your procedure.

Let's step through each of these in turn.

Prepare your workspace

Stored procedures are defined as part of a Data project. If you haven't already done so, open the Data perspective, establish a database connection, and create a Data project. For details, see the previous section Prepare your workspace.

Create your procedure

Our SQL-based stored procedure invokes a single SQL/XML statement to query the "clients" table based on input from the caller. This procedure returns a single result set that contains a SQL VARCHAR column (for the client's name) and an XML column (for the client's email). The query will be similar to the following:

				
SELECT name, xmlquery('$c/Client/email[1]'  
passing contactinfo as "c")   
from clients where status = ? 

The process for building a SQL stored procedure that accesses XML data is no different from building a SQL procedure that accesses non-XML data. Here's one way to do so:

1. Define a new stored procedure. Expand your new Data project, highlight Stored Procedures, and right mouse click. Select New > Stored Procedure. Follow the prompts to verify the project name and specify a stored procedure name. Keep the default language type as SQL.
2. Specify your SQL statement(s). When prompted, you can type in your query statement directly or use the wizards to help you create one. The following steps are for the latter.
1. Click Create SQL.
2. Accept the defaults for the statement type (SELECT) and development process (guided through statement creation with wizards).
3. Select the clients table as the target for your statement.
4. Under the Columns tab, include two columns in the final result set. Select names, then select Add > Function > Next. In the following window, specify the function category as XML, and XMLQuery as the function signature. Click Finish.
5. Under the Conditions tab, construct the SQL WHERE clause. Specify clients.status as the column, equals ("=") as the operator, and :input as the value.
6. Modify the resulting SQL statement to include the appropriate path expression for retrieving the first email address in the "contactinfo" column. Specifically, change the XMLQUERY line to read: xmlquery('$c/Client/email[1]' passing contactinfo as "c")
7. Parse your query to verify there are no syntax errors.
3. Specify deployment information. In particular, you may find it helpful to Enable Debugging.
4. Optionally, review the generated SQL code. Click Show SQL. (See Figure 2 for a sample of what should appear.)
5. Complete the stored procedure. Click Finish.

Deploy and test your procedure

With your procedure created, you're now ready to deploy and test it. Follow these steps:

1. Deploy the procedure. Locate the procedure in your Data project, right mouse click and select Deploy. Accept the defaults and click Finish. The Data Output pane in the lower right corner should note that your procedure has been successfully deployed.
2. Run the procedure. Locate the procedure in your Data project, right mouse click, and select Run. When prompted, specify an input value for the customer status (such as "Gold" or "Silver"). Click OK, and view the results of your stored procedure in the Data Output pane.

You can call the stored procedure outside the Developer Workbench, if desired. For example, if you had named your procedure "getInfo," you could invoke the DB2 command line processor, connect to the database, and issue this statement:

				
call getInfo('Silver') 

Summary

Writing Java applications that work with DB2 XML data involves using familiar JDBC code to execute queries and process their results. IBM provides an Eclipse-based Developer Workbench with DB2 to help you code, test, and debug your work. Included in this workbench are wizards for exploring the contents of your databases, writing stored procedures that access XML and non-XML data, writing XQueries that access XML data, and writing SQL/XML statements that access XML and non-XML data.

Acknowledgments

Thanks to Don Chamberlin, Grant Hutchison, and Brian Payton for their review of this article.

Resources

Learn

• DB2 Viper Web site: Learn more about DB2's XML support.
  
  
• "What's new in DB2 Viper: XML to the Core" (developerWorks, February 2006): Get an overview of the new XML technologies now in beta in DB2.
  
  
• "Get off to a start start with DB2 Viper" (developerWorks, March 2006): Learn how to insert, import, and validate XML data.
  
  
• "Query XML Data in DB2 with SQL" (developerWorks, March 2006): Learn how to query DB2 XML data using SQL and SQL/XML.
  
  
• "Query XML Data in DB2 with XQuery" (developerWorks, April 2006): Learn basic XQuery expressions for querying DB2 XML data XQuery.
  
  
• "XQuery from the Experts," (Howard Katz, et. al., Addison-Wesley, 2003): Learn about the XQuery language. A book excerpt is available online.
  
  
• "Integration of SQL and XQuery in DB2" (forthcoming IBM Systems Journal issue): Explore how SQL and XQuery differ, and how the two languages can be used together effectively.
  
  
• "Firing up the Hybrid Engine" (DB2 Magazine, Quarter 3, 2005): Read more about IBM's hybrid database management system.
  
  
• System RX: One Part Relational, One Part XML (SIGMOD 2005 Conference): Learn about the architecture and design aspects of building a hybrid relational and XML DBMS.
  
  
• In "Native XML Support in DB2 Universal Database," read more about DB2 XML support in a VLDB paper published by Matthias Nicola and Bert Van der Linden in 2005.
  
  
• "Managing XML for Maximum Return," (IBM White Paper, November 2005): Explore the business benefits of DB2's XML support.
  
  
• developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• "IBM Systems Journal: Celebrating 10 Years of XML": Learn about core XML technologies as well as techniques for integrating XML with business data. Papers from IBM researchers and developers discuss XPath, XQuery, SQL/XML, DB2 optimization, and many other topics.
  
  
• Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community.
  
  

Get products and technologies

• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  
• Learn more about the open source Eclipse platform and download free software from the Eclipse Web site.
  
  
• Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.
  
  

Discuss

• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Follow typical programming "best practices"
• Configure your environment
• Connect to your database
• Insert XML data
• Query XML data
• Update and delete XML data
• Query builder
• Stored procedures
• Summary
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers