Subject: Re: [xsl] XML to Database Inserts via XSLT From: Brian Chrisman <incubus@xxxxxxxxxxxxxx> Date: Wed, 9 Mar 2005 12:42:43 -0800 |
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 -> |
---|---|---|
[xsl] XML to Database Inserts via X, Stef | Thread | Re: [xsl] XML to Database Inserts v, Stef |
[xsl] XSL Variable problem, Gang Li | Date | Re: [xsl] XML to Database Inserts v, JBryant |
Month |