Biztalk explodes in XSLT transformations
I am getting XML in BizTalk. One part is the element and the value is comma separated identifiers
<Stores>15,34</Stores>
I need to convert this to
<Stores>
<Store>Store 1</Store>
<Store>Store 4</Store>
</Stores>
I need to do this to explode the value by comma, take each value and get the value from the database (15 -> Store 1, 34 -> Store 2).
How can I blast in xslt how I get the value from the database for each spaced value. I already have a procedure in db for this, just need to know how to call it.
source to share
Here is an XSLT 1.0 compliant solution that does look:
<!-- 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>
The output for <Stores>15,34</Stores>
would be:
<Stores>
<Store>Store 15</Store>
<Store>Store 34</Store>
</Stores>
David Hall's solution seems to have a pointer to using an XSLT extension function to make calls to this database from XSLT.
source to share
BizTalk Mapper does not support XSLT 2.0 (see MSDN documentation http://msdn.microsoft.com/en-us/library/aa559261(BTS.10).aspx ), so you will need to use EXSLT extensions if you want to use the map ...
There is an excellent post here by Richard Holgren that describes how to use EXSLT in the BizTalk Mapper.
Another thought for an alternative solution. It is not clear whether you absolutely need to call the database one by one every time - will one call work?
You can provide a stored procedure with a separator string as a parameter and then use a function to break that string. I gave an example of such a function below, the example is a table function. You can find many other implementations on the Internet.
With the spreadsheet function, you can join this store search procedure.
If that suits your needs, it should be much faster as you now only have one database access and can do dial operations to return the store list.
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
source to share
I am assuming that you know how to write a generic conversion, but need help tokenize a string containing store numbers.
If you're using XSLT 2.0, take a look at the definition of the tokenize () function. This will split the string value at the specified delimiter, allowing you to perform this conversion. In XSLT 1, you can see the EXSLT extension function extensions.
source to share