Introduction

IBM WebSphere Studio Application Developer (hereafter called Application Developer) is an application development product that supports the building of a large spectrum of applications using different technologies such as JSPs, servlets, HTML, XML, Web services, databases, and EJBs. In particular, Application Developer provides tight integration between XML and relational data. Application Developer supports all of the databases that WebSphere Application Server supports, including DB2, Oracle Sybase and Microsoft SQL Server.

This is Part 9 of a series of articles focusing on the XML tools provided with Application Developer. Part 9 explores the XML to SQL feature that lets you update relational database tables from XML documents. With this feature, you can perform data modification operations such as update, delete, and insert based on XML document contents. This article first presents the XML to SQL wizard, a visual tool that can be used for interactively updating database tables. A sample application will then show the usage of the major APIs.

Before proceeding, you should read the previous articles in this series:

• Part 1: Developing XML Schema.
• Part 2: Creating an SQL query.
• Part 3: SQL and XML together
• Part 4: Exploring the XML Editor.
• Part 5: Exploring the RDB to XML Mapping Editor.
• Part 6: XML Namespace demystified using the XML Schema and XML Editor.
• Part 7: Developing XSL stylesheets using the XSL Editor and XSL debugger.
• Part 8: Exploring the XML to XML Mapping Editor.

Part 3 is particularly important because, when combined with this article, it illustrates round-trip data access between relational databases and XML.

This tutorial consists of three major sections:

• Generate XML from an SQL query
• Update a database table from XML
• Sample application using the XMLToSQL API

To walk through this sample, you need to install Version 5.0 or 5.1 of WebSphere Studio Application Developer.

Back to top

Generate XML from an SQL query

1. We begin by using the VIDEOS database that we used in Part 3 of this series. Follow the steps in Part 3 to set up the database and create an SQL query against the EMPLOYEES table, which is shown in Figure 1.
   Figure 1. Creating an SQL query using SQL Builder
   
   
2. Once we build an SQL query, we will use the XML From SQL wizard (Figure 2) to generate an XML file with other artifacts, such as a grammar file and default XSL file. Before doing this, create a folder in VideoStoreProject to store all the result artifacts. In this tutorial we named the folder Employees.
3. To bring up the wizard, right-click on the select_employees statement, shown in Figure 1, then select Generate new XML.... Choose DTD as the schema definition, and name the Output folder /VideoStoreProject/Employees. This is shown in Figure 2.
   Figure 2. XML from SQL query wizard
   
   
4. Click Finish to generate the XML artifacts from the SQL query. This will generate XML, DTD, XSL, and HTML files in the output folder. The HTML results are shown in Table 1.

   Table 1. Generated SQL query result (select_employees.xml)

   EMP_ID	NAME	JOB_TITLE	HIRE_DATE	STORE
   10	Allan Baker	Manager	2000-01-01	1
   11	Carol Dan	Manager	2000-01-01	2
   20	Eric Frank	Sales Rep	2000-01-02	1
   21	Garry Hans	Asst Sales Rep	2000-02-01	1
   22	Ivan James	Sales Rep	2000-02-01	2
   12	Karen Law	Manager	2000-06-05	3
   23	Mike Nichols	Sales Rep	2000-06-06	3

   
   In the next section, we will modify the content of the resulting XML file, then update the database table to reflect the XML content change.

   Partial XML of generated SQL query result: (select_employees.xml):

   <?xml version="1.0" encoding="UTF-8"?> <SQLResult> ... <EMPLOYEES> <EMP_ID>21</EMP_ID> <NAME>Garry Hans</NAME> <JOB_TITLE>Asst Sales Rep</JOB_TITLE> <HIRE_DATE>2000-02-01</HIRE_DATE> <STORE>1</STORE> </EMPLOYEES> <EMPLOYEES> <EMP_ID>22</EMP_ID> <NAME>Ivan James</NAME> <JOB_TITLE>Sales Rep</JOB_TITLE> <HIRE_DATE>2000-02-01</HIRE_DATE> <STORE>2</STORE> </EMPLOYEES> ... </SQLResult>

   
   

