Mapping

BizTalk Mapper – How-to execute xslt statements and use the result as input to other functoids

This post will present a short demonstration of how to use the result of a Scripting functoid doing XSLT to something else then creating a node in the destination document.

This post relies on the following concept, and your familiarity with them:

The sample is simple, and what it does could be solved in a much simpler way. I am not out to show of some complex application – I want to show a very simple example, and let you apply it to whatever complex scenario where you think it may be a fit.

Here is the map:

image

Given this (extremely simple) input:

<ns0:Root xmlns:ns0="http://SwebugMapping.C">
  <Cars>
    <Car>
      <Registration>Registration_0</Registration>
      <Brand>Brand_0</Brand>
      <Color>Color_0</Color>
    </Car>
  </Cars>
</ns0:Root>

It will create the same output (phew!). Nothing fancy by far.

As you might have seen from the screenshot though, the magic of it all, and what I want to show, is how I use xslt to transfer the data via an (for this small scenario) obscure and unneccesarily complex yet (for more demanding scenarios) extremely powerful and dynamic mix in of xslt in an otherwise “normal” map. There are scenarios where this is very useful.

The first functoid uses the global variable conecept to create an global variable. It also holds a method returning a void that allows it to be connected to the root node of the destination without creating an output. Additionally it uses a trick where a scripting functoid will allow you to specify multiple methods within the functoids script, although only the first will be the one triggered to create the functoids output. This second method will be used to update the value of the global variable.

string MyStringValue;

void initglobals ()
{}

void SetMyStringValue(string s)
{
  MyStringValue = s;
}

The second functoid uses and scripting functoid with the xslt call template to transfer the registration node. But it also, in bold and italic below, calls the user defined csharp method we created in the first functoid that updates the global variable. It can perform virtually any type of xslt logic and that pass that in as a parameter, like the keyed cumulative sum – but in this case simply selects the value of the Color node and sets that value. Like I said – I’m not out to present a complex scenario. I’m out to make it simple to understand. Putting it to good use in a complex scenario is your job!

<xsl:template name="MyXsltCallTemplate">
  <xsl:param name="registration" />
  <xsl:variable name="var:u1" select="userCSharp:SetMyStringValue(string(Color/text()))" />
  <xsl:element name="Registration">
    <xsl:value-of select="$registration" />
  </xsl:element>
</xsl:template>

Lastly the third scripting functoid simply outputs the value of the global variable completing the map that creates the same output as it got input.

public string GetMyStringValue()
{
  return MyStringValue;
}

Cool huh? At least I hope I got you thinking about how to combine the power of xslt and the BizTalk Mapper for those complex scenarios where direct usage of xslt just makes so much more sense then trying to get the same result out of mad series of chaining of “regular” functoids which sometime border on bringing about semi-suicidal thinking.

/Johan

Mapping

BizTalk Mapper – How-to create global variables

One of the things that can be fairly useful in BizTalk maps is the ability to create global variables. These can be any .Net class that can be accessed from the map, everything from simple bool or int values to generic classes such as List<string>. You create these from within a c# scripting functoid and can use them from within the same or other scripting functoids.

A good place to create and initialize global variables is in a scripting functoid at the top of the map (or in another standardized location) so you always know where to look for them.

For any functoid to be triggered it needs to be connected to an output node, and it needs to contain (at least) one c# method. Yet the functoid containing your global variable initialization you often do not want creating output. To create a functoid that will initialize your global variables without creating output create a method that return a void. Such a method will not create any output, yet it will – when connected to node in the destination schema – be included in the resulting and called from the xslt. A good practice is to connect this functoid to your Root node, whatever that node might be named.

The following is an example of a map that creates and uses a global variable:

image

The functoids has the following content, from top to bottom:

string myGlobalString = "hello";

void InitGlobals ()
{
// you can do any initialization logic here
}

This code creates a global variable and has an empty method returning void to fulfill requirements yet yield no output. This is really the only snippet needed, the one that shows how to create a global variable, the rest simply show some examples of how to use it.

string GetGlobalString ()
{
  return myGlobalString;
}

This method takes no input, but returns the globally available string that we defined in the first functoid and returns it – just to show that it’s available.

string UpdateAndReturnGlobalString(string s)
{
  myGlobalString = s;
  return myGlobalString;
}

This method takes an input, and then updates the global variable with that input, before returning it. This is just to show that you can do things to your variable – it is not a constant. For example, had this been a collection, you could add items to it.

This post, although something fairly well know within the community of seasoned BizTalk developers, is meant to illustrate how you can think outside the Mapper designer and the xslt box when developing BizTalk maps. I also created it for the purpose of being able to refer to it as I create posts that are more advanced in nature, without having to explain this concept in those posts.

/Johan

Mapping

BizTalk Mapper – resolving decimal sum and separator issues, Keyed Cumulative Sum continued…

