Defining scope for Record Count functoid:
- by ArunManick
Defining scope for Record Count functoid:
Problem: One of the most common scenarios in BizTalk is calculating the record count of repeating structure. BizTalk has come up with an advanced functoid called Record Count functoid which will give the record count for the repeating structure however you cannot define the scope for a Record Count functoid. Because Record Count functoid accepts exactly one parameter which can be repeating record or field element.
If somebody don’t know what “scope” means I will explain with a simple example. Consider that we have a source schema having a structure Country -> State -> City. Country will have various states and each state will have different cities. Now you want to calculate no. of cities present in each state. Here scope is defined at the parent node “State”. Traditional Record Count functoid will give the total no. of cities present in the source message and not the State level city count.
Source Schema:
Destination Schema:
Soultion #1: As the title indicates we are not going to add one more parameter to the record count functoid. Instead of that, we are going to achieve the solution with the help of Scripting functoid with Inline XSLT script. XSLT is basically the transformation language used in the mapping.
“No.OfCities” indicates the destination field name to which we are going to send the value. In count(City), “count” refers to built in XPath function used in XSLT and “City” refers to source schema record name. Here you can find the list of built-in functions available in XSLT.
The mapping will look like as follows:
The 2 Record Count functoids used in this map will give the total number of states and total number of cities as that of input message.
Soultion #2: If someone doesn’t like XSLT code and they wish to achieve the solution using functoids alone, then here is another solution.
Use logical Existence functoid to check whether “City” exist or not
Connect the output of Logical Existence functoid to the Value Mapping functoid with second parameter as constant “1”. Hence if the first parameter is TRUE it will give the output as “1”.
Connect the output of Value Mapping functoid to the Cumulative Sum functoid with scope as “1”
This will calculate the City count at the state level. The mapping will look like as follows:
Let us see the sample input and the map output.
Input:
<?xml version="1.0" encoding="utf-8"?>
<ns0:Country xmlns:ns0="http://RecordCount.Source">
<State>
<StateName>Tamilnadu</StateName>
<City>
<CityName>Pollachi</CityName>
</City>
<City>
<CityName>Coimbatore</CityName>
</City>
<City>
<CityName>Chennai</CityName>
</City>
</State>
<State>
<StateName>Kerala</StateName>
<City>
<CityName>Palakad</CityName>
</City>
</State>
<State>
<StateName>Karnataka</StateName>
<City>
<CityName>Bangalore</CityName>
</City>
<City>
<CityName>Mangalore</CityName>
</City>
</State>
</ns0:Country>
Output:
<ns0:Country xmlns:ns0="http://RecordCount.Destination">
<No.OfStates>3</No.OfStates>
<No.OfCities>6</No.OfCities>
<States>
<No.OfCities>3</No.OfCities>
</States>
<States>
<No.OfCities>1</No.OfCities>
</States>
<States>
<No.OfCities>2</No.OfCities>
</States>
</ns0:Country>
Conclusion:
This is my first post and I hope you enjoyed it.
-Arun
