Example: Counting Database Table Rows
The mapping illustrated in this example is available as DB_UserList.mfd in the <Documents>\Altova\MapForce2024\MapForceExamples\ folder. This mapping extracts user records from a database table called "Users" and writes them to an XML file. The database column "Username" contains both the first name and the surname of a person (for example, "Vernon Callaby"). This mapping has the following goals:
1.For each record in the "Users" table, create a new Person element in the XML file.
2.Split the value extracted from the database field "Username" into two separate fields in the XML file ("First" and "Last").
3.For each record, find its sequential number compared to the number of total records present in the database (for example, "Record 1 of 4") and write this information to the Details element.
DB_UserList.mfd
As illustrated above, in order to achieve the first goal, a connection is drawn between the source "Users" table and the Person element of the target XML file. This ensures that, for each record in the source table, a new Person element will be created in the target.
The value of the field "Username" is supplied to the substring-before and substring-after functions. These two functions extract the text before and after the space character (" "), respectively, which takes care of the second mapping goal.
Finally, to achieve the third goal, the mapping uses the count function. The result of the count function is passed on to a variable. The variable ensures that this result is stored on the mapping and available when writing the "Details" element of each person to the target XML. Note that, for efficiency reasons, database records should be counted only once, so the variable scope is set to compute-when=once (see Changing the Context and Scope of Variables)