Mapping a target element from database tables

To map a target element from a database table, set up the Mapping node to:
  • retrieve the relevant rows from the database
  • populate the message target elements with values from database
After you have added a database to the mapping, the Spreadsheet pane contains a $db:select entry in the Map Script column. By default, its value is fn:true(), which means that all rows are retrieved from the database table. In database SQL, you would restrict the number of rows by adding a WHERE clause to a database call; here is the equivalent method of restricting the number of rows in a Mapping node:
  1. In the Spreadsheet pane, click the $db:select row. This causes fn:true() to be put into the Edit pane.
  2. Edit the expression in the Edit pane to specify the correct condition for the database call. To help you achieve this, you can:
    1. Select any database columns that are relevant to the rows that are retrieved, and drag them from the Source pane to the Edit pane. These are the database column names that are used in the WHERE clause.
    2. Select any source message elements with values that are relevant to the rows that are retrieved, and drag them from the Source pane into the Edit pane. These are values against which the selected database columns can be matched.
    3. Open Content Assist by clicking Edit > Content Assist.
    4. From Content Assist, select the functions to apply to message elements in the database call.
Here is an example of a $db:select entry:
$db:select_1.BROKER50.JDOE.RESOLVEASSESSOR.ASSESSORTYPE = 'WBI' or $db:select_1.BROKER50.JDOE.
RESOLVEASSESSOR.ASSESSORTYPE = $source/tns:msg_tagIA81CONF/AssessorType 

A $db:select entry retrieves all qualifying rows, so it is possible that more than one row is retrieved. By default, the selection is treated as repeating, which is indicated by the ‘for’ row immediately below $db:select in the Spreadsheet pane. If you know that your database call will return only one row, you can delete this ‘for’ row.

After you have configured the $db:select, populate the target message from the database by dragging the database column from the Source Pane to the message element in the Target pane. The mapping is indicated by a line between the database column in the Source pane and the element in the Target pane. An entry for this map in Xpath format also appears in the Spreadsheet pane. Triangular icons appear in the Source and Target panes next to objects that have been mapped.

Related concepts
Message Mapping editor
Creating message mappings
Related reference
Mapping node
Message Mapping editor Source pane
Message Mapping editor Target pane
Message Mapping editor Edit pane
Message Mapping editor Spreadsheet pane
Message mappings