[XSL-LIST Mailing List Archive Home] [By Thread] [By Date]

Re: [xsl] XML to Database Inserts via XSLT

---

---

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 &amp; 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="');&#xA;'"/>
                <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="');&#xA;'"/>
        </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() &lt; 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() &lt; 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="');&#xA;'"/>

        <!-- 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>);&#xA;</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>);&#xA;</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>

---