Reduce frustration with XSLT and SQL by employing an innovative approach

by Edmond Woychowsky | May 23, 2006 3:06:00 PM

Tags: XML, Edmond Woychowsky, apostrophe, XSLT, SQL

Takeaway: When it comes to solving problems with XSLT 1.0, an innovative, out-of-the-box approach is often necessary. Here are two occasions where the solution to a troublesome problem involved an approach out of the ordinary.

There is something refreshing about climbing up on a soapbox and ranting. It doesn't matter what the subject of the rant is, most of us have some primal need to hop up and down and basically act like Donald Duck. Maybe that is the reason that flame wars are so popular or it could just be that the frustration level for working with technology can sometimes be off the chart.

Consider XSLT 1.0 for instance, I’ve been using it for several years and consider myself somewhat well versed in it as a language. There are, however, still times when encountering a problem that I feel like yelling at Goofy. In the last year there have been two of these occurrences where I needed to do something that was a little bit out of the ordinary.

---

Code Listing A and Listing B are available as separate files which are included in the downloaded zip file.

---

Problem one

The first issue dealt with producing SQL from XML using XSLT. The project started simply enough with a style sheet containing the following output statement:

<xsl:output method="text" indent="yes" media-type="text/sql"/>

Unfortunately that was the high point of the development process; it all went down hill from there. Oh, sure I produced SQL, but it had one particularly troublesome error, namely apostrophes embedded in text. Since SQL uses apostrophes to enclose text strings encountering one in the middle of a string becomes a real issue. Add to this that an apostrophe is the common abbreviation for feet and that there are names like O’Connor, O’Connell and Paddy O’Furniture in use out there, and the size of the problem grows.

At first glance the simplest way to fix this is by replacing each single apostrophe with two apostrophes, which will inform SQL of the fact that the string has an embedded apostrophe. Unfortunately, because XSLT uses apostrophes as delimiters for attributes, the problem grows to the size of Godzilla with that solution.

Sometime shortly after encountering this problem like a bolt of lightning we remember the "&apos;" entity and code something like the following:

<xsl:choose>
 <xsl:when test="contains($string,&apos;)">
   <!---->
 </xsl:when>
 <xsl:otherwise>
   <!---->
 </xsl:otherwise>
</xsl:choose>

Which produces an error in the style sheet because the &apos; is interpreted as a single apostrophe. So I gave it another shot and tried the following:

<xsl:choose>
 <xsl:when test="contains($string,'&apos;')">
   <!---->
 </xsl:when>
 <xsl:otherwise>
   <!---->
 </xsl:otherwise>
</xsl:choose>

With pretty much the same result, only now instead of a single apostrophe there are three. This pretty-much covers the common errors when dealing with apostrophes in XSLT. There were, however, more errors ranging from the exotic to the "doesn’t produce an error but doesn't work either" type. Rather than outlining each and every one of these, let’s take a look at what works:

<xsl:variable name="apostrophe">'</xsl:variable>
<xsl:choose>
 <xsl:when test="contains($string,$apostrophe)">
   <!---->
 </xsl:when>
 <xsl:otherwise>
   <!---->
 </xsl:otherwise>
</xsl:choose>

Because of the way that the XSLT processor works variables are handled differently than literals, which is something that we can use to our advantage. Using this knowledge I wrote the following named template that can be used to replace single apostrophes with two apostrophes in the parameter string, called interesting enough, string. In addition, because this template is recursive it is capable of handling multiple occurrences of single apostrophes as Listing A illustrates.

Listing A -- sqlApostrophe template

<!--
    Template:     sqlApostrophe
    Mode:         n/a
    Parameter(s): string [required].
    Description:  Parses the parameter (string)
replacing
single apostrophes (')
with two apostrophes ('').
    This prevents issues in the output SQL.
-->
<xsl:template name="sqlApostrophe">
 <xsl:param name="string"/>

 <xsl:variable name="apostrophe">'</xsl:variable>

 <xsl:choose>
   <xsl:when
test="contains($string,$apostrophe)">
     <xsl:value-of select=
"concat(substring-before($string,$apostrophe),
$apostrophe,$apostrophe)"
disable-output-escaping="yes"/>

     <xsl:call-template name="sqlApostrophe">
       <xsl:with-param name="string"
select="substring-after($string,$apostrophe)"/>
     </xsl:call-template>
   </xsl:when>
   <xsl:otherwise>
     <xsl:value-of select="$string"
disable-output-escaping="yes"/>
   </xsl:otherwise>
 </xsl:choose>

</xsl:template>

Problem two

The second issue that made me hopping mad last year was more of along the lines of the feature envy. I had thought that I was in remission, unfortunately going back and forth between SQL and XSLT proved me wrong. You see, there was a feature in SQL that I believed would really add significant value to my toolbox, the XSLT equivalent of the SQL IN clause.

If you’re not familiar with the IN clause it works something like a conditional statement that tests to see if a particular row contains a value that is in a list that is separated by commas. The result will be either is true, when the value is found, or false, when the value is not found. This is essentially what the XSLT in template shown in Listing B does.

Listing B -- in template

<!—
    Template:     in
    Mode:         n/a
    Parameter(s): list [required].
                  Value [required].
    Description:  Determines if value is IN a list of values.
-->
<xsl:template name="in">
  <xsl:param name="list"/>
  <xsl:param name="value"/>
            
  <xsl:choose>
    <xsl:when test="$list =
$value">true</xsl:when>
    <xsl:when test="substring-before($list,',') =
$value">true</xsl:when>
    <xsl:when test="string-length($list) = 0">false</xsl:when>
    <xsl:otherwise>
      <xsl:call-template name="in">
        <xsl:with-param name="list" select="substring-after($list,',')"/>
        <xsl:with-param name="value" select="$value"/>
      </xsl:call-template>
    </xsl:otherwise>
  </xsl:choose>
</xsl:template>

No hurt feelings

These occurrences pointed out to me that getting angry really isn’t an option when there is a job to do. Unlike Donald, who takes his frustrations out on Goofy, when I get frustrated I try to get creative and take it out on software like, in this case, Altova’s XMLSpy. Fortunately, unlike Goofy, its feelings don’t get hurt.

Print/View all Posts Comments on this article

Your XSLT lessonsMark W. Kaelin  | 05/23/06

RE: Reduce frustration with XSLT and SQL by employing an innovative approacjensen@...  | 11/09/07

RE: Reduce frustration with XSLT and SQL by employing an innovative approacsteven.hentschel@...  | 01/09/08