Re: [xsl] XML to Database Inserts via XSLT

[Brian Chrisman <incubus@xxxxxxxxxxxxxx>]

On Wed, Mar 09, 2005 at 02:55:07PM -0500, Stef wrote:
> Hello Everyone,
> 	Well, the subject says it all really. I have
> a few jobs that I -could- write perl to transform the
> xml into inserts, however, I would prefer to use XSLT
> and learn as I go. Another feather in my cap and all
> that.

Below is a stylesheet I used to use as part of a set of
export/import tools... I think the XML schema was something
a little different from what you are using...
Mine was probably something like:
<table>
  <name>foo</name>
  <row>
    <column>
      <name>foo_id</name>
      <value>35</value>
    </column>
    ...
  </row>
  ...
</table>

This blindly translated the XML into SQL statements.. I had
a separate stylesheet for verifying/manipulating values...
I had some ideas about making it a little less kludgy, but
those have fallen by the wayside..
I've had very good results in using XSLt for this kind of purpose.
Now if only I could find a better language for interfacing with
an RDBMs than SQL...



<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"; version="1.0">
<xsl:output method="text" indent="yes" encoding="iso8859-1" omit-xml-declaration="yes"/>

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

<xsl:template match="/">
        <xsl:apply-templates select="/table"/>
</xsl:template>

<xsl:template match="table">
        <xsl:apply-templates select="row"/>
</xsl:template>

<xsl:template match="row">
        <xsl:value-of select="concat('INSERT INTO ', ../name, '(')"/>

        <xsl:for-each select="column">
                <xsl:value-of select="name"/>
                <xsl:if test="position() &lt; last()">
                        <xsl:value-of select="','"/>
                </xsl:if>
        </xsl:for-each>

        <xsl:value-of select="') VALUES ('"/>

        <xsl:for-each select="column">
                <xsl:value-of select="concat($quote, value, $quote)"/>
                <xsl:if test="position() &lt; last()">
                        <xsl:value-of select="','"/>
                </xsl:if>
        </xsl:for-each>

        <xsl:value-of select="';&#xA;'"/>
</xsl:template>

</xsl:stylesheet>



> 
> 	So far, I have a small datafile something like;
> 
>        <entity>
>                 <name>Democratic and Popular Republic of Algeria</name>
>                 <shortname>Dem &amp; Pop Rep Algeria</shortname>
>                 <ticker>ALGERI</ticker>
>                 <red>VZ5ACN</red>
> 	</entity>
> 
> 	Now, I have a stylesheet which is as follows;
> 
>   <xsl:template match="entity">
>         <xsl:text>INSERT INTO table1 (</xsl:text>
>         <xsl:if test="string-length(name)&gt;0">
>                 <xsl:text>fullname,</xsl:text>
>         </xsl:if>
>         <xsl:if test="string-length(red)&gt;0">
>                 <xsl:text>red,</xsl:text>
>         </xsl:if>
>         <xsl:text>) VALUES (</xsl:text>
>         <xsl:if test="string-length(name)&gt;0">
>                 <xsl:text>'</xsl:text>
>                 <xsl:value-of select="name"/>
>                 <xsl:text>',</xsl:text>
>         </xsl:if>
>         <xsl:if test="string-length(red)&gt;0">
>                 <xsl:text>'</xsl:text>
>                 <xsl:value-of select="red"/>
>                 <xsl:text>',</xsl:text>
>         </xsl:if>
>         <xsl:text>);</xsl:text>
>   </xsl:template>
> 
> 
> 	It does work, but its probably a tad, well, 
> clunky. I would prefer to know how others do this, or
> have done this, or where my syntax is 'lacking'. I am
> open to all and any creative or constructive critiscms.
> This is my first time in really getting into XSLT, but
> already I can see the benefits. 
> 
> 	Is there anyway, for example, to store a 'variable'
> in XSLT ? is there any equivalent to 'chop' (so I can remove
> any trailing ',' from the variable) ? If i have an entity 
> inside the entity, will XSLT automagically recurse into it
> or do I have to specifically code recursion into the stylesheet ? 
> 
> 
> 	Regards and Many Thanks for any Pointers (no pun intended)
> 	Stef