Update if... combinations - with delete child data

Previous Top Next

This section describes the effect of the Update if... condition on a parent table combined with each of the possible table actions defined for related child tables. The "Delete data in child tables option" is active in all but one of these examples. You can continue to use the mapping from the previous section, for this section.

action-ins1

Files used to illustrate this example:

•	Altova_hierarchical.xsd

•	Altova-cmpy-extra.xml

•	Altova.mdb

Update if.. on parent table, Insert all... on child table

Parent table - Department
Table action		Update if...	compare PrimaryKey
Delete data in child tables	þ
Ignore input child data	o
child table - Person
Table action		Insert all...	compare PrimaryKey
Delete data in child tables
Ignore input child data

Result:

•	Updates parent table data (Department records)

•	Deletes child data of those tables which satisfy the Update if... condition (Person records).

Retains existing database tables (Engineering in this case) which do not have a counterpart in the input XML file (no source keys for the Update if... comparison).

•	Inserts all Person records from the input XML-instance. This also includes new records that might not already exist in the database.

Update if... on parent table, Update if... on child table

Parent table - Department
Table action		Update if...	compare PrimaryKey
Delete data in child tables	þ
Ignore input child data	o
child table - Person
Table action		Update if...	compare PrimaryKey
Delete data in child tables
Ignore input child data

Result:

•	Updates parent table data (Department records).

•	Deletes child data of those tables which satisfy the Update if... condition (Person records).

Retains existing database tables (Engineering in this case) which do not have a counterpart in the input XML file (no source keys for the Update if... comparison).

•	Update if... condition, defined for the Person table, fails because all Person records in the database have been deleted by the "Delete data in child tables" option. There is no way to compare the database and XML data primary keys, as the database keys have been deleted. No records are updated.

Update if... on parent table, Delete if... on child table (Delete data in child tables - active)

Parent table - Department
Table action		Update if...	compare PrimaryKey
Delete data in child tables	þ
Ignore input child data	o
child table - Person
Table action		Delete if...	compare PrimaryKey
Delete data in child tables
Ignore input child data

Result:

•	Updates parent table data (Department records).

•

Deletes child data (Person records) from all Departments because the "Delete data in child tables" option is active. All Person records are deleted for each Department which has a corresponding PrimaryKey in the source XML. I.e. even Person records of the database which have no counterpart in the source XML, are deleted.

Retains existing database tables (Engineering in this case) which do not have a counterpart in the input XML file (no source keys for the Update if... comparison).

•	The child table data (Person records) are deleted before the Table action, Delete if..., is executed, no records are deleted.

Update if... on parent table, Delete if... on child table (Delete data in child tables - deactivated)

Parent table - Department
Table action		Update if...	compare PrimaryKey
Delete data in child tables	o		Delete data... not active !
Ignore input child data	o
child table - Person
Table action		Delete if...	compare PrimaryKey
Delete data in child tables
Ignore input child data

Result:

•	Updates parent table data (Department records).

Retains existing database tables (Engineering in this case) which do not have a counterpart in the input XML file (no source keys for the Update if... comparison).

•	Delete if... only deletes those Person records for which a corresponding Person PrimaryKey exists in the source XML file.

•	Database records which do not have the corresponding Person key, are retained.

To see a further example involving duplicate items, Insert, Update and transactions, please see the Customers_DB.mfd sample file available in the C:\Documents and Settings\<username>\My Documents\Altova\MapForce2008\MapForceExamples folder. The example shows how XML schemas and database sources can be mapped to target databases.

In the example:

•	XML Schema to database:

Customers and Addresses exist in the target database. These entries are updated with the new data from the from the source XML Schema/document. The FirstName an LastName items are used to find the correct rows in the database.

•	Database to database:

Address and Person data are supplied by the database source and are inserted into the database. The target table (Customers) is duplicated

CustomerID for each record are created anew, with the initial value being A1000.

db-target-eg

Previous Top Next