Get off to a fast start with DB2 9 pureXML, Part 5: Develop Java applications for DB2 XML data

IBM DB2 9 for Linux, UNIX, and Windows features significant new support for storing, managing, and querying XML data. In this article, you'll learn the basics of how to write Java applications that access the new XML data. You'll see how to insert, query, update, and delete XML data, how to create stored procedures that access XML data, and more.

Note: This article has been updated to include changes in DB2 for Linux, UNIX, and Windows, Versions 9.5 and 9.7.

Cynthia M. Saracco, Senior Software Engineer, IBM

Cindy Saracco photoC. M. Saracco works at IBM's Silicon Valley Laboratory in the DB2 XML organization. She works on database management, XML, Web application development, and related topics.



25 March 2010 (First published 04 May 2006)

Also available in Russian Vietnamese Portuguese Spanish

Writing Java applications that access XML data stored natively in DB2 9 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. The examples in this article use IBM Data Studio (or Optim Development Studio) for its development environment. This section discusses how to configure the Data Studio, reviews some sample data, and explores database configuration parameters that may be of interest to you.

IBM Data Studio

IBM Data Studio is based on the Eclipse 3.4 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 4.0-compliant driver. To configure your environment, complete the following steps:

  1. Launch Data Studio.
  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 > Java 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 9, Part 2 (developerWorks, March 2006). As a quick review, this table was defined as:

Listing 1. Sample code listing at maximum width
create table clients(
  id    		int primary key not null, 
  name  		varchar(50), 
  status 		varchar(10), 
  contactinfo 	xml
)

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

Listing 2. Sample XML file to be inserted into the "clients" table
<?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>

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 3 contains a helper class with methods for establishing and closing a DB2 database connection.

Listing 3. Helper class to acquire and release database connections
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 9 manuals for details on other insert scenarios.

DB2 9 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 4 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.

Listing 4. Inserting XML data from a file
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 9, Part 2" (developerWorks, March 2006), you only need to modify one line of code in Listing 4 to instruct DB2 to insert the XML file with validation. This code involves the definition of the query string.

As shown in Listing 5, 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.

Listing 5. Inserting XML data from a file with validation
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).

Listing 6. Inserting XML data from a character string
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 5.

Here's the revised statement:

Listing 7. Inserting XML data from a character string with validation
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. "Get off to a fast start with DB2 9, Part 3: 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 8 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).

Listing 8. Retrieving full XML documents with SQL
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.

Listing 9. Retrieving relational data and XML fragments with SQL/XML
. . . 
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:

Listing 10. Sample output from previous application
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).

Listing 11. Filtering XML data based on XML element values
. . . 
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 "Get off to a fast start with DB2 9, Part 4: 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.

Listing 12. Retrieving XML fragments with XQuery
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 13. 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.

Listing 13. Sample output from previous application
<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:

Listing 14. Deleting data based on a relational data value
. . . 
 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:

Listing 15. Deleting data based on an XML element value
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

With DB2 9, 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).

Part of the DB2 9.5 release, however greatly simplified updating existing XML documents. In this release, we implemented a new XQuery function called "Transform". This function expanded the initial XQuery langauage to go beyond simply querying XML, to updating XML.

Listing 16 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:

Listing 16. Updating XML data from a file
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. This is the method that needs to be followed with DB2 9.1. With DB2 9.5, however, you have the newly added W3C function called XML Transform or Update. This new function, now in recommendation status, allows you to update a node, delete a node, replace a node, or modify a node. The transform function requires specific syntax, which is shown in Listing 17b. With this new function, you are basically taking a snapshot of an existing XML document, making your modifications and then replacing it with the new modifications. Listing 17b shows the difference with 9.5 and the new Transform function.

Listing 17. Updating XML data with a character string
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);
. . .
Listing 17b. Updating XML data with a character string with DB2 V9.5 and the XQuery Transform function.
String homeph = "4081114444";
String xml =
         "4087773111";

String query =  "update clients  set contactinfo = " +
           "xmlquery('  "+
            "copy $new :=$CONTACTINFO" +
            "modify do replace value of $new/Client/fax with $p " +
            "return $new "+
             
         "passing cast (? as varchar(11)) as "p") "+
   "where xmlexists('$y/Client/phone[home=$homeph]') "  +
      "passing clients.contactinfo as \"y\"  ";

. . . 
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 9.

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:

Listing 18. Sample SQL/XML query
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 DB2 9, 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 Data Studio 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:

Listing 19. Sample SQL/XML query
SELECT name, xmlquery('$c/Client/email[1]'  
passing contactinfo as "c")   
from clients where status = ?

The process for building an SQL stored procedure that accesses XML data is no different from building an 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 Listing 20 for a sample of what should appear.)
  5. Complete the stored procedure. Click Finish.
Listing 20. Sample code generated for SQL stored procedure involving XML data
CREATE PROCEDURE foo (INPUT VARCHAR(10)
	DYNAMIC RESULT SETS 1
--------------------------------------------
--SQL Stored Procedure
	--INPUT
--------------------------------------------
P1:BEGIN
	--------------------------------------------
	--Variables declaration fragment inserted from SP_SQL_VAR.FRAGMENT
	--------------------------------------------

	--Declare cursor
	DECLARE cursor1 CURSOR WITH RETURN FOR
		SELECT NAME, XMLQUERY('$c/Client/email[1]' PASSING CONTACTINFO AS "c")
		 FROM CLIENTS
		 WHERE STATUS=INPUT;

	--Cursor left open for client application
	OPEN cursor1;
END P1

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:

Listing 21. Invoking your stored procedure
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 tools such as Data Studio, an Eclipse-based developer workbench, to help you code, test, and debug your work. Included with Data Studio 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

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • 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

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, XML
ArticleID=110455
ArticleTitle=Get off to a fast start with DB2 9 pureXML, Part 5: Develop Java applications for DB2 XML data
publish-date=03252010