[XSL-LIST Mailing List Archive Home]
[By Thread]
[By Date]
Re: [xsl] XML to Database Inserts via XSLT
Subject: Re: [xsl] XML to Database Inserts via XSLT From: Stef <stef@xxxxxxxxx> Date: Thu, 10 Mar 2005 11:48:02 -0500 |
For the record, and for anyone else who needs to dop a similiar style of processing, I am including the stylesheet that I ended up creating, along with an example item. It would have been nice to have done a fast bcp (thus ignoring all the triggers and RI on the database), but, since there are external auditors/review process which has to 'scan/verify' the inserts, I couldn't do this. I know, its completely crazy, but, it appears to be the way that things work around these parts. As a small final note, it takes around 6 minutes to process the file (along with Verification against the DTD) for 42,000 items. Not bad all things considered. Comments/Critiscm's are always welcome. Many thanks Stef (ps. XSLT really isn't that hard to pick up from what I have seen, although it does have a quite fearsome reputation (being part of the whole XPath/XML group)) <!-- Data File //--> <?xml version="1.0"?> <data> <!-- each name in entityAlternativeNames should be stored individually with the current red --> <!-- each prev is it's own item with red set and 'p' for prev or n for next --> <entity> <name>Democratic and Popular Republic of Algeria</name> <shortname>Dem & Pop Rep Algeria</shortname> <ticker>ALGERI</ticker> <red>VZ5ACN</red> <companynumber> <type>New Jersey Company Identification</type> <value>4901801000</value> </companynumber> <alternativenames>name_one; name_two; name_three</alternativenames> <cusip>V4193K</cusip> <type>Sov</type> <jurisdiction>Algeria</jurisdiction> <liquidity>Low</liquidity> <prev>VZ5ACA</prev> <prev>VZ5ACB</prev> <next>VZ5AD0</next> </entity> </data> <!-- StyleSheet //--> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:output method="text" indent="no" encoding="iso8859-1" omit-xml-declaration="yes"/> <xsl:variable name="quote">'</xsl:variable> <xsl:strip-space elements="*"/> <!-- above is the typical setting up of parameters and such forth //--> <!-- here is the StrSplit function, which will take the alternativenames and the current red value, and then print out the insert statements one by one. Note that it recurses into itself. neat :) //--> <xsl:template name="StrSplit"> <xsl:param name="str"/> <xsl:param name="myred"/> <xsl:choose> <xsl:when test="contains($str,';')"> <xsl:text> INSERT INTO entityAlternativeNames (red,alternativeName) = VALUES (</xsl:text> <xsl:value-of select="concat($quote, $myred, $quote)"/> <xsl:value-of select="','"/> <xsl:value-of select="concat($quote, substring-before($str,';'), $quote= )"/> <xsl:value-of select="');
'"/> <xsl:call-template name="StrSplit"> <xsl:with-param name="str" select="substring-af= ter($str,'; ')" /> <xsl:with-param name="myred" select="$myred" /> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:text> INSERT INTO entityAlternativeNames (red,alternat= iveName) VALUES (</xsl:text> <xsl:value-of select="concat($quote, $myred, $quote)"/> <xsl:value-of select="','"/> <xsl:value-of select="concat($quote, $str, $quote)"/> <xsl:value-of select="');
'"/> </xsl:otherwise> </xsl:choose> </xsl:template> <!-- lets start converting from the root '/' and scanning in the data namespace for any entities //--> <xsl:template match="/"> <xsl:for-each select="data/entity"> <xsl:text>INSERT INTO entity (</xsl:text> <!-- Since we want to ignore any nodes with 'prev' or 'next' or 'alternativenames', lets not even include them in our search pattern. If we want all nodes, we could use * //--> <xsl:for-each select="*[not(name()='prev') and not(name()='next') an= d not(name()='alternativenames')]"> <xsl:if test="name() != 'companynumber'"> <xsl:value-of select="name()"/> <!-- if this is the last node, then skip the , //--> <xsl:if test="position() < last()">=20 <xsl:value-of select="','"/> </xsl:if> </xsl:if> <!-- we have to flatten out the companynumber sub-element //--> <xsl:if test="name() = 'companynumber'"> <xsl:for-each select="*"> <xsl:value-of select="name()"/> <xsl:value-of select="','"/> </xsl:for-each> </xsl:if>=09 </xsl:for-each> =09 <!-- close the columns part, and start the values //--> <xsl:value-of select="') VALUES ('"/> <!-- now, lets do the values for the items to save //--> <xsl:for-each select="*[not(name()='prev') and not(name()='next') an= d not(name()='alternativenames')]"> <xsl:if test="name() != 'companynumber'"> <xsl:value-of select="concat($quote, ., $quote)"/> <xsl:if test="position() < last()">=20 <xsl:value-of select="','"/> </xsl:if> </xsl:if> <xsl:if test="name() = 'companynumber'"> <xsl:for-each select="*"> <xsl:value-of select="concat($quote, ., $quote)"/> <xsl:value-of select="','" /> </xsl:for-each> </xsl:if> </xsl:for-each> <!-- end the insanity now //--> <xsl:value-of select="');
'"/> <!-- get the current 'red' value here, it will come in handy later = //--> <xsl:variable name="myred"><xsl:value-of select="red"/></xsl:variable> <!-- deal with any prev's that this entity may have //--> <xsl:for-each select="prev"> <xsl:text> INSERT INTO entityPrevNext (prevNextFlag, red, prevNextRed) VA= LUES ('P',</xsl:text> <xsl:value-of select="concat($quote, $myred, $quote)"/> <xsl:value-of select="','"/> <xsl:value-of select="concat($quote, ., $quote)"/> <xsl:text>);
</xsl:text> </xsl:for-each> <!-- then deal with any 'next' items as well //--> <xsl:for-each select="next"> <xsl:text> INSERT INTO entityPrevNext (prevNextFlag, red, prevNextRed) VA= LUES ('N',</xsl:text> <xsl:value-of select="concat($quote, $myred, $quote)"/> <xsl:value-of select="','"/> <xsl:value-of select="concat($quote, ., $quote)"/> <xsl:text>);
</xsl:text> </xsl:for-each> <!-- Lastly, let's deal with the alternativenames, if any exist then we should pass the str into the template StrSplit, along with the current 'red' value //--> <xsl:for-each select="alternativenames"> <xsl:call-template name="StrSplit"> <xsl:with-param name="str" select="." /> <xsl:with-param name="myred" select="$myred" /> </xsl:call-template> </xsl:for-each> </xsl:for-each> </xsl:template> </xsl:stylesheet>
Current Thread |
---|
|
<- Previous | Index | Next -> |
---|---|---|
RE: [xsl] XML to Database Inserts v, Aron Bock | Thread | Re: [xsl] XML to Database Inserts v, Wendell Piez |
Re: [xsl] calling two consecutive x, RQamar | Date | [xsl] XSL:IF and Sort Results, Jason Trépanier |
Month |
Keywords