Create DDL from an XSD file, Part II
As requested, here is the stylesheet for creating a series of SQL DDL statements from a DataSet schema file.
The results may require some tweaking, depending on the structure of your schema. If you have a "collection"-type element that has no other purpose but to enclose multiple instances of a sub-element, you may wind up with a superfluous table. That was something I couldn't work around without procedural code.
Good luck with it, and let me know how it works out.
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"version="1.0"xmlns:xs="http://www.w3.org/2001/XMLSchema"xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"><xsl:output method="text" omit-xml-declaration="yes" /><xsl:template match="/"><xsl:text>-- ----------------------------------------------------------------- DDL generated from a .Net DataSet XSD via XSLT-- Stylesheet by Ann Lewkowicz, 2007-- ---------------------------------------------------------------</xsl:text><xsl:apply-templates /></xsl:template><!-- Get schema nodes from this schema and any included schemas --><xsl:variable name="contents" select="/|document(//xs:include/@schemaLocation)" /><xsl:variable name="complexTypes" select="$contents//xs:complexType[@name]" /><!-- Match data table elements --><xsl:template match="/" ><xsl:for-each select="$contents" ><xsl:apply-templates select=".//xs:element" /></xsl:for-each></xsl:template><!--xsl:template match="xs:element[xs:complexType]"--><xsl:template match="xs:element"><!-- Get the complex type that defines it, if any --><xsl:variable name="complexType" select="xs:complexType[not(@name)]|$complexTypes[@name=current()/@type]" /><xsl:if test="$complexType"><!-- Only create a table if the table has at least one simple type'd element or attribute. --><xsl:variable name="fields"select="$complexType/xs:sequence/xs:element[not(@type=$complexTypes/@name)]|$complexType/xs:attribute|$complexType/xs:simpleContent|$complexType/xs:simpleContent/xs:extension/xs:attribute" /><xsl:if test="$fields"><!-- table name --><xsl:variable name="tableName" select="@name" /><!-- Generated primary key name. We may not use this, depending onwhether a primary key is declared in the xsd. --><xsl:variable name="generatedKey" select="concat($tableName, '_id')" /><!-- Create statement --><xsl:text>CREATE TABLE dbo.[</xsl:text><xsl:value-of select="$tableName" /><xsl:text>] ( </xsl:text><!-- Get primary key declaration, if any, and use that to determinewhether to create an Id field --><xsl:variable name="primaryKey"select="//xs:key[@msdata:PrimaryKey='true'][xs:selector[@xpath=concat('.//mstns:',$tableName)]]" /><!-- Insert ID field --><xsl:if test="not($primaryKey)" ><xsl:text>	[</xsl:text><xsl:value-of select="$generatedKey"/><xsl:text>] int Identity(1,1), </xsl:text></xsl:if><!-- If table is a nested table, insert parent table Id field --><!-- '<xsl:value-of select="$contents//xs:element[@type=current()/ancestor::*/@name]/@name" />'--><xsl:variable name="ancestorTables"select="ancestor::xs:element[@name][not(@msdata:IsDataSet)][xs:complexType[xs:sequence[xs:element[@type]] or xs:attribute[@type] or xs:simpleContent]]|$contents//xs:element[@type=current()/ancestor::*/@name]" /><xsl:if test="$ancestorTables"><xsl:variable name="foreignKey" select="concat( $ancestorTables[1]/@name, '_id' )" /><!-- Build foreign key field name --><xsl:text>	[</xsl:text><xsl:value-of select="$foreignKey" /><xsl:text>] int</xsl:text><!-- Keywords and key name --><xsl:text> CONSTRAINT FK_</xsl:text><xsl:value-of select="$tableName"/><xsl:text>_</xsl:text><xsl:value-of select="$ancestorTables[1]/@name" /><!-- Build reference to parent table --><xsl:text> FOREIGN KEY REFERENCES dbo.[</xsl:text><xsl:value-of select="$ancestorTables[1]/@name" /><xsl:text>]</xsl:text><!-- Name of key field of parent --><xsl:text> ( [</xsl:text><xsl:value-of select="$foreignKey" /><xsl:text>] ), </xsl:text></xsl:if><!-- Iterate through fields --><xsl:for-each select="$fields[not(@type=$complexTypes/@name)]"><!-- white space --><xsl:text>	</xsl:text><!-- Field name --><xsl:text>[</xsl:text><xsl:choose><xsl:when test="@msdata:ColumnName"><xsl:value-of select="@msdata:ColumnName" /></xsl:when><xsl:when test="@name"><xsl:value-of select="@name" /></xsl:when><xsl:when test="local-name()='simpleContent'"><xsl:value-of select="concat(parent::*/@name,'_Text')" /></xsl:when></xsl:choose><xsl:text>] </xsl:text><!-- Type --><xsl:choose><xsl:when test="@type"><xsl:call-template name="Types"><xsl:with-param name="type" select="@type" /></xsl:call-template></xsl:when><xsl:otherwise><xsl:call-template name="Types"><xsl:with-param name="type" select="xs:extension/@base" /></xsl:call-template></xsl:otherwise></xsl:choose><!-- Handle identity columns --><xsl:if test="@msdata:AutoIncrement='true'"><xsl:text> IDENTITY(</xsl:text><xsl:choose><!-- If a seed was specified --><xsl:when test="@msdata:AutoIncrementSeed"><xsl:value-of select="@msdata:AutoIncrementSeed"/></xsl:when><xsl:otherwise><xsl:text>1</xsl:text></xsl:otherwise></xsl:choose><xsl:text>,</xsl:text><xsl:choose><!-- If a step is specified --><xsl:when test="@msdata:AutoIncrementStep"><xsl:value-of select="@msdata:AutoIncrementStep" /></xsl:when><xsl:otherwise><xsl:text>1</xsl:text></xsl:otherwise></xsl:choose><xsl:text>)</xsl:text></xsl:if><!-- Optional elements become nullable columns --><xsl:if test="@minOccurs=0"> NULL</xsl:if><!-- separator --><xsl:if test="position()<last()">,</xsl:if><xsl:text> </xsl:text></xsl:for-each><!-- Primary key. We determine the primary key by selecting an xs:keyelement that has the msdata:PrimaryKey attribute flag, that has achild xs:selector element that references our table name. --><xsl:choose><xsl:when test="$primaryKey"><!-- White space and keywords --><xsl:text> 	PRIMARY KEY ( </xsl:text><!-- Go through all the fields --><xsl:for-each select="$primaryKey/xs:field"><xsl:text>[</xsl:text><xsl:value-of select="substring-after(@xpath, 'mstns:')" /><xsl:text>]</xsl:text><xsl:if test="position()<last()">, </xsl:if></xsl:for-each><!-- Closing parenthesis --><xsl:text> )</xsl:text></xsl:when><xsl:otherwise><!-- White space and keywords --><xsl:text> 	PRIMARY KEY ( [</xsl:text><xsl:value-of select="$generatedKey" /><xsl:text>] )</xsl:text></xsl:otherwise></xsl:choose><!-- closing parenthesis and white space --><xsl:text> ) </xsl:text></xsl:if></xsl:if><xsl:apply-templates /></xsl:template><!-- Process the value of the "type" attribute of an element. --><xsl:template name="Types"><xsl:param name="type" /><xsl:choose><!-- Process ordinary simple types --><xsl:when test="substring-after($type,':')='int'">int</xsl:when><xsl:when test="substring-after($type, ':')='string'">varchar<xsl:if test="local-name()!='restriction'">(255)</xsl:if></xsl:when><xsl:when test="$type='money_type'">money</xsl:when><xsl:when test="substring-after($type,':')='nonNegativeInteger'">int</xsl:when><xsl:when test="substring-after($type,':')='date'">datetime</xsl:when><xsl:when test="substring-after($type,':')='boolean'">bit</xsl:when><xsl:when test="substring-after($type,':')='decimal'">decimal(19,9)</xsl:when><xsl:when test="substring-after($type,':')='gYearMonth'">datetime</xsl:when><!-- If a type is not one of the simple types above, look for adefined simple type and reference its base. This is recursive,so a defined simple type that restricts another defined simpletype will eventually reach an xsl: data type --><xsl:otherwise><xsl:apply-templates select="$contents//xs:simpleType[@name=$type]" mode="derivedType" /></xsl:otherwise></xsl:choose></xsl:template><!-- Handles restriction element of xs:simpleType declaration --><xsl:template match="xs:restriction" mode="derivedType" ><xsl:call-template name="Types"><xsl:with-param name="type"><xsl:value-of select="@base"/></xsl:with-param></xsl:call-template><xsl:apply-templates mode="derivedType" /></xsl:template><!-- Handles max length constraint (for strings) of xs:simpleType declaration --><xsl:template match="xs:maxLength" mode="derivedType" >(<xsl:value-of select="@value" />)</xsl:template><!-- Create foreign keys.For this, we're processing xs:keyref elements. We determine thechild table name from the selector child of the keyref, and theparent table name from the key specified in the xs:keyref as thekey of the parent table.--><xsl:template name="ForeignKey" match="xs:keyref[@refer]" ><!-- variables --><xsl:variable name="parentKeyName" select="@refer" /><xsl:variable name="childTable" select="substring-after(xs:selector[1]/@xpath, './/mstns:')" /><xsl:variable name="parentTable" select="substring-after(//xs:key[@name=$parentKeyName][1]/xs:selector[1]/@xpath, './/mstns:')" /><!-- Opening keywords --><xsl:text>ALTER TABLE dbo.</xsl:text><!-- Child table being changed --><xsl:value-of select="$childTable" /><!-- Whitespace and constraint keywords --><xsl:text> 	ADD CONSTRAINT </xsl:text><!-- Make constraint name --><xsl:text>[FK_</xsl:text><xsl:value-of select="$childTable" /><xsl:text>_</xsl:text><xsl:value-of select="$parentTable" /><xsl:text>] </xsl:text><!-- Foreign key keyword and parenthesis --><xsl:text>FOREIGN KEY 	( </xsl:text><!-- Fields in foreign key --><xsl:for-each select="xs:field"><xsl:text>		[</xsl:text><xsl:value-of select="substring-after(@xpath, 'mstns:')" /><xsl:text>]</xsl:text><xsl:if test="position()<last()"><xsl:text>,</xsl:text></xsl:if><xsl:text> </xsl:text></xsl:for-each><!-- Closing parenthesis and REFERENCES keyword --><xsl:text>	) 	REFERENCES </xsl:text><!-- Referenced table name --><xsl:text>dbo.</xsl:text><xsl:value-of select="$parentTable" /><xsl:text> 	( </xsl:text><!-- Referenced fields --><xsl:for-each select="//xs:key[@name=$parentKeyName][1]/xs:field"><xsl:text>		[</xsl:text><xsl:value-of select="substring-after(@xpath, 'mstns:')" /><xsl:text>]</xsl:text><xsl:if test="position()<last()"><xsl:text>,</xsl:text></xsl:if><xsl:text> </xsl:text></xsl:for-each><!-- Closing parenthesis and white space--><xsl:text>	) </xsl:text></xsl:template></xsl:stylesheet>
posted by Ann at 1:18 PM
17 comments
