11 Oct 2010 - Added fpmldb2was.zip in Downloads.

Overview

XML's widespread use is prompting programmers to look for effective ways of working with both transient XML messages and persistent XML data. Web services and other software components often generate transient XML messages that contain important business data. Increasingly, applications need to process these messages as well as persistent business artifacts stored in a native XML database management system.

While it's certainly possible to map both transient and persistent XML data to Java objects, doing so can be challenging for heavily nested, complex XML structures. Furthermore, if the schemas for your XML data are likely to change over time, this can introduce added complexity to your application code. The first article in this series introduced a declarative programming approach for working with transient and persistent XML data across the application server and database server tiers to reduce the complexity often associated with imperative programming approaches.

This article dives more deeply into the topic of working with transient and persistent XML in a server-side Java application. By reviewing common application development tasks and sample code, you'll:

1. See how to exploit native XML database server technology from Java EE applications that need to effectively integrate transient and persistent XML data.
2. Learn how to leverage XML indexing and query filtering capabilities in a database management system to ensure strong runtime performance.
3. Review how to join transient and persistent XML data as well as how to update specific portions of persistent XML documents.

Prerequisites and products

Before reading this article, you should be familiar with XML, XPath, and XQuery. See Resources for information about these technologies. In addition, because examples in this article are based on the WebSphere Application Server V7.0 Feature Pack for XML 1.0, you should read Part 1 of this article series. Knowledge of XML database management technology is also helpful. We use DB2® as the XML database server in the examples for this article.

Our application environment and sample database

To explore how server-side Java programmers can work efficiently with transient and persistent XML data, we installed the following software on a single Windows® system:

• Rational Application Developer for WebSphere Software V7.5.5 with the WebSphere Application Server 7.0 test environment
• WebSphere Application Server Feature Pack for XML 1.0 with Fix Pack 1.0.0.7. (This can be used with the stand-alone version of WebSphere Application Server as well as the WebSphere test environment built into Rational Application Developer.)
• DB2 9.7 Enterprise Server Edition

The build path for our Rational Application Developer development project included appropriate .jar files for the JDBC 4.0 driver in DB2. These are: db2jcc4.jar and db2_license_cu.jar. To use the XML Feature Pack within your project, ensure that you enable the XML Transformation and Query Project Facet for your project by right clicking the project, selecting Project Facets, and enabling the facet. Alternatively, you can add the XML Feature Pack thin client jar (com.ibm.xml.thinclient_1.0.0.jar) to your build path. For details on the JDBC 4.0 driver in DB2 or the XML Feature Pack, see Resources.

The database for our examples contains records of over-the-counter (OTC) derivatives trades based on sample XML data provided by the International Swaps and Derivatives Association (ISDA). This data complies with the Financial Products Markup Language (FpML) specification, which is popular with many firms that trade derivatives.

DB2 provides a number of free, industry-specific software downloads to help firms quickly create and populate a test database that complies with an industry-standard XML format. One such bundle is available for FpML. We customized this bundle to help us illustrate certain points in this article; you can download this version using the link in Downloads. If you want to set up the sample database on your Windows system, open a DB2 command window and issue start.bat. This will create the necessary database objects and load sample FpML data.

Our examples use the FPMLADMIN.FPML43 table, which contains two relational columns (ID and COMMENT) as well as one XML column (DOCUMENT). The XML column stores full FpML records for various types of derivatives trades. It's quite typical for firms to store both relational and XML data in a single table, and our examples reference both types of columns. Figure 1 illustrates the structure of this table.

FpML records contain highly nested structures with elements and attributes that vary according to the type of trade represented. Figure 2 illustrates a small portion of a sample FpML record for a credit default swap trade.

Our sample table contains FpML documents that range in size from 2KB to more than 125KB. Furthermore, the table stores trade documents based on three different versions of the FpML schema in the same XML column. (Specifically, it stores trade documents based on FpML 4.2, 4.3, and 4.7.) XML Schemas tend to evolve over time to accommodate changing business needs, so storing documents that conform to different schemas in a single XML column is a common business requirement.

Back to top

Querying persistent XML data

Java EE programmers often need to retrieve persistent XML data stored in the database tier and manipulate this data in the application server tier. To demonstrate this, we'll use an example that leverages the XML Feature Pack API and built-in DB2 support for querying XML data. Both offerings support industry standards for querying XML data, including XQuery and XPath expressions.

Because DB2 recognizes and supports XML data as a first-class data type, programmers can work directly with the XML data in its native format rather than work with the data as a large object (LOB). Using XML across multiple software tiers simplifies programming logic and reduces development costs. Furthermore, native XML support in DB2 provides efficient access to specific portions (XML nodes) of a document; large objects do not.

