Re: XML to Database Inserts via XSLT

Well, Saxon has an extension to work with databases, so that you don't 
have to build up SQL commands as you are doing here. However, Dr. Kay 
(developer of Saxon) has made it very clear that the SQL extension is an 
example of an extension, not something he supports as part of Saxon. 
Still, I've used the thing to build small databases (because I work with a 
bunch of SQL addicts who would rather code against a database than an XML 
file). If you know Java, you could also write your own database extensions 
for Saxon. I'm sure the same is possible for other XSLT processors, but 
Saxon is the one I know.

As for the questions at the end of your message, many of them are in 
various FAQs, the best of which is Dave Pawson's marvelous contribution to 
the XSL community: http://www.dpawson.co.uk/xsl/xslfaq.html

Still, here's my shot at some answers:

XSL's variables can't be assigned once they've been created. If you need 
to use a variable that way, either use an extension or use recursion.
XSL does have a rough equivalent to chop in the substring-before function 
-for example: substring-before($someString, ',')
Whether entities within entities recurse depends on how you write the 
stylesheet, but it certainly can be done.

Given an XML structure like so:

<a>
  <b>
    <thing>Thing 1</thing>
    <b><thing>Thing 2</thing></b>
  </b>
</a>

and a stylesheet like so:

<xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform";>

  <xsl:template match="b">
    <xsl:apply-templates/>
  </xsl:template>
 
  <xsl:template match="thing">
    <xsl:value-of select="."/>
  </xsl:template>

</xsl:stylesheet>

You'll get recursion. If you did something like:

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

You won't get recursion. <xsl:for-each> and <xsl:value-of> also do 
interesting things, if you feel like experimenting.

HTH

Jay Bryant
Bryant Communication Services
(presently consulting at Synergistic Solution Technologies)




Stef <stef(_at_)ummon(_dot_)com> 
03/09/2005 01:55 PM
Please respond to
xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com


To
xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
cc

Subject
[xsl] XML to Database Inserts via XSLT






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.

                 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

--~------------------------------------------------------------------
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>
--~--