Subject: Re: [xsl] XML to Database Inserts via XSLT From: Stef <stef@xxxxxxxxx> Date: Wed, 9 Mar 2005 17:00:57 -0500 |
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() < 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() < last()"> > <xsl:value-of select="','"/> > </xsl:if> > </xsl:for-each> > > <xsl:value-of select="';
'"/> > </xsl:template> > > </xsl:stylesheet> > > > > > > > So far, I have a small datafile something like; > > > > <entity> > > <name>Democratic and Popular Republic of Algeria</name> > > <shortname>Dem & 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)>0"> > > <xsl:text>fullname,</xsl:text> > > </xsl:if> > > <xsl:if test="string-length(red)>0"> > > <xsl:text>red,</xsl:text> > > </xsl:if> > > <xsl:text>) VALUES (</xsl:text> > > <xsl:if test="string-length(name)>0"> > > <xsl:text>'</xsl:text> > > <xsl:value-of select="name"/> > > <xsl:text>',</xsl:text> > > </xsl:if> > > <xsl:if test="string-length(red)>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
Current Thread |
---|
|
<- Previous | Index | Next -> |
---|---|---|
Re: [xsl] XML to Database Inserts v, Brian Chrisman | Thread | Re: [xsl] XML to Database Inserts v, JBryant |
Re: [xsl] XML to Database Inserts v, Stef | Date | Re: [xsl] XML to Database Inserts v, JBryant |
Month |