Biztalk explode in XSLT transformations

Question

I'm receiving XML into BizTalk. One part is element and the value is ids separated by comma

<Stores>15,34</Stores>

I need to transform this into

<Stores>
    <Store>Store 1</Store>
    <Store>Store 4</Store>
</Stores>

What I need to do is to explode the value by comma, take each value and get value from database (15 -> Store 1, 34 -> Store 2).

How can I make the explode in xslt, how ca I get value from database for each exploded value. I already have procedure in db for that, just need to know how to call it.

Answer 1

I assume you know how to write the overall transform but need help with the tokenization of the string containing the store numbers.

If you're using XSLT 2.0, look at the definition of the tokenize() function. This will split the string value at a specified delimiter, allowing you to perform this transformation. In XSLT 1 you could look at EXSLT regex extension functions.

Answer 2

The BizTalk Mapper does not support XSLT 2.0 (see MSDN Documentation http://msdn.microsoft.com/en-us/library/aa559261%28BTS.10%29.aspx) so you will need to use the EXSLT extensions if you want to use the mapper.

There is a great post here by Richard Hallgren that covers how to use EXSLT within the BizTalk Mapper.

One additional thought is about an alternative solution. It is not clear if you absolutely must make your database calls one by one - would making a single call work?

It is possible to provide a stored procedure a delimited string as a parameter and to then use a function to break this string up. I've included an example of such a function below, the example being a table function. You'll be able find lots of other implementations on the web.

With the table function you can join against this in you store lookup procedure.

If this meets your needs it should be a lot faster since you now perform just a single database hit and can perform set operations to get back your list of stores.

CREATE function fn_ParseCSVString
(   
    @INPUTCSV varchar(MAX)
)
RETURNS @TBL TABLE 
( 
    COL1 INT
)
AS
BEGIN
DECLARE @NUM_STR NVARCHAR(MAX)
SET @NUM_STR = @INPUTCSV

SET @NUM_STR = REPLACE(@NUM_STR,' ','')     
-- this will trim any intermediate spaces

WHILE LEN(@NUM_STR) >= 0
BEGIN 

DECLARE @@SUBSTR VARCHAR(100) 
IF CHARINDEX(',',@NUM_STR,0) <> 0   
BEGIN   
SET @@SUBSTR = SUBSTRING(@NUM_STR,0,CHARINDEX(',',@NUM_STR,0))   
INSERT INTO @TBL VALUES(@@SUBSTR)  
END 
ELSE   
BEGIN   
INSERT INTO @TBL VALUES(@NUM_STR)   
BREAK   
END  

SET @@SUBSTR = @@SUBSTR + ',' 

SET @NUM_STR = SUBSTRING(@NUM_STR, CHARINDEX(',',@NUM_STR,0) + 1, LEN(@NUM_STR)) 

END
RETURN
END

Answer 3

Here is an XSLT 1.0 compatible solution that does the explode:

<!-- straightforward -->
<xsl:template match="Stores">
  <xsl:copy>
    <xsl:call-template name="explode">
      <xsl:with-param name="str" select="text()" />
    </xsl:call-template>
  </xsl:copy>
</xsl:template>

<!-- string processing through recursion -->
<xsl:template name="explode">
  <xsl:param name="str" select="''" />

  <xsl:variable name="temp" select="concat($str, ',')" />
  <xsl:variable name="head" select="substring-before($temp, ',')" />
  <xsl:variable name="tail" select="substring-after($temp, ',')" />

  <xsl:if test="$head != ''">
    <Store>
      <xsl:value-of select="$head" />
    </Store>
    <xsl:call-template name="explode">
      <xsl:with-param name="str" select="$tail" />
    </xsl:call-template>
  </xsl:if>
</xsl:template>

Output for <Stores>15,34</Stores> would be:

<Stores>
  <Store>Store 15</Store>
  <Store>Store 34</Store>
</Stores>

David Hall's solution seems to contain a pointer how to use an XSLT extension function to make calls to that database from XSLT.