Updating tables using an XML document

You can use the XML To SQL wizard to update relational database tables with the data in an XML document.

Prerequisite: Enable the XML development capability.

Before using this wizard, use the XML From SQL wizard to generate an XML document that contains data about the database tables used by a SQL query. Use the XML editor to modify the XML file with the data that you want to add or update.

For an update action, the data in the XML file that corresponds to the primary key is used to determine which row to modify in the database table. For example, if the database table has the column employeeid as its primary key, the employeeid element in the XML file is used to determine which row to update. Consider an XML file that has the following data: employeeid=1, salary=3 and employeeid=2, salary=4. The update action using this data modifies the table rows where employeeid=1 and employeeid=2.

The format of the file is critical to the successful completion of the XML to SQL mapping operation. There must be a root element in the XML file followed by elements that represent the tables in the database. Each table element can have subelements that represent the columns in the table. It is important that the names of the table and column elements exactly match the database table and column names.

After you have modified your XML file, you are ready to update a table or insert columns into a table.

To update a table using an XML document:

1. In the Navigator view of the Data perspective, right-click the XML file that contains the data for updating the table, and then click Generate > Database data. Use either an XML file that was generated by the XML From SQL wizard, or a file that has the same format as a wizard-generated file.

2. Complete the Database Connection page of the wizard by selecting or entering the information specific to your database program, and then click Next.

3. On the XML to SQL Update page of the wizard, ensure that the correct database and table are specified.

4. Select the schema to update and the action to perform, and then click Next. You can either update the table or insert a row in the table.

5. On the Columns Update Option page, select the columns that you want to update or add to the table. You cannot update the primary key. If there is no primary key, select a column from the table to use for update in the way that a primary key would be used.

6. Click Finish to close the wizard and update your database table.

 

Related tasks

Generating XML from SQL using a wizard
Updating tables using the XMLToSQL class