Back to top

Update a database table from XML

A set of methods is provided for updating relational databases from XML. In this section, we will update the database table EMPLOYEES using the XML To SQL wizard.

1. First, we will modify the XML file generated in the previous section. Note, however, that we cannot update the primary key columns or any other key columns that you declare; in this case, EMP_ID is a primary key column. We will update two rows in employees.xml, below: Garry has been promoted to Sales Rep, and Ivan has been promoted to Manager of Store 1. We will copy select_employees.xml to employees.xml and modify the contents, as shown in bold text, below:

   Modified XML content for database update (employees.xml):

   <?xml version="1.0" encoding="UTF-8"?> <SQLResult> <EMPLOYEES> <EMP_ID>21</EMP_ID> <NAME>Garry Hans</NAME> <JOB_TITLE>Sales Rep</JOB_TITLE> <HIRE_DATE>2000-02-01</HIRE_DATE> <STORE>1</STORE> </EMPLOYEES> <EMPLOYEES> <EMP_ID>22</EMP_ID> <NAME>Ivan James</NAME> <JOB_TITLE>Manager</JOB_TITLE> <HIRE_DATE>2000-02-01</HIRE_DATE> <STORE>1</STORE> </EMPLOYEES> </SQLResult>

   
   

2. When the file has been modified, right-click on the file to invoke the XML To SQL wizard from the Navigator view. Alternatively, you can launch the wizard by selecting File => New => Other... => XML. The first dialog of the wizard, shown in Figure 3, is for preparing a JDBC connection. We can either create a new connection or reuse the existing connection Video that we previously created. Select the Use existing connection:, then Next.
   Figure 3. Database connection page
   
   
3. On the next dialog (Figure 4), select UPDATE from the Action drop-down list, since we will be updating the EMPLOYEES table. (The same table name may exist in other schemas, so be sure to select the correct schema.)
   Figure 4. Action (and optional Schema) selection page
   
   
4. On the last page of the XML to SQL wizard, select JOB_TITLE and STORE, which are the columns to be updated. The key column(s) are not selectable, and are indicated by a key icon. Click Finish to update the database table.
   Figure 5. Columns page
   
   

Back to top

Sample application using XMLToSQL API

In this section, we will develop a command line application program that uses the XMLToSQL API to update the VIDEOS database. With this application, you can update the records of the Employees table, insert new records into the table, and delete existing records, with an input XML file. The format of input XML files must be compatible with the one generated from the SQL To XML wizard. This sample program merely illustrates how the API can be used to perform database operations. A complete Javadoc for the API can be found at plugins_directory/com.ibm.etools.sqltoxml/jars/sqltoxml_javadoc.zip.

1. Prepare SQLProperties

This class is used to provide data necessary for XMLToSQL to update database tables. An instance of this class is used as an argument to the XMLToSQL constructor.

First, create an SQLProperties instance and provide the information necessary for the desired operation (including database userID, password, JDBC connection info, as well as an SQL command mode such as update, insert, or delete). The information can be provided either using the set-methods of the class, or through an external XST file that is optionally generated as part of SQL to XML.

The following sample code provides info using set methods.

Using SQLProperties:

import com.ibm.etools.xmltosql.*;
...
SQLProperties prop = new SQLProperties();
prop.setLoginId("db2admin");
prop.setPassword("db2admin");
prop.setJdbcDriver("COM.ibm.db2.jdbc.app.DB2Driver");
prop.setJdbcServer("jdbc:db2:VIDEOS");
prop.setSchema("XMLTEST");
prop.setAction(SQLProperties.UPDATE);

Note that from the above code snippet, the bold lines are not necessary if we intend to supply our own JDBC connection. This can be done by calling the XMLToSQL.setConnection(java.sql.Connection) method.

