Scenario: Updating Relational Data from XML Messages

Before we introduce our example applications, let's describe the type of scenario our applications are designed to solve. Suppose a company allows its customers to update their current account information over the Internet. A customer request from a Web browser triggers an XML message, which is sent to an application that integrates data from the XML message and relational data stored in one or multiple relational databases, and updates the appropriate data in the databases.

In our scenario, the relational data that is maintained by the application is stored in a database table containing basic customer information. The table is created using the following SQL statement.

CREATE TABLE custinfo (
 custid integer primary key,
 firstname varchar(100),
 lastname varchar(100),
 address varchar(200)
)

For example, the custinfo table looks something like this:

custid firstname lastname address
1 Ryan  Adams 504 Cherry Lane, Norfolk, VA
2 Norah  Jones 1606 Meadowlake Street, Jacksonville, NC

Here's an example of an XML message to be processed by the application that maintains customer information:

<customers>
 <customer id='1'>
 <name>
  <first>Ryan</first>
  <last>Adams</last>
 </name>
 <address>504 Cherry Lane, Norfolk, VA</address>
 </customer>
 ...
 </customers>

In the following sections, we'll introduce two example applications that solve the problem described in our scenario. Both applications perform the following steps:

  1. Using DataDirect XQuery, the application extracts the necessary information from the XML message and determines which action needs to be performed on the custinfo table (insert, update, or delete data).
  2. The application passes the result of step 1 and the information that is extracted from the XML message in the XQuery environment to the Java environment. How our two applications perform this step differs slightly. Example 1 passes a string of 'insert', 'update', or 'delete' to trigger the appropriate action. Example 2 uses Java external functions to perform the same step.
  3. The application updates the custinfo table based on the action determined by step 1 and the information passed to the Java environment in step 2.