View stylesheet

XML schemas http://dd.sdr.edw.ro/GetSchema?id=TBL7212
Output type SQL
Description MySql
XSL file sdr-lakes-station-mysql.xsl (Last modified: 13 Apr 2011 14:46 )
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:dd715="http://dd.sdr.edw.ro/namespace.jsp?ns_id=715" xmlns:dd716="http://dd.sdr.edw.ro/namespace.jsp?ns_id=716">
	<xsl:output method="text"/>
	<xsl:template match="/">
		<xsl:apply-templates select="dd715:StationsLakes/dd715:Row"/>
	</xsl:template>
	<xsl:template match="dd715:StationsLakes/dd715:Row">
		<xsl:if test="position()=1">
			<xsl:call-template name="create-table"/>
--\n\r
-- Dumping data for table
--\n\r
        </xsl:if>
-- Start SQL command 
INSERT INTO [StationsLakes]            <xsl:call-template name="fields"/> VALUES (N'<xsl:value-of select="@status"/>',
<xsl:for-each select="*">
			<xsl:if test="count(preceding-sibling::*[local-name() = local-name(current())])=0">
				<xsl:if test=".=''">null</xsl:if>
				<xsl:if test=".!=''">N'<xsl:call-template name="globalReplace">
						<xsl:with-param name="outputString">
							<xsl:call-template name="getValue"/>
						</xsl:with-param>
						<xsl:with-param name="target" select="&quot;'&quot;"/>
						<xsl:with-param name="replacement" select="&quot;''&quot;"/>
					</xsl:call-template>'</xsl:if>
				<xsl:if test="position()!=last()  and count(following-sibling::*[local-name() != local-name(current())])&gt;0">,</xsl:if>
			</xsl:if>
		</xsl:for-each>
);
-- End SQL command 
</xsl:template>
	<xsl:template name="create-table">
--
-- SQL scripts for MS SQL Server
-- Table structure for table
--
-- Start SQL command 
CREATE TABLE [StationsLakes] (status nvarchar(10),     <xsl:for-each select="*">
			<xsl:if test="count(preceding-sibling::*[local-name() = local-name(current())])=0">[<xsl:value-of select="local-name()"/>]<xsl:value-of select="' '"/>
				<xsl:call-template name="getFieldType">
					<xsl:with-param name="name" select="local-name()"/>
				</xsl:call-template>
				<xsl:if test="position()!=last()  and count(following-sibling::*[local-name() != local-name(current())])&gt;0">,</xsl:if>
			</xsl:if>
		</xsl:for-each>
);
-- End SQL command 
</xsl:template>
	<xsl:template name="fields">
