In my new job, I won’t be playing very much with SSRS, so the focus of this blog will change.
We do use SQL Server on the back-end, so there’s almost always some T-SQL work in the mix — that’s always fun for me — but we don’t use Reporting Services. I will miss SSRS being part of my day job. (I can still use the blog to answer SSRS questions from readers, and I don’t mind researching them on my own time. That’s what I was doing before, anyway.)
I’m spending a lot more time on enterprise integration now, which sometimes means quite a lot of SSIS, sometimes not.
But — and this is really fun for me — I’m back to using XSLT deeply and constantly again — not just occasionally, or because I happen to like using it. XSLT is part and parcel of how we do things in our products. Eminently sensible, IMHO.
I thought I’d give you a little example from my current work, combining XSLT and SQL.
SQL-Delimited Strings from XSLT
Recently I was working on a piece of XSLT that generates SQL statements from a lot of XML instructions that specified the values to be inserted.
Previous iterations of this code included many INSERT statements but all the VALUES to be inserted were types like numeric or bit. In my revision of this code, I wanted to add some string values.
These strings are perfectly fine in the XML document, but they would need to be surrounded by string delimiters to be listed as VALUES in the INSERT statement.
The problem here, as you realize, is that the string values themselves may include the delimiter character, so these delimiter characters need to be doubled within the string.
For example, if you want to insert the value this isn’t a test, you need to delimit this string as ‘this isn”t a test’ to get it into the SQL statement properly.
Right?
So what’s the problem?
XSLT 1.0 doesn’t have a “replace” function, like SQL or .NET languages, or lots of others. You can’t just say replace($theString, $someString, $someOtherString) and have it done for you all at once. There’s a “translate” function, but it is strictly a character-for-character replacement. You can’t use “translate” to replace one character by two characters.
While XSLT 2 does have this handy capability, many people who are using MS processors don’t have XSLT 2 available to them. (Don’t get me started on what a short-sighted attitude MS has with respect to XSLT…)
String replacing is not actually difficult to do, even in XSLT 1.0, if you remember to use recursion. I have a feeling that Michael Kay and the others involved in creating the XSLT standard thought this was so obvious that they didn’t sweat the small stuff like “replace” functionality in the original XSLT standard.
Unfortunately, it turned out that many people found the idea of recursion counter-intuitive, and they whined quite a lot about it.
IMHO, they were a lot more justified in whining about grouping, but that’s water under the bridge. (Don’t get me started on Muenchian grouping, either.)
Since recursion is an extremely useful XSLT technique to have under your belt, in any version, and since this bit of code is easily re-used in other situations where you have to massage a string, and since you may even find yourself building SQL in XSLT some day… and since this is also a fairly classic use of XSLT’s string-parsing functions… I thought I’d give it to you.
Ready?
The simple templates that do the heavy lifting assume you have these variables declared:
<xsl:variable name=”Apos“>‘</xsl:variable>
<xsl:variable name=”SqlApos“ select=”concat($Apos,$Apos)“/>
… and you call them like this (assume this snippet is going to fill a variable that will form part of a SQL statement containing a string, so it needs to have delimiters added to it):
<xsl:call-template name=”DelimitSqlString“>
<xsl:with-param name=”theString“
select=”[your XPath or variable goes here]“/>
</xsl:call-template>
… and here they are:
<xsl:template name=”DelimitSqlString“>
<xsl:param name=”theString“/>
<xsl:variable name=”resultString“>
<xsl:choose>
<xsl:when test=”contains($theString,$Apos)“>
<xsl:call-template name=”FixSqlString“>
<xsl:with-param name=”currString“ select=
“concat(substring-before($theString,$Apos),$SqlApos)“/>
<xsl:with-param name=”restString” select=
“substring-after($theString,$Apos)“/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select=”$theString“/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:value-of select=
“concat($Apos, normalize-space($resultString) ,$Apos)“/>
</xsl:template>
<xsl:template name=”FixSqlString“>
<xsl:param name=”currString“/>
<xsl:param name=”restString“/>
<xsl:choose>
<xsl:when test=”contains($restString,$Apos)“>
<xsl:call-template name=”FixSqlString“>
<xsl:with-param name=”currString” select=
“concat($currString,substring-before($restString,$Apos),$SqlApos)“/>
<xsl:with-param name=”restString“ select=
“substring-after($restString,$Apos)“/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select =
“concat($currString,$restString)“/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
I’ve put a little extra work in there with the “contains” test in the first template. This just avoids calling out to the second template at all, when there isn’t any extra work that needs to be done.
The second template is the one that does the recursion; it finds and doubles delimiter characters one at a time, calling itself to fix the remainder of the string as it goes. When the second template’s all done fixing, the first template adds the outside SQL delimiters.
Got it?
Your version may or may not need the normalize-space function (depending on what your source is like).
Your version might not need the external variables, either; I need them in other places so I found it convenient to declare them and make them readable up-top.
With those caveats and potential simplifications, you can drop these two templates in pretty much anywhere you needed to drive SQL from XSLT. With a few changes, you could use them to drive javascript generation or other types of XSLT work that may face a problem with delimiters embedded in strings.
Better than QUOTENAME at what it does.
Think of this recursive technique as the XSLT equivalent of the T-SQL QUOTENAME function — without QUOTENAME’s limitations. The T-SQL function doesn’t handle long strings.
XSLT processors do this type of recursive string handling pretty fast. Even with really long strings. Even the MS XSLT processors.
Enjoy.