BizTalk Map: Key Value pair with conditional cumulative concatenation

One of the good things in spending time in MSDN forums is you will be amazed with different scenario questions posted. These scenarios are quite common and used in different places, but would have made you think for a while. Going forward, I am going to blog those interesting scenarios to share with people and also as a quick reference for myself.

Recently there was a question in MSDN forum about BizTalk Map: Key Value pair with conditional cumulative concatenation

Let’s say you have a source schema and sample input instance like the following

Source schema:

SoruceSchema

 

 

 

 

 

Sample Input Instance:

<ns0:OrderDetails xmlns:ns0="http://fortuvis.OrderDetails">
 <ID>ID123</ID>
   <CustomerDetails>
     <KeyValue>
       <Fieldkey>FirstName</Fieldkey>
       <FieldValue>Ashwin</FieldValue>
     </KeyValue>
     <KeyValue>
       <Fieldkey>Something</Fieldkey>
       <FieldValue>xxxx</FieldValue>
     </KeyValue>
     <KeyValue>
       <Fieldkey>LastName</Fieldkey>
       <FieldValue>Prabhu</FieldValue>
     </KeyValue>
   </CustomerDetails>
   <CustomerDetails>
     <KeyValue>
       <Fieldkey>FirstName</Fieldkey>
       <FieldValue>Pharrell</FieldValue>
     </KeyValue>
     <KeyValue>
       <Fieldkey>Something</Fieldkey>
       <FieldValue>yyyy</FieldValue>
     </KeyValue>
     <KeyValue>
       <Fieldkey>LastName</Fieldkey>
       <FieldValue>Williams</FieldValue>
     </KeyValue>
   </CustomerDetails>
</ns0:OrderDetails>

In the above input schema, we need to map the fields where

  • You have the “Fieldkey” and “FieldValue” fields and you need to concatenate the values of “FieldValue” field, based on the values of “Fieldkey” field. In this case, you just need to add the values of “FieldValue” when the value of the corresponding “Fieldkey” is FirstName” or “LastName”
  • Also you need to concatenate the values for all the records satisfying the above condition within a “CustomerDetails” and not for all the key-value pair which matches the above condition.

When you actually try to do this map, you will have various issues. There are few things which we need to consider.

  • You can’t use “String Concatenate” functoid since the concatenation should happen on the values of the same field “FieldValue” but for different “CustomerDetails” records. So we need to use “Cumulative Concatenate” functoid.
  • It’s not just a simple use of “Cumulative Concatenate” functoid. We need to concatenate the value based on two conditions. FieldKey shall have values “FirstName” and “LastName”.
  • Also, we need to concatenate “FieldValue” values matching above condition, just with in its parent record – “CustomerDetails”.

The solution to do is map as shown below:

Cumulative Concatenate Map

  1. Use a “Logical Equal” functoid. Link “FieldKey” as one of its parameters. And add a constant “FirstName” as another parameter.
  2. Use a “Logical Equal” functoid. Link “FieldKey” as one of its parameters. And add a constant “LastName” as another parameter.
  3. Use a “Logical Or” functiod. Link outputs of the above “Logical Equal” functoids.
  4. Link the output of “Logical Or” functoid to a “Value Mapping” functoid as its first parameter. And link “FieldValue” field as the second parameter.

The above steps apply the condition that we want to apply before concatenation.  The condition as mentioned is to get the values of “FieldValue” field to concatenate only if the value of its corresponding “FieldKey” is “FirstName” or “LastName”.

  1. Drag a “Cumulative Concatenate” functoid. Link the output of “Value Mapping” to it. Now link the output of “Cumulative Concatenate” to the destination schema element where you want to output the concatenated full name. Since we want to concatenate the values within nodes of each CustomerDetails record, define the scope of the “Cumulative Concatenate” functoid by giving a second parameter as “1”.
  1. Use a “Looping” functoid. And link the “CustomerDetails” record from source schema to the “Customer” record in the destination schema. This functoid ensures that the concatenation happens for every “CustomerDetails” record.

The output of this map is as follows:

<ns0:CustomersDetails xmlns:ns0="http://fortuvis.CustomerDetails">
 <Customer>
   <FullName>AshwinPrabhu</FullName>
 </Customer>
 <Customer>
   <FullName>PharrellWilliams</FullName>
 </Customer>
</ns0:CustomersDetails>
Posted in: BizTalk, Map Tagged: , 2 Comments December 31, 2014

About M.R.ASHWINPRABHU

M.R.ASHWINPRABHU is the founder and CEO of Fortuvis Systems Limited, a consulting company specialised in Microsoft technologies. Ashwin is a highly experienced integration consultant who works with clients to deliver high quality solutions. He works as technical lead developer, application architect and consultant, specializing in custom applications, enterprise application integration (BizTalk), Web services and Windows Azure.

Comments (2)

  • Walter Michel May 19, 2015 - 12:26 am Reply

    Great example of using the cumulative concatenate. Thanks for sharing this.

  • veena May 11, 2017 - 5:33 am Reply

    Hi Ashwin,
    I have similar requirement, but its not concatenation though. I have map the value to the destination based on the key. Can you please suggest how we can do it.

Leave a Reply

Your email address will not be published. Required fields are marked *