(status, 
<xsl:for-each select="*">
			<xsl:if test="count(preceding-sibling::*[local-name() = local-name(current())])=0">[<xsl:value-of select="local-name()"/>]<xsl:if test="position()!=last() and count(following-sibling::*[local-name() != local-name(current())])&gt;0">,
</xsl:if>
			</xsl:if>
		</xsl:for-each>
) </xsl:template>
	<xsl:template name="getFieldType">
		<xsl:param name="name" select="''"/>
		<xsl:choose>
			<xsl:when test="$name = 'NationalStationID'">
				<xsl:variable name="type">string</xsl:variable>
				<xsl:choose>
					<xsl:when test="$type = 'integer'">numeric(38)</xsl:when>
					<xsl:when test="$type = 'string'">nvarchar(50)</xsl:when>
					<xsl:when test="$type = 'float'">numeric(38)</xsl:when>
					<xsl:when test="$type = 'double'">numeric(38)</xsl:when>
					<xsl:otherwise>ntext</xsl:otherwise>
				</xsl:choose>
			</xsl:when>
			<xsl:when test="$name = 'StationName'">
				<xsl:variable name="type">string</xsl:variable>
				<xsl:choose>
					<xsl:when test="$type = 'integer'">numeric(7)</xsl:when>
					<xsl:when test="$type = 'string'">nvarchar(7)</xsl:when>
					<xsl:when test="$type = 'float'">numeric(7)</xsl:when>
					<xsl:when test="$type = 'double'">numeric(7)</xsl:when>
					<xsl:otherwise>ntext</xsl:otherwise>
				</xsl:choose>
			</xsl:when>
			<xsl:when test="$name = 'LakeName'">
				<xsl:variable name="type">string</xsl:variable>
				<xsl:choose>
					<xsl:when test="$type = 'integer'">numeric(10)</xsl:when>
					<xsl:when test="$type = 'string'">nvarchar(10)</xsl:when>
					<xsl:when test="$type = 'float'">numeric(10)</xsl:when>
					<xsl:when test="$type = 'double'">numeric(10)</xsl:when>
					<xsl:otherwise>ntext</xsl:otherwise>
				</xsl:choose>
			</xsl:when>
			<xsl:when test="$name = 'Longitude'">
				<xsl:variable name="type">float</xsl:variable>
				<xsl:choose>
					<xsl:when test="$type = 'integer'">numeric(38)</xsl:when>
					<xsl:when test="$type = 'string'">nvarchar(100)</xsl:when>
					<xsl:when test="$type = 'float'">numeric(38)</xsl:when>
					<xsl:when test="$type = 'double'">numeric(38)</xsl:when>
					<xsl:otherwise>ntext</xsl:otherwise>
				</xsl:choose>
			</xsl:when>
			<xsl:when test="$name = 'Latitude'">
				<xsl:variable name="type">float</xsl:variable>
				<xsl:choose>
					<xsl:when test="$type = 'integer'">numeric(10)</xsl:when>
					<xsl:when test="$type = 'string'">nvarchar(10)</xsl:when>
					<xsl:when test="$type = 'float'">numeric(10)</xsl:when>
					<xsl:when test="$type = 'double'">numeric(10)</xsl:when>
					<xsl:otherwise>ntext</xsl:otherwise>
				</xsl:choose>
			</xsl:when>
			<xsl:otherwise>ntext</xsl:otherwise>
		</xsl:choose>
	</xsl:template>
	<xsl:template name="globalReplace">
		<xsl:param name="outputString"/>
		<xsl:param name="target"/>
		<xsl:param name="replacement"/>
		<xsl:choose>
			<xsl:when test="contains($outputString,$target)">
				<xsl:value-of select="concat(substring-before($outputString,$target),$replacement)"/>
				<xsl:call-template name="globalReplace">
					<xsl:with-param name="outputString" select="substring-after($outputString,$target)"/>
					<xsl:with-param name="target" select="$target"/>
					<xsl:with-param name="replacement" select="$replacement"/>
				</xsl:call-template>
			</xsl:when>
			<xsl:otherwise>
				<xsl:value-of select="$outputString"/>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>
	<xsl:template name="getValue">
		<xsl:choose>
			<xsl:when test="count(following-sibling::*[local-name() = local-name(current())])=0">
				<xsl:value-of select="."/>
			</xsl:when>
			<xsl:otherwise>
				<xsl:call-template name="joinMultiValue">
					<xsl:with-param name="valueList" select="parent::*/child::*[local-name() = local-name(current())]"/>
				</xsl:call-template>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>
	<xsl:template name="joinMultiValue">
		<xsl:param name="valueList" select="''"/>
		<xsl:variable name="separator">
			<xsl:call-template name="getSeparator">
				<xsl:with-param name="element" select="local-name()"/>
			</xsl:call-template>
		</xsl:variable>
		<xsl:for-each select="$valueList">
			<xsl:choose>
				<xsl:when test="position() = 1">
					<xsl:value-of select="."/>
				</xsl:when>
				<xsl:otherwise>
					<xsl:value-of select="concat($separator, .)"/>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:for-each>
	</xsl:template>
	<xsl:template name="getSeparator">
		<xsl:param name="element" select="''"/>
		<xsl:value-of select="','"/>
	</xsl:template>
</xsl:stylesheet>