As a continuation of my Keyed Cumulative Sum post, where I did a key based summation using an xslt call template in a BizTalk map I got two main points of feedback worth repeating and showing.

Questions

  1. If I use Sum on a decimal number it gives me all sort of weird decimals in the Sum although it shouldn’t; ie a sum of 1.1 and 1.1 could result in 2.199999999999. How do I solve that?
  2. What if I am in Sweden (or Germany or some other country) and I want to use a comma (,) as a decimal separator instead of a period (.). How can I do that?

Answers

  1. You can use the xslt format-number function to remove erroneous decimal digits that are the result of using Sum on a decimal number.
  2. You can use the xpath translate function to replace a period with a comma. However this does not conform to an xsd:decimal, so make sure that the target schema does not have this field defined as a decimal if you wish to be compliant.

This will make the outlook of the xslt instead look like this (code is edited to improve readability):

<xsl:template name="OutputSum">
  <xsl:param name="param1" />
  <xsl:param name="param2" />
  <xsl:element name="Compensation_Amount">
    <xsl:variable name="SumOfNodes" select="//row[Compensation_SubCode=$param1 and   
Compensation_Level=$param2]/Compensation_Amount)"
/> <xsl:variable name="FormattedSum" select="format-number($SumOfNodes,'0.00')"/> <xsl:value-of select="translate($FormattedSum, '.', ',')" /> </xsl:element> </xsl:template>

The use of several variables are for maintainability only, and are not required. You could jam it all into the value-of select statement if you really want to.

/Johan

Mapping

BizTalk Mapper – Keyed Cumulative Sum

From time to time I get questions about mapping puzzles, and I love it! Send me more of those! (I realize might regret that statement later). This time the puzzle was how best to do a conditional cumulative sum, or keyed conditional sum, also possibly known as grouped conditional sum.

The scenario is this (I’ve removed some namespaces etc for readability):

<Compensation>
  <rows>
    <row>
      <Compensation_Code>1</Compensation_Code>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Level>1</Compensation_Level>
      <Compensation_Id>1113</Compensation_Id>
      <Compensation_Days>41856.50</Compensation_Days>
      <Compensation_Amount>25288084</Compensation_Amount>
      <Compensation_Tax>6690289</Compensation_Tax>
    </row>
  <row>
      <Compensation_Code>1</Compensation_Code>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Level>1</Compensation_Level>
      <Compensation_Id>1113</Compensation_Id>
      <Compensation_Days>41856.50</Compensation_Days>
      <Compensation_Amount>29627</Compensation_Amount>
      <Compensation_Tax>6690289</Compensation_Tax>
    </row>
    <row>
      <Compensation_Code>1</Compensation_Code>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Level>2</Compensation_Level>
      <Compensation_Id>1113</Compensation_Id>
      <Compensation_Days>41856.50</Compensation_Days>
      <Compensation_Amount>234348</Compensation_Amount>
      <Compensation_Tax>6690289</Compensation_Tax>
    </row>
  </rows>
</Compensation>

How do you best produce a map that as step one summarize all Compensation_Amount for each variation of Compensation_SubCode, and returns this (let’s call this scenario 1):

<Compensation>
  <rows>
    <row>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Amount>25552059</Compensation_Amount>
    </row>
  </rows>
</Compensation>

and as step two, summarize all Compensation_Amount for each combination of Compensation_SubCode and Compensation_Level, and return this (let’s call this scenario 2):

<Compensation>
  <rows>
    <row>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Level>1</Compensation_Level>
      <Compensation_Amount>25317711</Compensation_Amount>
    </row>
    <row>
      <Compensation_SubCode>1</Compensation_SubCode>
      <Compensation_Level>2</Compensation_Level>
      <Compensation_Amount>234348</Compensation_Amount>
    </row>
  </rows>
</Compensation>

You might start thinking custom xslt and xsl:sort or xsl:keys or muenchian methods and things like that. That’s one way to do it. I actually prefer using the mapper for most maps until performance forces me to go another way.

My solution for scenario 1:

clip_image001

My Solution for scenario 2:

clip_image002

I am not presenting a complete solution for scenario 1, since it’s really just a subset of the functionality required for scenario 2, but you should get the point from the explanation of scenario 2.

The magic of the scenario 2 solution happens in the three scripting functoids. The first one, connected to the destination root node contains the following snippet (again namespace deprived for readability):

List<string> keyList= new List<string>();
void initglobals() {}

It’s simply responsible for making sure that the variable keyList is set up. The empty method that return void ensures that I can connect this to an output node to make sure it triggers, yet it will produce no output.

The next functoid then makes use of that list, and adds all unique keys to it (which is unique combinations of the two keys separated by a secure character, in this case a colon):

public string AddKey(string key)
{
  if (keyList.Contains(key))
    return string.Empty;
  keyList.Add(key);
  return key;
}

The Not Equals functoid then makes sure that we will only see output when a new unique combination is found, by checking to make sure that the string outputted from the scripting functoids <> and empty string.