For our sample program, we will use an external XST file to load the information. The file content is shown below:

update_employees.xst:

<?xml version="1.0"?>   
      <SQLGENERATEINFORMATION>   
        <DATABASEINFORMATION>   
          <LOGINID>db2admin</LOGINID>   
          <PASSWORD>db2admin</PASSWORD>   
          <JDBCDRIVER>COM.ibm.db2.jdbc.app.DB2Driver</JDBCDRIVER>
        
          <JDBCSERVER>jdbc:db2:VIDEOS</JDBCSERVER>
        
        </DATABASEINFORMATION>   
        <OPTIONS>   
          <SCHEMA>XMLTEST</SCHEMA>   
          <ACTION>UPDATE</ACTION>   
        </OPTIONS>   
      </SQLGENERATEINFORMATION>

The following code shows how to load the above XST file into SQLProperties.

Code example: loadProperties()

import com.ibm.etools.xmltosql.*;
...
private SQLProperties loadProperties(String propertiesFile) throws
Exception {
  SQLProperties prop = new SQLProperties();
  prop.load(propertiesFile);
  return prop;
}

2. Create an instance of XMLToSQL

Once SQLProperties is prepared, we need to create an instance of XMLToSQL, which is used to insert, update, or delete rows in a database table using an XML document. The mapping between the XML structure and the database table is based on a set of simple mapping rules. The following XML fragment illustrates the mapping rules:

XMLToSQL mapping rules

<RootElement>  
           <EMPLOYEE>  
             <FIRSTNAME>CHRISTINE</FIRSTNAME>
             <LASTNAME>HAAS</LASTNAME>
           </EMPLOYEE>  
           <EMPLOYEE>  
             <FIRSTNAME>MICHAEL</FIRSTNAME>
             <LASTNAME>THOMPSON</LASTNAME>
           </EMPLOYEE>
        ...
         </RootElement>

• The root element can contain 0-n elements. The tag of the root element is irrelevant. All elements contained by the root will be processed based on the specified action type.
• Each element maps to a row in the corresponding table. In this example, the EMPLOYEE element maps to the EMPLOYEE table. The children of the EMPLOYEE element maps to the two columns in the EMPLOYEE table by name. That is, the FIRSTNAME element corresponds to the FIRSTNAME column in the EMPLOYEE table, the LASTNAME element corresponds to the LASTNAME column in the EMPLOYEE table.
• The XMLToSQL library will create the appropriate SQL statement (e.g. insert, update, or delete) based on the column value and the data type for the corresponding column in the table.

A good way to create a valid XML document is to first use the SQLToXML run time library to generate one, then modify it to supply new values for update. Then, you can use the XML To SQL wizard to unit test the XML document for validity before writing your code that will use the XMLToSQL run time library.

The SQLProperties that we constructed in the previous section is passed to the XMLToSQL constructor. After an XMLToSQL is created, primary key columns and target columns are set. The following sample class illustrates these steps:

Code example - execute()

import com.ibm.etools.xmltosql.*;
 import java.sql.*;

public class UpdateEmployees
 ...
 private void execute(String propertiesFile, String xmlFile, String
action, Connection connection) throws Exception {
   SQLProperties prop = loadProperties(propertiesFile);
   prop.setAction(action); // override action
if we want a different action than the one set in SQLProperties
   XMLToSQL xml2sql = new XMLToSQL(prop);
   xml2sql.addToKeyColumns("EMP_ID");
   xml2sql.addToUpdateColumns("JOB_TITLE");
   xml2sql.addToUpdateColumns("STORE");
   xml2sql.setTrace(true);
   if (connection != null)
     xml2sql.setConnection(connection);
   xml2sql.execute(xmlFile);
 }
 ...
 } // end UpdateEmployees

Three interesting things can be seen in the above code, demonstrating additional flexibility in configuring XMLToSQL:

