Re: XML to Database Inserts via XSLT

I forgot to mention, many thanks Brian. I believe that
your example has provided a nice path (again, no pun
intended) and I am currently seeing where it goes. I
should have something workable by the end of the week
(hopefully :).

Again, Many thanks, and I will of course share the code
for critique's and comments once done :)

regards
Stef

On Wed, Mar 09, 2005 at 12:42:43PM -0800, Brian Chrisman wrote:
> 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