Generating SQL Server DB from XSD

Question

Duplicate: http://stackoverflow.com/questions/263836/generating-sql-schema-from-xml

---

In a project i am working on, i have a need to support either a strongly-typed dataset for storing the data as XML, or storing the data in sql server. Now i already have the XSD schema created and i would like to be able to create a sql server database using the tables and relationships defined in the XSD.

Is this possible? and if so, what is the best way to approach this problem?

---

Clarification: What i'm looking for is a way to do the above via code at runtime with C# and SQL Server. Can this be done?

Answer 1

I would write some XSLT to turn the XSD into SQL create statements.

Answer 2

If you are working on SQL2005, you have the possibility to create a table with strongly typed XML columns, so that each value is validated against an XML Schema Collection (i.e. an XSD). However I cannot tell you anything about performance, scalability etc.

If you try to translate an XSD into a set of relational tables, you will find that there is no unique mapping between XSD elements and SQL tables:

An XSD child element may be implemented as detail table, as a set of columns representing the element (if only 1 child is allowed), or as mandatory/optional 1:1/1:n relation.

A collection of XSD child elements can be a master-detail relation, or an n:m relation stored in a separate table along with the attributes.

IIRC there is no definition of primary and unique constraints in XSD, which poses another problem in automated schema generation.

This all does not mean that nobody has yet bothered to develop a tool for such a task. But it certainly means that the task cannot be fully automated.

Answer 3

I managed to come up with the following class based on the SQL Server Management Objects:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Rule=System.Data.Rule;

namespace XSD2SQL
{
public class XSD2SQL
{
    private readonly Server _server;
    private readonly SqlConnection _connection;
    private Database _db;
    private DataSet _source;
    private string _databaseName;

    public XSD2SQL(string connectionString, DataSet source)
    {
        _connection = new SqlConnection(connectionString);
        _server = new Server(new ServerConnection(_connection));
        _source = source;
    }

    public void CreateDatabase(string databaseName)
    {
        _databaseName = databaseName;
        _db = _server.Databases[databaseName];
        if (_db != null) _db.Drop();
        _db = new Database(_server, _databaseName);
        _db.Create();
    }

    public void PopulateDatabase()
    {
        CreateTables(_source.Tables);
        CreateRelationships();
    }

    private void CreateRelationships()
    {
        foreach (DataTable table in _source.Tables)
        {
            foreach (DataRelation rel in table.ChildRelations)
                CreateRelation(rel);
        }
    }

    private void CreateRelation(DataRelation relation)
    {
        Table primaryTable = _db.Tables[relation.ParentTable.TableName];
        Table childTable = _db.Tables[relation.ChildTable.TableName];

        ForeignKey fkey = new ForeignKey(childTable, relation.RelationName);
        fkey.ReferencedTable = primaryTable.Name;

        fkey.DeleteAction = SQLActionTypeToSMO(relation.ChildKeyConstraint.DeleteRule);
        fkey.UpdateAction = SQLActionTypeToSMO(relation.ChildKeyConstraint.UpdateRule);


        for (int i = 0; i < relation.ChildColumns.Length; i++)
        {
            DataColumn col = relation.ChildColumns[i];
            ForeignKeyColumn fkc = new ForeignKeyColumn(fkey, col.ColumnName, relation.ParentColumns[i].ColumnName);

            fkey.Columns.Add(fkc);
        }

        fkey.Create();

    }

    private void CreateTables(DataTableCollection tables)
    {
        foreach (DataTable table in tables)
        {                
            DropExistingTable(table.TableName);
            Table newTable = new Table(_db, table.TableName);

            PopulateTable(ref newTable, table);                
            SetPrimaryKeys(ref newTable, table);
            newTable.Create();

        }
    }

    private void PopulateTable(ref Table outputTable, DataTable inputTable)
    {
        foreach (DataColumn column in inputTable.Columns)
        {
            CreateColumns(ref outputTable, column, inputTable);
        }
    }

    private void CreateColumns(ref Table outputTable, DataColumn inputColumn, DataTable inputTable)
    {
        Column newColumn = new Column(outputTable, inputColumn.ColumnName);
        newColumn.DataType = CLRTypeToSQLType(inputColumn.DataType);
        newColumn.Identity = inputColumn.AutoIncrement;
        newColumn.IdentityIncrement = inputColumn.AutoIncrementStep;
        newColumn.IdentitySeed = inputColumn.AutoIncrementSeed;
        newColumn.Nullable = inputColumn.AllowDBNull;
        newColumn.UserData = inputColumn.DefaultValue;

        outputTable.Columns.Add(newColumn);
    }

    private void SetPrimaryKeys(ref Table outputTable, DataTable inputTable)
    {
        Index newIndex = new Index(outputTable, "PK_" + outputTable.Name);
        newIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
        newIndex.IsClustered = false;

        foreach (DataColumn keyColumn in inputTable.PrimaryKey)
        {                                
            newIndex.IndexedColumns.Add(new IndexedColumn(newIndex, keyColumn.ColumnName, true));                
        }
        if (newIndex.IndexedColumns.Count > 0)
            outputTable.Indexes.Add(newIndex);
    }



    private DataType CLRTypeToSQLType(Type type)
    {
        switch (type.Name)
        {
            case "String":
                return DataType.NVarCharMax;

            case "Int32":
                return DataType.Int;

            case "Boolean":
                return DataType.Bit;

            case "DateTime":
                return DataType.DateTime;

            case "Byte[]":
                return DataType.VarBinaryMax;


        }

        return DataType.NVarCharMax;
    }

    private ForeignKeyAction SQLActionTypeToSMO(Rule rule)
    {
        string ruleStr = rule.ToString();

        return (ForeignKeyAction)Enum.Parse(typeof (ForeignKeyAction), ruleStr);
    }

    private void DropExistingTable(string tableName)
    {
        Table table = _db.Tables[tableName];
        if (table != null) table.Drop();
    }

}
}

It hasn't been rigorously tested yet, and there needs to be more SQL to CLR types mapped out, but it does create a new database, all the tables, columns, primary keys, and foreign keys.

For this code to work, a few assemblies need to be referenced:

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnum

Hope this helps someone else out.

Answer 4

An excellent bit of work. Sorted me right out. Thanks very much for your effort.