Problem Statement: I will be receiving two different messages (Message1 and Message2) inside orchestration each with multiple records. Now, I have to join these messages in a map inside orchestration based on TRANID and PROCID. So basically, I have to get the value of TRANID from Message1 and look for the record inside Message2 where value of PROCID matches to value of TRANID and get the value of SUM element and populate it in the output message and this process have to be repeated for every TRAN record of Message1.
Solution: There is already a very good solution for this and I found it at this blog <http://geekswithblogs.net/synboogaloo/archive/2005/04/22/37335.aspx>. But only thing is that in my case, input schema contains namespace which complicates the XPATH Query used in XSLT Call Template. Let us figure it out later, how to quickly build XPATH query using namespaces in this scenario.
1) First, create the schema for the three messages.
2) Now create orchestration like below. It will receive these two input messages, constructs the output message using map inside Transform shape and then sends the output to a file location. Since we have to receive two different messages using File adapter and we do not have any unique identifier for correlation purpose, I used BTS.ReceivePortName for correlation. Though, this may not be very practical, I just want to demonstrate joining of two schemas when they have got namespace. But yes, make sure that both the receive locations are in the same Receive Port.
3) In the transform shape, select the msgInput1 and msgInput2 as Inputs and msgOutput as Output. Open the map now.
4) In the map, put a looping functoid as in the figure below. Link TRANID and DESC from input to output. The main thing remaining is to retrieve value of SUM from INPUT2 when TRANID matches to PROCID. Now drop a scripting functoid to the map and connect it to TRANID as Input and SUM as Output. It should look something like below.
5) Now, for the scripting functoid, things would have been something like below if the schema does not have the namespaces. But here, this XPATH query inside the ‘Inline XSLT Call Template’ will give us only blank results.
6) So we need to use the namespaces prefix in out query to get the desired output. Now we need to find out the namespace prefix being used in map for INPUT 2 schema. To find it out, we need to view the XSLT of the map. Therefore, we have to validate the map file first. So, right click the map file and say validate map. Now in the output, we will get a link to the map XSLT. Open this XSL file. Now in this XSL file, we can find the prefix used for the namespace “http:\\Input2” and that is ‘s1’.
7) Now, use the prefix "s1" inside the XPATH Query as below. There could be other ways to use XPATH Query but this was the best I could figure out.
8) Deploy the solution and test it. That’s all.
You can download my Biztalk project and try it. If you got a better solution than this, please let me know. I would really be interested in knowing that.
I hope this post helps!