Updating a Table
This example shows you how to update data of an existing database table with data coming from an XML source. The example uses the following files:
•altova-cmpy.xml — contains the source data to be inserted into the database.
•Altova_Hierarchical.xsd — the schema used to validate the instance file above.
•Altova.sqlite — the target database to be updated.
All files are available in the <Documents>\Altova\MapForce2023\MapForceExamples\ folder. Below, the complete path to them will be omitted, for simplicity.
The mapping in this example modifies a sample database file. It is strongly recommended to back up the original database and start with a new copy before following the steps below. This ensures that the original examples are not overridden and that you get the same results as below. For more information, see Executing Mappings Which Modify Databases. |
The goal of the mapping is to update all records in the "Person" table with instances of "Person" from the XML document. Each person in the XML file has a PrimaryKey child element. Each person in the "Person" table has a PrimaryKey column. Only those records where a person's PrimaryKey in the XML file corresponds to a person's PrimaryKey in the database must be updated.
To achieve the mapping goal, we will take the steps below.
Step 1: Insert the source XML component
•On the Insert menu, click XML Schema/File, and browse for Altova_Hierarchical.xsd. When prompted to supply an instance file, browse for altova-cmpy.xml.
Step 2: Insert the target database
•On the Insert menu, click Database, and go through the wizard steps to connect to Altova.sqlite. See Add a Database.
Step 3: Draw the connections
•Draw the mapping connections as shown below.
Step 4: Configure the Update action
1.On the target component, click the Action: Insert ( 
) button next to the "Person" table.
2.Next to Action on record, select Update if... . This changes the database table action to a conditional update action. That is, the current record will only be updated when a condition is satisfied (see next step).
3.Next to PrimaryKey, select the value equal, as shown below. This defines the update condition: that is, the database record will be updated only when its PrimaryKey value is equal to the PrimaryKey value coming from the mapping.
In this example, the equality operator is applied to the PrimaryKey field, which is a likely scenario when updating databases. Note that conditions can also be defined on other fields which are not necessarily primary keys. For example, by selecting equal next to the First and Last fields, you would update only those records where both the first and last name is equal to that in the source XML.
Optionally, select the NULL equal check box. In this example, this check box is irrelevant, because neither the PrimaryKey field in the source XML nor the PrimaryKey field in the database contains any null values. However, you should select this check box if your data contains null values, and if you want to treat such null values as equal; otherwise, you may get undesired results. For more information, see Handling Nulls in Database Table Actions.
4.Click OK to close the dialog box. Notice that, back on the mapping, the Action: Insert button has now changed to an Action: Update ( 
) button. This indicates that an update action is configured to take place for this table.
Step 5: Preview the mapping and update the database
Click the Output pane to preview the mapping. A SQL script is generated, containing actions to be executed against the database. The script has not modified the database yet; it is only for preview. To run the script against the database:
•On the Output menu, click Run SQL-Script.
| Note: | Running the SQL script directly from MapForce is just one of the ways to update the database, see also Executing Mappings Which Modify Databases. |