When database tables do not have explicitly defined relationships between them, you can define such relationships locally in MapForce. In particular, you can create, from MapForce, primary and foreign key relationships between columns of different tables, without affecting the database in any way. Any database columns can be used as primary or foreign keys. Also, new relations can be created, in addition to those existing in the database. Locally defined relationships are saved together with the mapping.
These "on-the-fly" relationships are called Local Relations in MapForce. The following table lists all the possible fields between which you can define local relations. Mixed relationships are possible (for example, mapping the output of a stored procedure to a database column). The fields taking part in the relationship must have the same, or a compatible, data type.
•Column of a database table or view
•Output parameter or return value of a stored procedure, see also Stored Procedures
•Column of a recordset returned by a stored procedure*
•Column of a user-defined SELECT statement, see also SQL SELECT Statements as Virtual Tables.
•Column of a database table or view
•Input parameter of a stored procedure
•Input parameter of a user-defined SELECT statement
* Applicable if the stored procedure is called either as data source (without parameters) or as a function (with input and output parameters). In order for the recordset to become available for selection, you must execute the stored procedure once, to retrieve the recordset.
The altova-no-relation.mdb database used in this example is a simplified version of the altova.mdb database supplied with MapForce. The "Person" and "Address" tables, as well as all table relationships have been removed in Microsoft Access. As illustrated below, none of the tables visible in the altova-no-relation tree have any child tables; all tables are on the same "root" level. The content of each table is limited to the fields it contains.
Database structure with no explicit relationships
The aim of the example is to display the offices of "Altova" and show the departments in each office. Note that, in the altova-no-relation.mdb, the primary and foreign key relationships do not exist explicitly, as mentioned above. They exist only logically (implicitly), so we will be re-creating them locally in MapForce to achieve the goal of the mapping.
Local relations can be defined while adding a database to the mapping, or by right-clicking an existing database component and selecting Add/Remove/Edit Database Objects from the context menu, as illustrated in the steps below.
1.On the Insert menu, click Database.
2.In the connection wizard, click Microsoft Access (ADO), and then click Next.
3.Browse for the altova-no-relation.mdb database available in the <Documents>\Altova\MapForce2020\MapForceExamples\Tutorial\ folder, and click Connect.
4.Select the User Tables check box.
5.Click the Add/Edit Relations button in the icon bar.
6.The "Add/Edit Table Relations" dialog box opens. Click Add Relation.
7.Select values from the two drop-down lists so as to create a primary and foreign key relationship between the "Altova" and "Office" tables, as illustrated below. The two drop-down lists allow you to select the tables or database objects you want to create relations for. The left list specifies the object which stores the primary/unique key, while the right one specifies the foreign key object. The Primary/Unique Key object will be the parent object in MapForce, and the Foreign Key object will be shown as child in the database component (see also Handling Database Relationships).
8.Click OK to complete the local relation definition, and then click the Insert button to insert the database into the mapping area.
At this stage, you have created a local relationship between the PrimaryKey column of the "Altova" table and the ForeignKey column of the "Office" table. As illustrated below, the "Altova" root table is now a parent to the "Office" table. Namely, the Office table is shown as a related table below the Altova table with its own expand icon.
However, the mapping goal is not yet complete. To complete the mapping goal, use the same method to create a relationship between the Office and Department tables, as shown below.
To open again the "Add/Edit Relations" dialog box, right-click the database component, and select Add/Remove/Edit Database Objects from the context menu.
Finally, add the target schema to the mapping as follows:
1.On the Insert menu, click Insert XML Schema/File.
2.Browse for the Altova_Hierarchical.xsd file available in the <Documents>\Altova\MapForce2020\MapForceExamples\ folder. When prompted to supply a sample XML file, click Skip. When prompted to select a root element, select "Altova".
Notice that, in order to preserve relationships between tables in the target XML, all connections were drawn from the same "root" table, hierarchically (in this case, "Altova"). For more information, see Handling Database Relationships.
Having defined the mapping as shown above, click the Output tab, to preview the result. The mapping result shows the department elements nested under each respective office, which was the intended goal of this mapping.