I have recently been tasked a data conversion that requires me to convert from SQL Data into XML, via a schema (not my design), the resultant XML data going to a BLOB table. Don’t ask.

I battled with a number of options but finally settled on a two step process. I'm using XSLT for the bulk of the work, with a sprinkling of VB to take care of some stuff that needed to use some existing .NET libraries of ours.

 Like most developers I'm often looking for the tool that will speed up my development process and reduce the amount of code I have to write. So I tried using Altova's latest offering in the MapForce range to handle the mapping between the two schemas. I figured this should be a breeze, infer a schema from my existing data (with a few tweaks here and there of course), visually map the old structure to the new, and a wonderfully quick XSLT is born to do my grunt work.

Ooh boy was I wrong....

 So what if the old table I was converting had over 250 poorly named columns? So what if the schema it was mapping to was a 1500 line, 100 type monolith? Did I mention it wasn’t my design?

Even taking these depressing facts into consideration the code MapForce produces is nothing short of abysmal. Every single mapping, regardless of the maxOccurs setting for the type in the schema, gets a for-each tag. I much prefer to use templates for the sake of extensibility, readability & modularity etc. Much like a procedural programmer likes subroutines.

However there is another reason for-each irks me, and here is a snippet of the auto-gen to demonstrate:

<xsl:param name="ForwardSlash" select="'/'"/>

<TitleRef>

       <xsl:for-each select="titleref">

              <xsl:variable name="Vvar24_titleref" select="."/>

              <xsl:variable name="Vvar25_INPUT" select="$ForwardSlash"/>

              <xsl:variable name="Vvar26_RESULTOF_substringbefore" select="substring-before($Vvar24_titleref, $Vvar25_INPUT)"/>

              <TitleVolume>

                     <xsl:value-of select="$Vvar26_RESULTOF_substringbefore"/>

              </TitleVolume>

       </xsl:for-each>

       <xsl:for-each select="titleref">

              <xsl:variable name="Vvar28_titleref" select="."/>

              <xsl:variable name="Vvar29_INPUT" select="$ForwardSlash"/>

              <xsl:variable name="Vvar30_RESULTOF_substringafter" select="substring-after($Vvar28_titleref, $Vvar29_INPUT)"/>

              <TitleFolio>

                     <xsl:value-of select="$Vvar30_RESULTOF_substringafter"/>

              </TitleFolio>

       </xsl:for-each>

</TitleRef>

MapForce just gets it all wrong. There will always be a TitleRef tag in the resultant XML, regardless of whether or not the transform encounters the titleref element in the source document. The duplicate for-each tags are partly because I asked something weird of the designer. “Give me the substring before a slash and put it here, and then give me the substring after the slash and put it there”. However I never intended for this monstrosity to occur. All of this pain could have been avoided with the following XSLT:

<xsl:template match="titleref">

       <TitleRef>

              <TitleVolume>

                     <xsl:value-of select="substring-before(., '/')"/>

              </TitleVolume>

              <TitleFolio>

                     <xsl:value-of select="substring-after(., '/')"/>

              </TitleFolio>

       </TitleRef>

</xsl:template>

Which is what I had in mind when I was visually designing it in the first place. I guess WYSIWYM is what I really want out of a designer these days.

MapForce is actually quite a nice tool for visually mapping and allows you to map between many different types of data source, using a good variety of modern programming languages (C#, XSLT 2.0) to do so. It exposes a good set of functions for you to use in the process with a neat way of visually representing them.

The Big Problem™ is that while the editor will let you visually design your transform, apparently providing two way communications between designer and transform editor was too hard. That is to say, thou canst see the gen’d code but thou canst edit the gen’d code.

My conclusion is that MapForce is your classic I-don't-want-to-know-what’s-under-the-hood kind of auto-gen tool. I ended up dumping the XSLT into VS2005 and used their excellent XSLT editor to finish off. The new improvements to the XSLT interface have made me migrate back home to Visual Studio.

So……Like most developers, I find myself disenchanted with the extremely poor code that auto-gen code tools espouse. I guess it keeps monkeys like me in a job though.