Query Scenario

Consider the situation in which a Java EE application needs to access credit default swap data from a specific trade. If the trade data is passed into the application as a transient XML message, the application can simply use services provided by the XML Feature Pack to execute an XPath expression against the message. Listing 1 defines such an XPath expression.

// This XPath expression obtains the credit default swap data for a given trade.  
// For simplicity, we omitted declaring a specific namespace here.
  
String myXpath = "*:FpML/*:trade/*:creditDefaultSwap;
. . . 

Not all applications rely on transient XML messages. An application designed to work with a corporate trade portfolio to analyze investment strategies, manage risk, or perform some other business function might need to obtain the desired trade data from a persistent XML repository.

Sample query returning a fragment of persistent XML trade data

Listing 2 illustrates how to use XPath expressions to obtain credit default swap trade data involving a company of interest from a database—in this case, DB2.

SELECT XMLQUERY('declare default element namespace 
   "http://www.fpml.org/2009/FpML-4-7";
   $fpml/FpML/trade/creditDefaultSwap' passing document as "fpml")
FROM fpmladmin.fpml43 
WHERE comment LIKE ‘cd%’ 
AND 
XMLEXISTS('declare default element namespace "http://www.fpml.org/2009/FpML-4-7";
   $fpml/FpML/trade/creditDefaultSwap/generalTerms/referenceInformation
   /referenceEntity[entityName="Agrium Inc."]' passing document as "fpml")

 

This query, which can be executed interactively through a DB2 command-line interface or graphical query tool, is a standard SQL statement that invokes two XML-specific functions. The first function, XMLQUERY(), identifies the data we want returned (the data in the creditDefaultSwap node). The second function, XMLEXISTS(), restricts the results to FpML 4.7 documents that reference a specific entity (Agrium Inc.).

Like most SQL statements, this example follows the standard SQL syntax of SELECT column(s) . . . FROM table(s) . . . WHERE condition(s). Let's step through this query in more detail and then review a Java coding example that shows how we can invoke this query in an application that also uses the XML Feature Pack.

The first line of the query issues a SQL SELECT statement that invokes an industry-standard SQL function (XMLQUERY). When invoking XMLQUERY(), we first declare the target namespace of interest (http://www.fpml.org/2009/FpML-4-7). As we discussed earlier, our sample DB2 table contains different versions of FpML records in a single column, and we’re only interested in a specific FpML 4.7 record.

After declaring the default namespace to the XMLQuery function, we provide an XPath expression that specifies the data we want to retrieve ($fpml/FpML/trade/creditDefaultSwap). The passing clause that follows the XPath expression specifies that the $fpml variable represents the DOCUMENT column in our table. Therefore, the first three lines of Listing 2 indicate that we want to retrieve only a subset of the FpML 4.7 trade data stored in the DOCUMENT column. Specifically, we want only credit default swap data, which FpML represents as an XML node that contains multiple child nodes (see Figure 2). The SQL FROM clause identifies FPMLADMIN.FPML43 as the table of interest.

The SQL WHERE clause indicates that we want only results related to credit default trades. In our sample table, values in the COMMENT column (a relational column) can be used to isolate the type of trade recorded in the DOCUMENT column (an XML column). COMMENT values that begin with cd represent credit default trades.

The remaining lines invoke the industry-standard XMLEXISTS() function, which restricts the query results to swaps involving a specific corporate entity named Agrium Inc. Looking at the XMLEXISTS function more closely, you'll see that it declares an appropriate namespace and includes an XPath expression that the database server will execute against data contained in the DOCUMENT column of the table.

For best runtime performance, Java EE programmers code queries to be as selective as possible. Our sample query retrieves only the portion of the XML data that we need (in particular, the XMLQUERY function obtains only a specific XML node of the trade document). In addition, our sample query qualifies the rows of interest by specifying relational and XML predicates in the WHERE clause. Writing our query in this manner minimizes the amount of data transferred between the database and application server tiers. (Our example retrieves a subset of one XML trade record stored in DB2, while a more general query involving all credit default swap trades would return forty full trade records.) Finally, defining appropriate XML indexes in DB2 can speed runtime query performance, as we'll discuss shortly.

Sample Java EE code for our query scenario

It's not difficult to include this database query in a Java EE application. Let's step through an example that retrieves the desired XML data from the database and manipulates this data in the application server.

Listing 3 shows an excerpt from a servlet that defines the database query that will be executed by an appropriate XML Feature Pack collection resolver.

// Excerpt from our sample servlet. 
//  
// First, the servlet sets up the database query. 
// Here, a String named "getCreditDefaultSwapsByEntityName" will be mapped to our query, 
// which extracts the creditDefaultSwap node from qualifying trades.

dbStatements = new HashMap<String, String>();
dbStatements.put("getCreditDefaultSwapsByEntityName",
 "select " +
   "xmlquery("'declare default element namespace " + 
     "\"http://www.fpml.org/2009/FpML-4-7\"; " + 
     "$DOCUMENT/FpML/trade/creditDefaultSwap' ) " +
  "from fpmladmin.fpml43 " + 
  "where comment like ? and " +
  "xmlexists("'declare default element namespace " + 
    "\"http://www.fpml.org/2009/FpML-4-7\"; " +
    "$fpml/FpML/trade/creditDefaultSwap/generalTerms/" + 
    "referenceInformation/referenceEntity[entityName=$name]' " +
    "passing document as \"fpml\", " +
    "cast (? as varchar(100)) as \"name\")"
);
...

// Next, another method in this servlet creates the XQuery executable.     
// This method then uses our JDBC resolver to execute it, 
// providing an appropriate value for the entityName variable. 

Source source = 
   new Source(FpMLServlet.class.getResource("/getCreditDefaultSwaps.xq").toString());
XQueryExecutable getCreditDefaultSwapsXQ = factory.prepareXQuery(source, staticContext);
...
JDBCCollectionResolver inputResolver =
  new JDBCCollectionResolver(getConnection(), dbStatements);
dynamicContext.setCollectionResolver(inputResolver);
dynamicContext.bind(new QName("http://com.ibm.xml.samples",entityName"), name);
XSequenceCursor output = getCreditDefaultSwapsXQ.execute(dynamicContext); 

The query in the first part of Listing 3 is nearly identical to the interactive version in Listing 2. Differences include:

• Parameter markers, denoted by the question mark character (?), replace hard-coded predicate values to allow for greater flexibility.
• Double quotes are properly escaped.

With a named query properly defined, the application can execute the query and apply business logic to the returned results. For simplicity, the business logic shown in this example merely obtains the coupon rate and other corporate bond information associated with the qualifying trades. A production application would contain more complex business logic.

The second portion of the code in Listing 3 contains the logic that obtains the appropriate XML data from the database server and manipulates it in the application server. This coding pattern follows the same approach that Part 1 of this article series introduced as an example (see Resources). First, the application creates an XQuery executable object that the XML Feature Pack will execute against the portion of the XML trade record returned by DB2. Next, the application specifies an appropriate collection resolver. The resolver will connect to the DB2 database and execute the query, passing in an appropriate data value for the query’s parameter marker (the name of the referenced entity in the trade).

Our resolver implementation is identical to the resolver described in Listing 8 of Part 1 of this article series, so we won't review it in detail here. This resolver works with collections that start with a jdbc:// URI scheme. It uses named queries to resolve the remainder of the URI and executes appropriate logic. This resolver, like the rest of the sample code discussed in this article, is available for download. See Downloads for the links.

Note that the resolver design used here is based on an example that ships with the XML Feature Pack and represents only one way of providing access to a native XML database (such as DB2 pureXML). If you want to use this code in a production application, you should modify or extend the sample as needed. In addition, Part 1 of this article series briefly discusses other possible approaches to designing a resolver; for an example, see Listing 14 of that article (see Resources).

Once the appropriate credit default swap data is retrieved from the database, the application can further manipulate the data as desired. Listing 4 shows an XQuery function that executes on the application server, extracting the instrument ID, coupon rate, and maturity date of the corporate bond referenced in each trade returned from the database.

declare variable $my:entityName as xs:string external;

declare variable $databaseURI := 
   concat('jdbc://getCreditDefaultSwapsByEntityName?cd%&', $my:entityName); 
declare variable $creditDefaultSwaps := collection($databaseURI);

for $bond in $creditDefaultSwaps//fpml:bond
     return
<tr>
     <td>{ $bond/fpml:instrumentId }</td>
     <td>{ $bond/fpml:couponRate }</td>
     <td>{ $bond/fpml:maturity }</td>
</tr>
 

Creating indexes to improve runtime performance

Now that you understand the logic we followed for retrieving appropriate XML data from the database and manipulating it in the middle tier, let's turn to another topic: performance. To ensure our query would execute efficiently on the database tier, we created two indexes on the FPMADMIN.FPML43 table:

• FPMLADMIN.COMMENTX, which indexes the relational COMMENT column.
• FPMLADMIN.ENTITYNAME, which indexes a specific node in the XML DOCUMENT column.

As you'll see shortly, DB2 can use both of these indexes to generate an efficient access path for our query. Listing 5 shows how to create these indexes. The first statement defines the relational index, and the second defines the XML index.

create index fpmladmin.commentx on fpmladmin.fpml43(comment)

create index fpmladmin.entityname on fpmadmin.fpml43(document) 
generate key using xmlpattern  
  'declare default element namespace "http://www.fpml.org/2009/FpML-4-7"; 
  /FpML/trade/creditDefaultSwap/generalTerms/referenceInformation
       /referenceEntity/entityName' 
as sql varchar(1000) 

We created these indexes because the FPMLADMIN.FPML43 table in the sample database is 4MB and contains nearly 900 rows. Only a small amount of data relates to credit default swaps that reference our target company (Agrium Inc.) as the named entity. DB2 can use both the relational and XML indexes to quickly pinpoint the data of interest, avoiding a costly scan of all rows in the table.

Figure 3 illustrates the access plan that DB2 selected for our query after we collected appropriate statistical data using the built-in RUNSTATS facility in DB2. Reading from the bottom up, you'll note that DB2 used both indexes to quickly retrieve the data requested by our sample application.

For more information about how to view and interpret DB2 data access plans, see Resources.

Back to top

Querying transient and persistent XML data

The XML Feature Pack also enables you to write XQuery expressions that join transient XML data—perhaps generated by web services or Java applications—with persistent XML data. As you might imagine, there are various ways to achieve this. We'll explore one approach here.

Join scenario

Consider an application that needs to analyze investments in derivatives. One aspect of this application might need to obtain current market data about the firms referenced in credit default swap trades. A simple example of such market data might include information about current stock prices. Assuming we have derivatives trade records stored in DB2 and can obtain stock information from a web service, our application needs to join persistent and transient XML data.

Sample Java EE code for our join scenario

Listing 6 illustrates one way to use the XML Feature Pack to perform the necessary work. We use the same query as in Listing 3 to obtain credit default swap from DB2, so we won't repeat it here. We also use an appropriate resolver to execute the query, much as we did in the previous scenario.

What's new in Listing 6 is the definition of a different XQuery executable—specifically, an XQuery function that will process the join in the XML Feature Pack. In addition, Listing 6 creates a StreamSource object to represent the transient XML data that will be joined with the appropriate trade data stored in DB2. For simplicity, our StreamSource is populated with data from a file named assets.xml, which we use to represent transient market data useful for portfolio analysis. (However, in a production application, this XML data is more likely to originate from a web service or a message queue.)

// Create the XQuery executable.   
Source source = 
    new Source(FpMLServlet.class.getResource("/joinCreditDefaultSwap.xq").toString());
XQueryExecutable joinCreditDefaultSwapsXQ = factory.prepareXQuery(source, staticContext);
...

// Declare the resolver and execute the join. 
// The resolver will issue the DB2 query, and WebSphere software will join its output 
// with XML data in the StreamSource object. 
JDBCCollectionResolver inputResolver = 
    new JDBCCollectionResolver(getConnection(), dbStatements);
dynamicContext.setCollectionResolver(inputResolver);
StreamSource source = 
    new StreamSource(FpMLServlet.class.getResourceAsStream("/assets.xml"));
dynamicContext.bind(new QName("http://com.ibm.xml.samples", "entityName"), name);
XSequenceCursor output = joinCreditDefaultSwapsXQ.execute(source, dynamicContext);

Listing 7 shows the contents of the assets.xml file.

<?xml version="1.0" encoding="UTF-8"?>
<assets>
     <equity>
          <symbol>AGU</symbol>
          <name>Agrium Inc.</name>
          <currency>USD</currency>
          <high>64.06</high>
          <low>62.79</low>
     </equity>
     <equity>
          <symbol>STM-FP</symbol>
          <name>STMicroelectronics N.V.</name>
          <currency>EUR</currency>
          <high>6.92</high>
          <low>7.2</low>
     </equity>
</assets>

Listing 8 contains the XQuery function that joins the transient and persistent XML data.

declare variable $my:entityName as xs:string external;

declare variable $databaseURI := 
    concat('jdbc://getCreditDefaultSwapsByEntityName?cd%&', $my:entityName); 
declare variable $creditDefaultSwaps := collection($databaseURI);

declare function local:equityRows($root) {
     for $equity in $root//equity
     let $referenceEntity := $creditDefaultSwaps//fpml:referenceEntity
     where $equity/name = $referenceEntity/fpml:entityName
     return
          <tr xmlns="http://www.w3.org/1999/xhtml">
               <td>{ $equity/*:symbol/text() }</td>
               <td>{ $equity/*:name/text() }</td>
               <td>{ $equity/*:high/text() }</td>
               <td>{ $equity/*:currency/text() }</td>
          </tr>
};

<table border="1">
<tr>
     <th>Ticker Symbol</th>
     <th>Company Name</th>
     <th>High</th>
     <th>Currency</th>
</tr>
{ local:equityRows(/) }
</table>

The FOR clause of this function loops through the entity nodes contained in the transient XML data. The LET clause extracts referenced entity information from the collection of credit default swap data that DB2 returns after executing the named query defined in Listing 6. The WHERE clause joins the transient and persistent XML data based on asset names. The function returns information in XHTML format about the stock symbol, company name, high sales price, and currency for all firms referenced in the credit default swaps returned from DB2.

When joining transient and persistent XML data, it's important to consider where the join should occur. If one software tier contains a large quantity of data that needs to be joined with a small amount of data that resides on another software tier, it's generally most efficient to perform the join on the tier with the large quantity of data.

Back to top

Updating persistent XML data

Another common requirement of XML-based Java EE applications involves updating persistent XML data. Part 1 of this article series included a code sample that showed how to replace a persistent XML document with another document held in memory. Full document updates are certainly useful in some situations. However, many applications only need to update portions of an XML document. Let's explore how to do this.

DB2 supports the XQuery Update Facility, a standardized extension to XQuery that enables programmers to update specific XML nodes in a variety of ways. For example, programmers can add new nodes, delete nodes, update the values of elements or attributes, and make other types of updates. Sub-document updates often help improve runtime performance. Programmers simply specify the changes they would like to make to appropriate portions of XML documents. DB2 performs these updates directly on the server, minimizing the application programming logic and transfer of data that otherwise need to occur. By contrast, DBMSs that rely on CLOBs or BLOBs for managing XML require applications to retrieve XML documents from the database, parse these documents, update them as needed, and then write these documents back to the database. If only a small portion of a large XML document needs to be changed, the performance penalty with such an approach can be considerable.

Update scenario

Consider a scenario in which an application needs to change the value of an XML node. For example, two parties involved in a credit default swap trade might renegotiate terms related to the scheduled termination date of the trade, which is represented as a node in the FpML trade record. You need to update the FpML trade record stored in the database to reflect these new terms, which can be passed to a Java EE application as a transient XML message.

For compliance reasons, firms engaged in derivatives trades often represent modified trade records as new records in their databases. For tutorial purposes, we'll show how to modify the original FpML record directly.

Sample sub-document update of persistent XML data

Listing 9 contains an interactive SQL statement that updates XML data in the DOCUMENT column of the FPMLADMIN.FPML43 table.

update FPMLADMIN.FPML43 
set document = 
  xmlquery ('declare default element namespace "http://www.fpml.org/2009/FpML-4-7";
    transform copy $new := $x 
    modify do replace 
      $new/FpML/trade/creditDefaultSwap/generalTerms/scheduledTerminationDate 
    with 
      <scheduledTerminationDate xmlns="http://www.fpml.org/2009/FpML-4-7">
         <adjustableDate>
            <unadjustedDate>2011-05-05</unadjustedDate>
            <dateAdjustments>
               <businessDayConvention>FOLLOWING</businessDayConvention>
            </dateAdjustments>
         </adjustableDate>
         <comment>This is new.</comment>
      </scheduledTerminationDate>
    return $new' passing document as "x")
where comment like 'cd-ex10-long-us-corp-fixreg-47%'and
  xmlexists('declare default element namespace "http://www.fpml.org/2009/FpML-4-7";
    $fpml/FpML/trade/creditDefaultSwap/generalTerms
    /referenceInformation/referenceEntity[entityName="Agrium Inc."]' 
    passing document as "fpml")

The WHERE clause restricts the update to a specific credit default swap trade involving Agrium Inc. Since the logic of this clause is very similar to the logic presented in previous scenarios, we won't review it again here.

The interesting portion of this query is contained in the expression included in the XMLQuery() function call. After declaring an appropriate default namespace, this expression copies the original XML document value (see Figure 2) into the $new variable. The MODIFY clause replaces the node for the scheduled termination date with a new node. This new node effectively changes the original node in four ways:

1. The value of the unadjusted date (a child node) is changed to a new date (May 5, 2011).
2. The value of the business day convention (a child node) is modified to "FOLLOWING".
3. The child node for business centers is deleted. (As a result, the children of the business centers node are also deleted.)
4. A new child node is added for comments.

Finally, the RETURN clause returns the new node representing the scheduled termination date so that DB2 will update data in the DOCUMENT column with the modified data.

Sample Java EE code for the update scenario

Let's look at how this update operation can be implemented in a Java EE application. As you might imagine, we need to use parameter markers and escape characters when incorporating this query into an application. The first portion of Listing 10 illustrates how we converted the interactive UPDATE statement (in Listing 9) into a named query in our Java EE application. As in our previous scenarios, this example also relies on a resolver to execute the database operation.

// Define the database query. 
// In this case, the named query will update part of an FpML trade record. 
dbStatements = new HashMap<String, String>();
dbStatements.put(
  "updateScheduledTerminationDateByEnityName",
  "update fpmladmin.fpml43 set document = " + 
  "xmlquery('" +
    "declare default element namespace " + 
       "\"http://www.fpml.org/2009/FpML-4-7\"; " +
     "transform copy $new := $x " +
     "modify do replace " +
       "$new/FpML/trade/creditDefaultSwap/generalTerms/scheduledTerminationDate with $d "+
     "return $new' " +
     "passing cast (? as xml) " +
     "as \"d\", " +
     "document as \"x\"" +
  ") " +			
  "where comment like ? and " +
    "xmlexists(" +
        "'declare default element namespace " + 
            "\"http://www.fpml.org/2009/FpML-4-7\"; " +
        "$fpml/FpML/trade/creditDefaultSwap/generalTerms" +
            "/referenceInformation/referenceEntity[entityName=$name]'" +
        "passing document as \"fpml\", cast (? as varchar(100)) as \"name\"" +
     ")"
);
...

// Create an XSLT executable and execute it with the JDBC resolver 
Source source = 
  new Source(FpMLServlet.class.getResource("/updateCreditDefaultSwap.xsl").toString());
XSLTExecutable updateCreditDefaultSwapXSL = 
  factory.prepareXSLT(source, staticContext);
...
JDBCResultsResolver resultsResolver = 
  new JDBCResultsResolver(getConnection(), dbStatements);
dynamicContext.setResultResolver(resultsResolver);
dynamicContext.bind(new QName("http://com.ibm.xml.samples","entityName"),"Agrium Inc.");
dynamicContext.bind(new QName("http://com.ibm.xml.samples","tradeType"),"cd-ex10-long%");
dynamicContext.bind(new QName("http://com.ibm.xml.samples","updateOrRestore"),"update");
...

// "newDate" is XML and comes from a non-DB2 XML data source
XItemView newDate = getUpdatedTerminationDate();
dynamicContext.bind(new QName("http://com.ibm.xml.samples",
   "updatedScheduledTermination"), newDate);
...
StreamResult result = new StreamResult(servletResponse.getOutputStream());
updateCreditDefaultSwapXSL.execute((Source)null, dynamicContext, result);

Instead of using an XQuery program (as we did in previous scenarios), we used XSLT 2.0 stylesheet in this example. Listing 11 illustrates a portion of our XSLT code.

<xsl:param name="my:entityName" as="xs:string" />
<xsl:param name="my:tradeType" required="yes" as="xs:string" />
<xsl:param name="my:updateOrRestore" required="yes" as="xs:string" />
<xsl:param name="my:updatedScheduledTermination" as="node()" />

<xsl:variable name="updateCreditDefaultSwapURL"
    select="concat('jdbc://updateScheduledTerminationDateByEnityName?--XML--&', 
       $my:tradeType, '&', $my:entityName)" />

<xsl:when test="$my:updateOrRestore eq 'update'">
    <xsl:result-document href="{$updateCreditDefaultSwapURL}" method="xml" indent="yes">
    <xsl:copy-of select="$my:updatedScheduledTermination" />
  </xsl:result-document>
</xsl:when>

Combining XML and relational data in new XML documents

Java EE programmers who want to obtain an XML-only representation of relational and XML data from the database tier can use DB2’s sub-document update support to help them achieve this goal. For example, programmers can write a single statement to enrich XML data with information extracted from relational columns of a table stored in DB2, Oracle, or some other DBMS. This capability can be useful in various situations because it enables programmers to combine XML and relational data across multiple systems. Even though this scenario isn't included in the sample code available for download with this article, the approach to executing such a query is the same as we discussed in previous examples.

To understand how to use DB2's sub-document update support in this manner, imagine that our FpML database includes a traditional relational table that tracks contact information for parties frequently involved in derivatives trades. See Listing 12 for a definition of this table.

CREATE TABLE FPMLADMIN.PARTYCONTACTINFO (
          PARTYID VARCHAR(40) PRIMARY KEY NOT NULL,
          PARTYNAME VARCHAR(100),
          PHONENO BIGINT,
          EMAILID VARCHAR(100),
          ADDRESS1 VARCHAR(100),
          ADDRESS2 VARCHAR(100),
          CITY VARCHAR(100),
          ZIPCODE BIGINT,
          STATE VARCHAR(100),
          COUNTRY VARCHAR(100)
     )

FpML trade records often include only limited information about the parties involved in a derivatives trade, yet trade confirmations (contracts) often require more detailed information. As shown in Listing 13, a single DB2 statement can readily transform the relational data into XML elements, insert these elements into appropriate nodes of the XML document, and return a new XML document for processing.

select xmlquery ('declare default element namespace "http://www.fpml.org/2009/FpML-4-7"; 
  transform 
  copy $new := $message 
  modify for $i in $new/FpML/party
  return
    do insert 
      db2-fn:sqlquery("select 
         XMLELEMENT(NAME ""ContactInfo"", 
         XMLELEMENT(NAME ""Address1"", p.ADDRESS1), 
         XMLELEMENT(NAME ""Address2"", p.ADDRESS2), 
         XMLELEMENT(NAME ""CITY"", p.CITY), 
         XMLELEMENT(NAME ""STATE"", p.STATE), 
         XMLELEMENT(NAME ""COUNTRY"", p.COUNTRY),
         XMLELEMENT(NAME ""PHONE"", p.PHONENO)
         ) 
         from FPMLADMIN.PARTYCONTACTINFO p 
         where partyId=parameter(1)", $i/partyId/text()) 
         as last into $i
         return <newroot>{$new}</newroot>'
  passing F.DOCUMENT as "message") 
FROM FPMLADMIN.FPML43 f  
where id=47022

If the relational data resided in a remote DB2 or OEM database, we would simply create a nickname for this remote table in our database. Nicknames represent remote database objects to the local DB2 server and allow programmers to treat these remote objects as though they were local DB2 tables. Thus, the query in Listing 13 remains the same even if underlying data was stored in a remote DB2, Oracle, or other database.

Furthermore, we can easily modify this query to insert this enriched trade data into an XML column in our DB2 database. All we need to do is include an INSERT INTO . . . clause immediately before the SELECT clause in Listing 13. (We can take a similar approach with the query in Listing 9 if we want to preserve the original trade record and insert the modified trade as a new document in the database.)

Back to top

Summary

In Part 2 of this article series, we illustrated how Java EE programmers can work natively with XML in the application server and database server tiers. Through several sample applications, we processed large amounts of transient and persistent XML data in a server-side Java application using indexing and query filtering capabilities for both XML and relational data. Our examples also showed how to work with subsets of persistent XML data—specifically, how to extract only XML nodes of interest from the database as well as how to update only specific XML nodes within a document. Working with fragments of persistent XML documents avoids unnecessary data transfers between the application and database server tiers and also helps reduce some of the processing that otherwise needs to occur on the application server. Finally, we also illustrated how programmers can join transient and persistent XML data—an increasingly common programming requirement given the popularity of XML across many software components today.

Acknowledgments

Thanks to Lee Ackerman and Matthias Nicola for reviewing this article. Thanks also to Susan Malaika and her colleagues for their help in developing the DB2 scripts available for download with this article.

Back to top

Downloads

Information about download methods

Resources

Learn

• Programming XML across the multiple tiers, Part 1: Use XML in the middle tier for performance, fidelity, and development ease: Develop an all-XML solution using JDBC 4.0, SQLXML, and the WebSphere Server XML Feature Pack (Andrew Spyker and Bert Van Der Linden, developerworks, March 2010): Learn about a natural and performant approach to working with XML data in the database and the middle tier. In a sample web app, combine XML data across an XML database and Atom services to explore the approach.
  
  
• Introduction to XML (Doug Tidwell, developerWorks, August 2002): For an excellent introduction to XML, read this tutorial.
  
  
• Query DB2 XML data with XQuery (Don Chamberlin, Cynthia M. Saracco, developerWorks, March 2010): Learn about using XQuery with DB2 pureXML by following several examples in this introductory article.
  
  
• An introduction to XQuery (Howard Katz, deveoperWorks, January 2006): Start here to learn about XQuery and follow the links from this article to even more information.
  
  
• Get started with XPath: Learn the basics (Bertrand Portier, deveoperWorks, May 2004): In this introduction to XPath, learn what XPath is; the syntax and semantics of the XPath language; how to use XPath location paths; how to use XPath expressions; how to use XPath functions; and how XPath relates to XSLT.
  
  
• Getting started with DB2 for Linux, UNIX, and Windows: Get started with information compiled for those less experienced with IBM DB2. In just about two hours, these training materials will prepare you to take a series of self-study tutorials and to download and try IBM DB2 for Linux®, UNIX®, and Windows.
  
  
• DB2 pureXML wiki: Check out this wiki for a comprehensive set of links to demos, free downloads, technical papers, and more.
  
  
• DB2 pureXML Cookbook (Matthias Nicola and Pav Kumar Chatterjee, IBM Press): Read this comprehensive guide to DB2 pureXML technology for all supported platforms.
  
  
• Work with GPX XML data in DB2 9.5 using JDBC (Khurram Farnaaz, Ronny Bartsch, and Susan Malaika, developerWorks, January 2009): Learn more about working with DB2 pureXML and JDBC 4.0.
  
  
• Using the SQLXML data type: The Java data type for database type XML (Deepak Vohra, developerWorks, April 2008): Learn to create an XML document, store an XML document in a relational database, retrieve an XML document from a database, and navigate an XML document with the SQLXML Java data type.
  
  
• DB2 9.7 Info Center: Visit the complete product documentation for DB2 pureXML for Linux, UNIX, and Windows.
  
  
• WebSphere Application Server Feature Pack for XML: Visit the website dedicated to the WebSphere Application Server Feature Pack for XML.
  
  
• Information Center for the WebSphere Application Server Feature Pack for XML: Visit the complete product documentation for the WebSphere Application Server Feature Pack for XML.
  
  
• IBM WebSphere Application Server V7 Feature Pack for XML Fix Pack 7: Download the XML Feature Pack Fix Pack 1.0.0.7.
  
  
• WebSphere Community Blog: Read the community blog.
  
  
• Video: Getting started with the WebSphere Application Server Feature Pack for XML: Install the sample application described in this article and get started with the WebSphere Application Server Feature Pack for XML.
  
  
• Video: XML Feature Pack and Sample for XML Database Integration Setup (With DB2 pureXML) Part 1 of 2: Configure WebSphere Application Server and DB2 in order to run the blog checker sample application described in this article.
  
  
• XML area on developerWorks: Get the resources you need to advance your skills in the XML arena.
  
  
• My developerWorks: Personalize your developerWorks experience.
  
  
• IBM XML certification: Find out how you can become an IBM-Certified Developer in XML and related technologies.
  
  
• XML technical library: See the developerWorks XML Zone for a wide range of technical articles and tips, tutorials, standards, and IBM Redbooks. Also, read more XML tips.
  
  
• developerWorks technical events and webcasts: Stay current with technology in these sessions.
  
  
• developerWorks on Twitter: Join today to follow developerWorks tweets.
  
  
• developerWorks podcasts: Listen to interesting interviews and discussions for software developers.
  
  
• developerWorks on-demand demos: Watch demos ranging from product installation and setup for beginners to advanced functionality for experienced developers.
  
  

Get products and technologies

• DB2 Express-C: Download the free DB2 Express C edition, which includes pureXML.
  
  
• Websphere Application Server: Get IBM WebSphere Application Server for Developers, a fully licensed product available for download at no charge.
  
  
• IBM product evaluation versions: Download or explore the online trials in the IBM SOA Sandbox and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
  
  

Discuss

• DB2 pureXML forum: Participate in the forum.
  
  
• XML zone discussion forums: Participate in any of several XML-related discussions.
  
  
• developerWorks blogs: Check out these blogs and get involved.
  
  

About the authors

Andrew Spyker is a senior technical staff member (STSM) in the WebSphere Application Server development team. Using his five years of experience leading the performance team, he advises the performance team. As a SOA runtime architect, he is responsible for driving consistency across the WebSphere SOA runtimes; he mainly focuses on benchmarking strategy, performance, and XML consistency. Finally he creates and drives the XML strategy of the WebSphere portfolio. Most recently, he acted as the chief architect for the XML Feature Pack.

Cynthia M. Saracco is a senior solutions architect at IBM's Silicon Valley Laboratory who specializes in emerging technologies and database management topics. She has 23 years of software industry experience, has written 3 books and more than 70 technical papers, and holds 7 patents.

Robbert (Bert) Van der Linden is a senior technical staff member (STSM) at IBM's Silicon Valley Laboratory. He joined IBM in 2001 to architect pureXML in DB2 which culminated in the release of DB2 9 in 2006. In parallel, he engaged with many customers and partners to evangelize XML in the database and continues to work intensely with customers. Bert came to IBM from a startup company, Propel, where he led the design and implementation of the distributed and fault-tolerant middleware which hosted a scalable e-commerce application. Before that Bert worked for many years at Tandem Computers on the NonStop SQL, a database that ran many critical applications in the financial industry.

Guogen Zhang is a distinguished engineer in DB2 for z/OS development at IBM's Silicon Valley Laboratory. He is the chief architect and development lead responsible for the delivery of pureXML in DB2 for z/OS, and also a lead in the advanced SQL technology, and has delivered important functionality including pureXML, Materialized Query Tables (MQTs), XML publishing functions, star join, and union in view, etc. in DB2 for z/OS. He is a frequent speaker in many conferences, such as IOD and IDUG, and has been deeply involved in providing customers with pureXML solutions.

Rate this article

Comments

Back to top

Table of contents

• Overview
• Our application environment and sample database
• Querying persistent XML data
• Querying transient and persistent XML data
• Updating persistent XML data
• Summary
• Downloads
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into XML on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers