BizTalk mapper – Using a message as a lookup and merging data

Following on my previous post on how to append records to existing (looping) records I am following up on that with a solution, or pattern as that’s a word in high fashion, for how to use a second message as a lookup table to merge or enrich data in the first message. In this sample we are not adding any new rows, we are enriching existing rows and merging the data of the second message into the records of the first document. Again, it’s a form of enrichment pattern.

Say for example that you have a list of products and now you need to do a lookup into some other system that owns a specific type of data, like for example when the first document only contains information about who the manufacturer of a product is, but you need to go to the logistics system to lookup who the distributor is as well, and enrich the document with that information before sending it to the ERP system. How would you do that? And just for the sake of argument, let’s asume that you would use a chunky interface to talk to the logistic system instead of a chatty – meaning you would of course send several products to the system and get several products, and their distributors, in response.

With the BizTalk mapper, using only the basic functoids provided, I don’t have a solution for that. I’ve tried different options, but the mapper just didn’t like what it saw. The solution then, is the advanced functoids, more specifically the scripting functoid and a custom xslt call template (it might as well be done using inline xslt for that matter, a call template is not crucial for the solution, it’s just my choice).

So to setup the scenario. Here are the two incoming schemas:

In both schemas Product is repeating.

As always when you have two messages as input to a map, the typical place you would place that map is inside an orchestration. So we create an orchestration, get our messages created, and create a map and make it take two inputs and one output and we are all set. Review my previous post for more info on this. In this post I’ll jump right to the mapping logic. First of, let me demonstrate using regular BizTalk mapper shapes what I would like to do, but that will not work!

What I want this to do is: “Loop all products, and the use the ID of the product to lookup the Distributor that we recieved from the logistics system and output that to the output”. But again, it doesn’t work. However if this would be a single product that I would need to enrich, the solution in the above image does work! (the loop in that case is of course ignored). But in this case I have multiple products.

For the sake of clarity I’ve added a loop. It isn’t really needed – the BizTalk mapper would have implied the loop had I not and produce the same xslt as the above would. I could connect the Schema2 product to the same loop functoid and that would introduce the same look as in my previous post, where it tries to loop the second messages Products as well, but that’s no good. I could even try to use an additional loop functoid, but that would just get blatantly ignored by the mapper.

Anyway, so what can we do. Enter the scripting functoid, enter xslt.

I can do this:

As you can see, I am now connecting a scripting functoid between the Product.ID of the input and the Product.Distributor of the output. The second message has no links at all! Instead the lookup logic is contained in the scripting functoid.

So how does this magic that the scripting functoids does look like?

And here is the actual xslt script:

<xsl:template name="MyXsltCallTemplate">

  <xsl:param name="param1" />

  <xsl:for-each select="../../../../InputMessagePart_1/s0:Root/Products/Product">

    <xsl:if test="string(ID)=$param1">

      <Distributor>

        <xsl:value-of select="Distributor" />

      </Distributor>

    </xsl:if>

  </xsl:for-each>                   

</xsl:template>
To explain the script in words, what it does is that it loops through the records until it finds the correct Product with the matching ID, and when it does, it outputs the Distributor information. I’m realize there is a way to do this without looping, but I’ll leave that particular gem for those who comment to point out.
A simple example:
Input:
<ns0:Root xmlns:ns0="http://schemas.microsoft.com/BizTalk/2003/aggschema">

  <InputMessagePart_0>

    <ns1:Root xmlns:ns1="http://MappingLookupPattern.Schema1">

      <Products>

        <Product>

          <ID>123</ID>

          <Manufacturer>Manufacturer_0</Manufacturer>

          <Distributor />

          <LotsOfOtherInfo>LotsOfOtherInfo</LotsOfOtherInfo>

        </Product>

      </Products>

    </ns1:Root>

  </InputMessagePart_0>

  <InputMessagePart_1>

    <ns2:Root xmlns:ns2="http://MappingLookupPattern.Schema2">

      <Products>

        <Product>

          <ID>123</ID>

          <Distributor>Distributor_0</Distributor>

        </Product>

      </Products>

    </ns2:Root>

  </InputMessagePart_1>

</ns0:Root>
Output:
<ns0:Root xmlns:ns0="http://MappingLookupPattern.Schema1">

  <Products>

    <Product>

      <ID>123</ID>

      <Manufacturer>Manufacturer_0</Manufacturer>

      <Distributor>Distributor_0</Distributor>

      <LotsOfOtherInfo>LotsOfOtherInfo</LotsOfOtherInfo>

    </Product>

  </Products>

</ns0:Root>
For those reading my previous post saying that what we did there was nothing special, I believe some of you will have found this example slightly more interesting. It’s a useful thing to be able to do in so many scenarios. And yes, I recognize that there are those of you who seldom use the mapper at all any more. I am still among those that think it has it’s benefits, especially since more often then not there are at least some junior members of the team that aren’t comfortable with pure xslt, and there is really no reason in that scenario to take an otherwise quite simple map to pure xslt when all you need is a small scripting functoid to do the trick.
Share this:
Twitter
Facebook
Like this:
Like Loading...


	
Related