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).
One of my colleagues believes this can be beaten using functoids only. I will be the happiest of people if he is correct, and will let you know.
If anyone else is up for the challenge, do download the source code and give it a go, there are two sample files in the visual studio solution. The challenge is for you to get the output to look the same as the last screenshot in my blog post. Happy mapping 🙂
LikeLike
I have just wasted my time for the whole working day to make it work with functoids help, because I was not so familiar with xslt language. Finally I have found this article and made my service back to work. It was much easier to create first a map with functoids and then generate xslt and work with it, correcting the places where functoids were unable to do the magic somehow.
Thank you for this article!
LikeLike
You’re very welcome. I agree that starting with Functoids and overriding where necessary is the way to go. Even if you don’t need to override the XSLT for a given map, looking at it’s XSLT can give you more appreciation of how the functoids actually work.
LikeLike
Why did you use xslt for this? This action can be done very easily with the use of the looping functoid. It will loop through each record and based on your logic, create multiple records in your target. Add a looping functoid between the “Root” source node and the “MergedEmployeeDetails” target node.
LikeLike
What you are suggesting would work fine if all you wanted to do was merge records from the two messages into the same structure but the aim of this exercise is to effectively join records together when they have matching keys and merge the details from the two records into a single record.
If you still believe that you can achieve what I demonstrated in my post without xslt I would be very interested to hear how.
Thanks for the comment.
Cheers
Johann
LikeLike
Johann,
I was mistaken. I tried this a few times with only functoids, but was unsuccessful. I noticed the same errors with the for-each code the functoids were generating. I actually used your solution as a template for a project I am on now, and it was a huge help. Thanks for contributing!!
LikeLike
No problem at all. It is a pretty rare mapping requirement and only when you do encounter it do you get to appreciate the limitations of the looping functoid.
I believe that the BizTalk services mapper allows for more explicit looping so maybe when the technologies converge this won’t be a problem anymore.
LikeLike
Thanks so much for this, really helped today.
LikeLike
Glad it helped Paul 🙂
LikeLike