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
--~------------------------------------------------------------------
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail:
<mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--
--~------------------------------------------------------------------
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail:
<mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--
--~------------------------------------------------------------------
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: <mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--