• prop.setAction() is used to override the action specified in update_employees.xst. For example, if we wanted to delete the corresponding rows instead of updating them, we could use prop.setAction(SQLProperties.DELETE).
• If we want to display the SQL statements performed by XMLToSQL, xml2sql.setTrace(true) will print out SQL statements while the action is performed on the command line. There is another version of setTrace() we can use that takes java.io.PrintWriter as an additional argument. Refer to the Javadoc for details.
• Notice the xml2sql.setConnection() method. Without this, XMLToSQL creates a JDBC connection using the user-provided database information before executing an action, and then closes the connection after execution. However, an external connection can be provided using the setConnection() method, in which case XMLToSQL will use this connection instead. The user is responsible for cleaning up the connection in the latter scenario.

3. Execute the program

The main() method takes XST and XML file names from the command line and sets the action as UPDATE. Since update_employees.xst defined the UPDATE action already, overriding the action is not necessary unless we wanted to introduce the usage of the method. Similarly, the JDBC connection could have been created from a connection pool. Once the variables are set, we are ready to execute the action by calling the update.execute() method.

Code example - main()

import com.ibm.etools.xmltosql.*;
import java.sql.*;
...
static public void main(String[] args) {
  String propertiesFile = args[0];
  String xmlFile = args[1];
  String action = SQLProperties.UPDATE;
  Connection connection = null;
  try {
    UpdateEmployees update = new UpdateEmployees();
    update.execute(propertiesFile,xmlFile,action,connection);
  } catch (Exception e) {
    System.out.println("Error updating Videos database:
" + e);
  }
}

The download file provided includes the source code described in this article. Unzip the download file and copy the following files to the working directory:

• plugins_directory\com.ibm.etools.sqltoxml\jars\sqltoxml.jar
• plugins_directory\com.ibm.etools.sqltoxml\jars\xmltosql.jar
• plugins_directory\org..apache.xerces\xercesImpl.jar
• plugins_directory\org..apache.xerces\xmlParserAPIs.jar
• db2java.zip (DB2 JDBC driver) from your DB2 installation directory

The download file also contains these batch files for your convenience:

• build.bat: compiles UpdateEmployees.java.
• update.bat: updates the database.

To compile UpdateEmployees.java, type the following from the command line:
> build

To execute the program, type the following from the command line:
> update update_employees.xst employees.xml

Back to top

Conclusion

This article explained how to use WebSphere Studio Application Developer's XML To SQL wizard and the run time API to accomplish data round-tripping between XML and relational databases. The API can be used with any database products that support JDBC drivers. When preferable, a JDBC connection can be provided from an external source, such as a connection pooling mechanism.

Major relational database vendors recently began implementing the SQL/XML standard for generating XML from a database query. Some XQuery implementations have also begun to appear. However, since these standards currently focus on producing XML from a query, the combined use of the SQLToXML and XMLToSQL APIs will provide a reasonable way of moving data between relational databases and XML until these standards mature.

Back to top

Download

Name	Size	Download method
xmltosql_sample_pgm.zip	4 KB	FTP|HTTP

Information about download methods

Resources

• IBM WebSphere Developer Technical Journal
  
  
• Part 1: Developing XML Schema
  
  
• Part 2: Creating an SQL query
  
  
• Part 3: SQL and XML together
  
  
• Part 4: Exploring the XML Editor
  
  
• Part 5: Exploring the RDB to XML Mapping Editor
  
  
• Part 6: XML Namespace demystified using the XML Schema and XML Editor
  
  
• Part 7: Developing XSL stylesheets using the XSL Editor and XSL Debugger
  
  
• Part 8: Exploring the XML to XML Mapping Editor
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Generate XML from an SQL query
• Update a database table from XML
• Sample application using XMLToSQL API
• Conclusion
• Download
• Resources
• About the author
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

Dig deeper into WebSphere on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

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

Special offers