Updating tables using the XMLToSQL class

The XMLToSQL class provides the means to update relational database tables or insert new rows or columns into a table.

The Javadoc information for the XMLToSQL class and for the SQLProperties class that provides the data necessary for XMLToSQL to update database tables is available in help.

Using the XMLToSQL class and the template (.xst) file generated by the XML From SQL wizard or the SQLToXML class, you can update a table dynamically using a servlet or a Web service. The template file contains the information needed for the query, including your database user ID and password, SQL statement, and generation options. A setConnection method is available in the XMLToSQL class. You can use it to create your own connection or recycle connections for connection pooling.

You can update the values of any elements other than the ones used as the primary key. If your table does not have a primary key specified, the XMLToSQL class contains several methods that you can use to ensure that only the columns you expect to update are updated.

Note: XMLToSQL does not update the database table properly when the column type is Binary, BLOB, or CLOB. This applies for the INSERT and UPDATE actions.

As well, when updating a database table that has columns with the data type Date, Time, or Timestamp, the XML To SQL wizard or sqlxml JAR file might throw an SQLException if the string value in the input XML source is not accepted by the target database product. The date and time formats are slightly different from one product to another. You can use the SQL Builder to determine the correct formats of these types.

 

Related tasks

Generating XML from SQL using a wizard
Updating tables using an XML document

Related reference
Class com.ibm.etools.xmltosql.XMLToSQL
Class com.ibm.etools.sqltoxml.SQLToXML
Class com.ibm.etools.xmltosql.SQLProperties