Query DB2 XML data with XQuery

Return to article

A simple Java example

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 embed an XQuery, we've included a code excerpt below. This excerpt shows how to retrieve the addresses of clients living in a given zip code.

XQuery doesn't provide a means to resolve parameter markers, so embedding XQuery in a Java application involves wrapping (or embedding) your XQuery in an SQL statement. "Query DB2 XML Data with SQL" discussed the XMLQuery function of SQL/XML, which we will use in this example.

If you're familiar with JDBC, you should find most of this example easy to understand. After declaring and defining necessary variables, establish a database connection following the standard DB2 process. Next, create a string that contains the query statement. We've actually defined two different, but equivalent, query strings in this example: query1 and query2. Query1 uses a FLWOR expression in keeping with the earlier examples. Query2 uses a simpler but equivalent path expression.

Logically, the code in this example is equivalent to a previous example (Listing 10. FLWOR expression with a new "where" clause), except that it

• Embeds the XQuery in an SQL SELECT statement. When embedded in an SQL statement, XQuery no longer needs to call db2-fn:xmlcolumn or db2-fn:sqlquery to obtain input data. Instead, XQuery obtains its input from variables that are passed to it from SQL by a PASSING clause.
• Uses a parameter marker for the zip code value.
• Uses Java escape characters (backward slashes) wherever double quotation marks are needed in the query string. In this example, they are used in the PASSING clause to enclose the variables "t," "y," and "z."
• Explicitly casts the value of the 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 created in "Get off to a fast start with DB2 Viper" defined zip codes to be XML strings. Finally, execute the statement, process the returned result set as desired, and close the database connection.

					
. . . 
// define variables 
String zipCode = "95116";
PreparedStatement selectStmt = null;
String output = null;
ResultSet rs = null;

try {

. . . 
// get a DB2 database connection following the standard process 
. . . 
// create the string for our query.
// we must embed our XQuery in SQL so we can pass in a parameter. 
// furthermore, we must use escape characters around double quotes.
//
// this query uses a FLWOR expression to 
// obtain addresses of customers who live in a specific zip code.
  
   String query1 = "select xmlquery("+
   " 'for $t in $y/Client/Address where $y/zip=$z" +
   "return $t' passing contactinfo as \"y\", " + 
   "cast (? as varchar(5)) as \"z\") " +
   "from clients";

//  this equivalent query uses a path expression for the same purpose.
   String query2 = "select xmlquery("+
   " '$y/Client/Address[zip=$z]' " + 
   " passing contactinfo as \"y\", " +
   "cast (? as varchar(5)) as \"z\") " +
   "from clients";

// prepare the statement and set the input parameter.
// since the two previous query strings are equivalent, 
// we can use either one.  
// we're using the shorter version (query2) here.  			
   selectStmt = conn.prepareStatement(query2);
   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