Tuesday, November 27, 2012

BizTalk Mapping - Use XSLT template to split a csv field to repease fields

Recently in a data integration scenario, I need to map a CSV field values into a repeating field.

The source schema field will be look like this,
<TestData>
     123, A, 23, B, 45, FF, 67, SD, 78, DE,,,
</TestData>
The value of the data is a variable length, also it maybe have some blank values or not.

The destination schema data should be mapped like this,
<Results>
    <Result id="123">A<Result>
    <Result id="23">B</Result>
    <Result id="45">FF</Result>
    <Result id="67">SD</Result>
    ...
</Results>

Because the incoming test data is a variable length csv value, we can not use a table looping and extractor to direct mapping to the destination

The solution I come up is to use a XSLT template with a recursive call.

The solution has a single link to destination Results element to decide if need to create the whole element.

Then the template is linked to the Result element to create each element by keep calling XSLT substring-before(), substring-after() to reduce the string and generate the result.

Also xslt contains() is used to check if the element is the last value. or else substring-before() will return empty string.

Here is the sample codes, we need to run substring-after twice for each round, becasue this scenario is a key-value pair.
 
 <xsl:template name="TokenizeCommaSepString">
    <xsl:param name="stringToTokenize" />
 
    <xsl:if test="$stringToTokenize != ''">

        <xsl:variable name="var:v98" select="substring-before($stringToTokenize, ',')" />
        <xsl:variable name="var:v99" select="substring-after($stringToTokenize,',')" />
       
        <xsl:choose>
            <xsl:when test="contains($var:v99, ',') = 0">              
                <xsl:if test=" $var:v98!='' or $var:v99!='' ">
                    <xsl:element name="Result">
                        <xsl:attribute name="Id">
                            <xsl:value-of select="$var:v99" />
                        </xsl:attribute>
                        <xsl:element name="Value">
                            <xsl:value-of select="$var:v98" />
                        </xsl:element>
                    </xsl:element>
                </xsl:if>
            </xsl:when>
         
            <xsl:otherwise>
                <xsl:variable name="var:v99.2" select = "substring-before($var:v99, ',')" />
                <xsl:if test=" $var:v98!='' or $var:v99.2!='' ">
                    <xsl:element name="Result">
                        <xsl:attribute name="Id">
                            <xsl:value-of select="$var:v99.2" />
                        </xsl:attribute>
                        <xsl:element name="Value">
                            <xsl:value-of select="$var:v98" />
                        </xsl:element>
                    </xsl:element>
                </xsl:if>
            </xsl:otherwise>
        </xsl:choose>

        <xsl:call-template name="TokenizeCommaSepString">
            <xsl:with-param name="stringToTokenize" select="substring-after(substring-after($stringToTokenize,','), ',')" />
        </xsl:call-template>
  </xsl:if>

</xsl:template>


No comments: