A common problem you might come across in orchestrations is the need to merge records in two different messages together based on key data.  You might even want to take it further and make it such that in those cases where there is no match for key data, you might want to make a record which only contains the data from the one source message that contained that key.

In this blog post, I will take you through the steps required to set this up, show you why you can’t achieve the desired result making use of the BizTalk mapper with the inbuilt functoids, and then show you how can achieve the desired result through the use of some creative XSLT in your map.

First for the setup.  Create the two source schemas, see the example below.  The EmployeeDetail and BankAccountDetail records should be set to have a max occurs of unbounded.

Next create the destination schema, see the example below.  The MergedEmployeeDetail record should have a max occurs of unbounded, and the Address and the BankAccountNumber elements should have a min occurs of 0.

Then lets create a property schema with an element called TransactionID, and lets promote the TransactionID element in the two source schemas based on this property.

Now lets create an orchestration that is activated on receive of the EmployeeDetails message, receives a BankAccountDetails message which has a matching TransactionID (you’ll need to setup a correlation set based on the TransactionID), transforms these two messages to the MergedEmployeeDetails message and sends it out to the MessageBox.

In case you didn’t know, when you create a BizTalk map in Visual Studio you are limited to having one source and one destination message, however there is a way to override this restriction.  If you add a transform shape into an orchestration and choose to create a new map rather than use an existing map then these restrictions are dropped.  This is because the only place in which a multi source/destination map can be executed is within orchestration.  I guess Microsoft wanted to ensure developers don’t try to use these maps elsewhere which is why they only let you create the maps from an orchestration…I do personally wish they wouldn’t try to handhold BizTalk developers quite so much.

Anyways, we are going to make use of this feature.  After receiving the EmployeeDetails and BankAccountDetails messages, drag in a transform shape and choose to create a new map based on the two source messages with the output message being a MergedEmployeeDetails message.

Now you might think that if you make use of some Equals and Value Mapping functoids that you might be able to satisfy your mapping requirement.  For arguments sake, try to setup your map as below.

Now lets run the below input message through the map.

 

Success, right?  See the output below.

But wait, there’s more.  What if we now try to add in multiple BankAccountDetail records into the second message.

Lets see what the output looks like?

Suddenly things aren’t looking quite so good anymore.  If you try to validate the map and view the xslt, you’ll notice that the map loops through all the EmployeeDetail records in the first message, however when it attempts to check for matching BankAccountDetail records, there is no loop.

 

You will find that there is no way to force the map to your will.  So some custom XSLT to the rescue.  Below is the XSLT I wrote to solve this issue.

 

Next I created a copy of the map and removed all links and functoids from the map.  I then added a scripting functoid which is connected to the MergedEquipmentDetail record in the output message, set the script type to Inline XSLT Call Template and pasted the XSLT in.

You’ll now get the results below, voila J

 

You’ll find the BizTalk 2010 source code for the above example here (click file and download to download the zip file).