[XSL-LIST Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message]

Re: XML to Database Inserts via XSLT

Subject: Re: XML to Database Inserts via XSLT
From: Brian Chrisman <incubus@xxxxxxxxxxxxxx>
Date: Wed, 9 Mar 2005 12:42:43 -0800
xslt if string length
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

Current Thread

PURCHASE STYLUS STUDIO ONLINE TODAY!

Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced!

Buy Stylus Studio Now

Download The World's Best XML IDE!

Accelerate XML development with our award-winning XML IDE - Download a free trial today!

Don't miss another message! Subscribe to this list today.
Email
First Name
Last Name
Company
Subscribe in XML format
RSS 2.0
Atom 0.3
Site Map | Privacy Policy | Terms of Use | Trademarks
Free Stylus Studio XML Training:
W3C Member
Stylus Studio® and DataDirect XQuery ™are products from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2013 All Rights Reserved.