Storing the Repeating Records in an Array

Here we will see how to store the value of Repeating Records into an Array.

Problem:

Assume that you have an Employees schema and input message which has multiple Employee Records:

From the above input message, you need to find out the Employee record with highest salary. For example: from the above sample message, we want to map 3rd record (having the highest salary of 9000) from source to target.

Solutions:

Below are the available approaches to get this done:

1. Many people generally go with XSLT

2. Loop through the Input message, find and map only that employee who is having highest salary.

Lets talk about option 2 in detail and see how to achieve it.

Ø  Connect the Salary to a Concatenation Functoid and give a delimiter character which doesn’t occur in the input message.

Ø  Connect the output of the Concatenation Functoid to a Cumulative Concatenate Functoid

Ø  Now if you see the output of the above two Functoid, we get the below string:

5000~3000~9000~1000~

Ø  This is the salary of each Employee record, separated by a delimiting character.

Apply C# script to

·        Split the above string into an Array

·        Sort the Array

·        Find the highest Salary.

Ø  Connect the output of the above two Functoids to a Scripting Functoid and Use a small script to execute the steps a,b,c.

Ø  The output of the above script will give you a value of 9000, which is the highest salary in the input schema.

Ø  Now use Logical Equal Functoid to connect the output of the above script and Salary element of Input Schema and Connect to the Employee Record of Target Schema.

Ø  Now connect each element in Source to Target.

Now if you test the above map with the input message (at the top of this post), you get only one Employee in the output whose Salary is the highest.

Note: The above mentioned technique has the limitation that the delimiting character used in Step-1 should not occur in the Input element. Else, C# script will wrongly interpret the data.

In such a case, you can Loop through each repeating record using XSLT and add those items into an array. Refer my previous blog for more details: Calling C# code from XSLT