Get off to a fast start with DB2 9 pureXML, Part 3

Query DB2 XML data with SQL

Return to article

As mentioned in the main article, we won't have time to discuss application development topics in detail here. But in case you're wondering how a Java application might issue an SQL/XML SELECT statement, included is a code excerpt below. This excerpt shows how to determine the names of clients living in a given zip code.

If you're familiar with JDBC, you should find this example easy to understand. After declaring and defining necessary variables, establish a database connection following the standard DB2 process. Next, create a string ("query") that contains your SELECT statement. This query is the same as the earlier version that we created in the main article (see Listing 7), except that it

  • uses a parameter marker for the zip code value
  • uses escape characters around variables in the SELECT string that need to be placed inside of double quotation marks. (In this example, they are "c" and "x".)
  • explicitly casts the value of our input parameter to an appropriate data type.

You then prepare the SELECT statement, setting its input parameter to your target value, which is defined in the Java "zipCode" variable. We used a String type here because the XML schema definition we created in the article "Get off to a fast start with DB2 9, Part 2" (developerWorks, March 2006) defined zip codes to be XML strings. However, an integer type would also work. Finally, execute the statement, process the returned result set as desired, and close the database connection.

Listing A. Java™ code excerpt for issuing a SQL/XML statement
. . . 
// define variables 
PreparedStatement selectStmt = null; 
String query = null;
ResultSet rs = null;
Connection conn = null;
String zipCode = "95116";

try {

. . . 
// get a DB2 database connection following the standard process
. . . 

// create the string for our SELECT statement
// if we were to issue this query interactively, we'd write: 
//	select name from clients
//	where xmlexists('$c/Client/Address[zip="95116"]'
//	passing clients.contactinfo as "c")
// because we're including it in a Java program, we need to 
// use escape characters around the double quotation marks.  
// we also need to explicitly cast our parameter marker value to 
// the appropriate type. 
query = "select name from clients where xmlexists(" +
   "'$c/Client/Address[zip=$x]' "+
   "passing clients.contactinfo AS \"c\", cast(? as char(5)) as \"x\" )";

// prepare the statement and set the input parameter
selectStmt = conn.prepareStatement(query);
selectStmt.setString(1, zipCode);

// execute the statement 
rs = selectStmt.executeQuery();

// process the results as desired
. . . 

// release resources and close the connection 
. . . 
catch (Exception e) { . . .  }

Return to article