BizTalk Server: Create repeating records from comma-separated values

Article
1/17/2024

This article describes how to create repeating records from character-separated data in an element. It is a slight rewrite of my blog article Create repeating records from comma-separated field.

This article requires some knowledge on XSLT processing in BizTalk map.

Problem

Sometimes you receive XML data with several values packed into a single element, separated with commas or some other character. Your target schema needs a separate record or element for each such value.

Source XML:

<Root> <goodsrow>  <weight>weight_0</weight>  <packageIDs>packageID_0,packageID_1,packageID_2</packageIDs> </goodsrow></Root>

Target XML:

<Root> <goodsrow>  <weight>weight_0</weight>  <packageIDs>packageID_0</packageIDs> </goodsrow> <goodsrow>  <weight>weight_0</weight>  <packageIDs>packageID_1</packageIDs> </goodsrow> <goodsrow>  <weight>weight_0</weight>  <packageIDs>packageID_2</packageIDs> </goodsrow></Root>

Solution

This solution uses a recursive XSLT template. As with all recursion, you must define the recursive function and the base case. The base case is that there is only one packageID in the <packageIDs> element. The recursive function generates a <goodsrow> for the head, and recurses (calls itself) with the tail (the rest of the packageIDs).

To keep the article simple, both the source and target document uses the same schema. In reality, you probably have different schemas for source and target.

How To Start

It is convenient to let Visual Studio do the boilerplate work of creating the skeleton XSLT, by creating an empty map with a looping functoid, then validating the map. Visual Studio now generates the XSLT and a companion extension XML file for you (see the output window for the file names and path), which you copy to your solution. Sandro Pereira has some advice on custom XSLT which is highly recommendable. The recommendation is that you let the XSLT base file name be the same name as your map, with the .xslt extension. Copy both the XSLT and the _extxml.xml file into your solution and add them to your project (don't forget to add them to your source control too). Finally, in Visual Studio Mapper, click somewhere in the mapper area and then look in the properties window: Set the properties for "Custom XSLT Path" and "Custom Extension XML" to their respective file names in your solution.

The skeleton XSLT

The Visual Studio-generated XSLT begins with a template matching the root and then a template matching the root element with the loop there for you:

<?xml version="1.0" encoding="UTF-16"?><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var" version="1.0" xmlns:ns0="http://BizTalk_Server_Project4.Schema1">  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />  <xsl:template match="/">    <xsl:apply-templates select="/ns0:Root" />  </xsl:template>  <xsl:template match="/ns0:Root">    <ns0:Root>      <xsl:for-each select="goodsrow">        <goodsrow />      </xsl:for-each>    </ns0:Root>  </xsl:template></xsl:stylesheet>

Add your templates

Add the two templates below, the recursive template and the base case template that actually generates a <goodsrow> element.

The recursive template

  <!--  The recursive template which calls itself (note the plural name):  -->  <xsl:template name="GenerateGoodsrows">    <xsl:param name="weight" />    <xsl:param name="packageIDs" />     <xsl:variable name="separator" select="','" />     <!-- Retrieve the head (first or only packageID): -->    <xsl:variable name="headPackageID">      <xsl:choose>        <xsl:when test="contains($packageIDs, $separator)">          <xsl:value-of select="substring-before($packageIDs, $separator)"/>        </xsl:when>        <xsl:otherwise>          <xsl:value-of select="$packageIDs"/>        </xsl:otherwise>      </xsl:choose>    </xsl:variable>     <!-- Retrieve the tail (any remaining packageIDs): -->    <xsl:variable name="tailPackageIDs" select="substring-after($packageIDs, $separator)" />     <!-- Generate a goodsrow for the head: -->    <xsl:call-template name="GenerateGoodsrow">      <xsl:with-param name="weight" select="$weight" />      <xsl:with-param name="packageID" select="$headPackageID" />    </xsl:call-template>     <!-- Generate the rest recursively with the tail (if any remains): -->    <xsl:if test="$tailPackageIDs">      <xsl:call-template name="GenerateGoodsrows">        <xsl:with-param name="weight" select="$weight" />        <xsl:with-param name="packageIDs" select="$tailPackageIDs" />      </xsl:call-template>    </xsl:if>  </xsl:template>

The base case template

  <!--  Base case: A template that generates exactly one goodsrow:  -->  <xsl:template name="GenerateGoodsrow">    <xsl:param name="weight" />    <xsl:param name="packageID" />    <xsl:element name="goodsrow">      <xsl:element name="weight">        <xsl:value-of select="$weight" />      </xsl:element>      <xsl:element name="packageIDs">        <xsl:value-of select="$packageID" />      </xsl:element>    </xsl:element>  </xsl:template>

Call the recursive template

Finally, call the recursive template for each <goodsrow> by replacing the text within the for-each loop with a call-template that calls your recursive template. The finished template that matches the root element should look like this:

  <!--  Main template, loops on goodsrow and calls the recursive template for each incoming goodsrow:  -->  <xsl:template match="/ns0:Root">    <ns0:Root>      <xsl:for-each select="goodsrow">        <xsl:call-template name="GenerateGoodsrows">          <xsl:with-param name="weight" select="string(weight/text())" />          <xsl:with-param name="packageIDs" select="string(packageIDs/text())" />        </xsl:call-template>      </xsl:for-each>    </ns0:Root>  </xsl:template>

That’s it! If you prefer, you can place the recursive and base case templates in a scripting functoid and connect the scripting functoid to the same node as the looping functoid on the right-hand (destination) side. This way you have everything in one file (the .btm file), but the editing capabilities in the scripting functoid is severely limited so I prefer a separate XSLT. Please note that I haven’t tried putting the templates in functoids for this solution, but in general it should work.

Conclusion

Recursion is seldom used, but once you have understood its principles, you will find its use in many places where a for each-loop would be clumsy or difficult, in any language.

A detail regarding BizTalk and XSLT is that BizTalk uses XSLT version 1 which lack many functions that simplifies things. For example, to retrieve the head and the tail, we have to resort to string-searching of delimiters. XSLT version 2 has many more built-in functions, among them is the tokenize() function that would simplify string splitting into head and tail.

Source Code

Download the sample source code from Code gallery here:

BizTalk: Create Repeating Records from Delimited Fields

Share via