BizTalk Advanced Mapping Tips – Inline XSLT – Selecting distinct nodes (grouping)

Posted: September 14, 2009  |  Categories: BizTalk Maps

One of the more challenging things to do in XSL 1.0 was getting a distinct list of values from a set of nodes. There is no simple syntax to write this type of XPath query. However, this is very easy to do in XSL 2.0:

<xsl:for-each select="distinct-values(…)">

But unfortunately, BizTalk do not support XSL 2.0.

In Sample 1, the transformation that needed to take place was to get the list of External Partners from a list of external employees.

<ns0:ExternalEmployees xmlns:ns0="http://SelectDistinctValues.Input">
   <Employee>
      <Name>Sandro Pereira</Name>
      <Company>DevScope</Company>
   </Employee>
   <Employee>
      <Name>Employee 1</Name>
      <Company>DevScope</Company>
   </Employee>
   <Employee>
      <Name>DemoEmployee2</Name>
      <Company>DemoCompany2</Company>
   </Employee>
   <Employee>
      <Name>Employee 2</Name>
      <Company>DevScope</Company>
   </Employee>
   <Employee>
      <Name>DemoEmployee</Name>
      <Company>DemoCompany</Company>
   </Employee>
</ns0:ExternalEmployees>

The first thing we need to do is to drag a Script functoid onto the Grid. Then drag a line to the element that we want to create output for.

To create the output in the format that we want we will start with the following XSLT.

<xsl:variable name="unique-companies" select="//Employee[not(Company=preceding-sibling::Employee/Company)]/Company" />

This creates variable named unique-companies and populates with a list of unique companies nodes. Now we need to start the output of our nodes and then loop through the companies. The following code will do that.

<xsl:for-each select="$unique-companies">
   <PartnerName><xsl:value-of select="."/></PartnerName>
</xsl:for-each>

The output looks like this:

<ns0:ListPartners xmlns:ns0="http://SelectDistinctValues.Output1">
   <PartnerName>DevScope</PartnerName>
   <PartnerName>DemoCompany2</PartnerName>
   <PartnerName>DemoCompany</PartnerName>
</ns0:ListPartners>

Sample 1 is composed by:

  • Input.xsd and Output1.xsd
  • Output1.xsd
  • Input.xml

The Sample 2, is a response to a question in MSDN thread:

Giving data like this:

<Data Header="AAA" date="2008-10-28" Name="a1" Value="1.0" />
<Data Header="AAA" date="2008-10-28" Name="a2" Value="2.0" />
<Data Header="AAA" date="2008-10-28" Name="a3" Value="3.0" />
<Data Header="BBB" date="2008-10-28" Name="a1" Value="1.0" />
<Data Header="BBB" date="2008-10-28" Name="a2" Value="2.0" />
<Data Header="BBB" date="2008-10-28" Name="a3" Value="3.0" />

It was a requirement to map for the following schema format:

<Data>
   <Header>AAA</Header>
   <date>2008-10-28</date>
   <Record>
      <Name>a1</Name>
      <Value>1.0</Value>
      <Name>a2</Name>
      <Value>2.0</Value>
      <Name>a3</Name>
      <Value>3.0</Value>
   </Record>
   <Header>BBB</Header>
   <date>2008-10-28</date>
   <Record>
      <Name>a1</Name>
      <Value>1.0</Value>
      <Name>a2</Name>
      <Value>2.0</Value>
      <Name>a3</Name>
      <Value>3.0</Value>
   </Record>
</Data>

The logic is that you have to get the distinct values from Header, and then for all these distinct values select all element from this particular value, in this sample all element that has AAA in Header and then all BBB.

The map looks like this:

Basically, I use two functoid scripts with inline XSLT:

  • In the functoid 1, I created a template that gets all records for a specific Header send by parameter
    <xsl:template name="NameValueTemplate">
       <xsl:param name="param1" />
       <xsl:for-each select="//Data[@Header=$param1]">
          <xsl:element name="Name"><xsl:value-of select="@Name" /></xsl:element>
          <xsl:element name="Value"><xsl:value-of select="@Value" /></xsl:element>
       </xsl:for-each>
    </xsl:template>
  • In the functoid 2, I select all distinct Header and call a template for each header
    <xsl:element name="Data">
       <xsl:for-each select="Data[not(@Header=preceding-sibling::Data/@Header)]">
          <xsl:element name="Header"><xsl:value-of select="@Header" /></xsl:element>
          <xsl:element name="date"><xsl:value-of select="@date" /></xsl:element>
          <xsl:element name="Record">
             <xsl:call-template name="NameValueTemplate">
                <xsl:with-param name="param1" select="string(@Header)" />
             </xsl:call-template>
          </xsl:element>
       </xsl:for-each>
    </xsl:element>

Sample 2 is composed by:

  • DataInput.xsd and DataOutput.xsd
  • DataMap.btm
  • DataInput.xml

Download BizTalk Project:

BizTalk Mapping – Selecting distinct nodes (grouping) (118.6 KB)
Microsoft | MSDN Code Gallery

Tags: BizTalk | Map | XSLT | Training

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

Leave a Reply

Your email address will not be published. Required fields are marked *

BizTalk360
BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Manage and monitor serverless
components effortlessly

Learn More
Atomicscope
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top