The third functoid then is responsible for doing the summation (I’ve inserted a linebreak and some spaces in the xpath statement for readability, so don’t just copy paste this):

<xsl:template name="OutputSum">
  <xsl:param name="param1" />
  <xsl:param name="param2" />
  <xsl:element name="Compensation_Amount">
  <xsl:value-of select="sum(//row[Compensation_SubCode=$param1 
              and Compensation_Level=$param2]/Compensation_Amount)" />
  </xsl:element>
</xsl:template>

This simple xslt call template takes the two key values in, and does a summation of all rows that has these key values set to these specific values.

Now someone might object and say that this would mean an unnecessary amount of summation gets done. But that’s actually not the case, the mapper is clever enough to only do this under the condition of the Not Equals functoid returning true, as the following xslt snippet shows (this is just a portion of the xslt generated):

<xsl:variable name="var:v3" select="userCSharp:LogicalNe(string($var:v2) , &quot;&quot;)" />
<xsl:if test="$var:v3">
  <row>
    ...
    <xsl:call-template name="OutputSum">
    <xsl:with-param name="param1" select="string(Compensation_SubCode/text())" />
    <xsl:with-param name="param2" select="string(Compensation_Level/text())" />
    </xsl:call-template>
  </row>
</xsl:if>

’So a short summation (pun intended) of the work involved:

1. Create a global variable, a list to hold keys

2. Add to that list once you discover a new key

3. Add logic to that discovery to only output your destination node when you hit a new key

4. Implement a xslt call template and use xpath to sum based on your keys

Mapping

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:

image image

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!

image

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:

image

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?

image

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.

Mapping

BizTalk mapper – Appending new records to existing (looping) records

Does BizTalk Server maps have patterns? What are patterns? Wikipedia states that it’s a pattern if

“…it happens 5 or more times…”

and that a design pattern in computer science

“…is a general reusable solution to a commonly occurring problem…”

To summarize, the solution that I’m going to present is certainly something that I’ve been up against a number of times, and its something that’s really quite useful, yet simple, and something that the BizTalk mapper does really well. It’s the task of data concatenation, or appending data if you’d rather call it that. It’s an implementation of the content enricher pattern.

Say that you have two schemas, for simplicity, lets call them Schema1 and Schema2. Now both of them are based on a kind of name/value looping structure. What you would like to do is to append the name/value pairs in Schema2 to the recurring record in Schema1 and produce a new, enriched schema.

image image

In Schema1, Record is repeating, and in Schema2, Row is repeating.

Now how do you combine that. That answer is… easily.

Now to do this combining you have to use an orchestration, since you can’t do a map with multiple inputs outside an orchestration. So we create an orchestration, get our messages created, and create a map and make it take two inputs, that is, we drop a transform shape, double click it and assign two input messages, and one output, like so:

image

After you open the map created, you will have two message parts under the root, like so:

image

Now we can do the mapping:

image

As always, the best way to check that the map really does what you are after is to look at the xslt generated. I’m not going to paste the entire xslt generate here, but if we take it down to just the important part, it’s this:

<ns0:Root>
  <Header />...
  <Records>
    <xsl:for-each select="InputMessagePart_0/ns0:Root/Records/Record" />...
    <xsl:for-each select="InputMessagePart_1/s0:Root/Rows/Row" />...
  </Records>
</ns0:Root>

The map we created will loop first the Record records, and then the Row records, and for each such found it will output (not shown) the Record record with a Name and Value.

Mission accomplished.

Just so there is no misunderstanding, here are sample documents. First the xml sent in (the combination of two xml messages conforming to the two schemas)

<ns0:Root xmlns:ns0="http://schemas.microsoft.com/BizTalk/2003/aggschema">
  <InputMessagePart_0>
    <ns1:Root xmlns:ns1="http://MappingConcatenation.Schema1">
      <Header>
        <ID>ID_0</ID>
      </Header>
      <Records>
        <Record>
          <Name>Name_1</Name>
          <Value>Value_1</Value>
        </Record>
      </Records>
    </ns1:Root>
  </InputMessagePart_0>
  <InputMessagePart_1>
    <ns2:Root xmlns:ns2="http://MappingConcatenation.Schema2">
      <Rows>
        <Row>
          <Name>Name_2</Name>
          <Value>Value_2</Value>
        </Row>
      </Rows>
    </ns2:Root>
  </InputMessagePart_1>
</ns0:Root>

 

and then the result:

<ns0:Root xmlns:ns0="http://MappingConcatenation.Schema1">
  <Header>
    <ID>ID_0</ID>
  </Header>
  <Records>
    <Record>
      <Name>Name_1</Name>
      <Value>Value_1</Value>
    </Record>
    <Record>
      <Name>Name_2</Name>
      <Value>Value_2</Value>
    </Record>
  </Records>
</ns0:Root>

For those saying that what I’ve done is nothing special. You are quite right, it’s not, but it’s a very useful pattern.