Automatically Generating SQL Schema from XML

Question

We are attempting to use a SQL Server 2003 database for our test records and want a quick way to take NUnit and NAnt output and produce SQL schema and data. Is there a simple way to generate SQL Schema using the XSD file describing these XML documents?

Answer 1

You could use XSD. No, I'm serious. Go to a command prompt and type xsd and press Enter.

Here's what you will see (truncated):

I:\>xsd
Microsoft (R) Xml Schemas/DataTypes support utility
[Microsoft (R) .NET Framework, Version 1.0.3705.0]
Copyright (C) Microsoft Corporation 1998-2001. All rights reserved.


xsd.exe -
    Utility to generate schema or class files from given source.

xsd.exe <schema>.xsd /classes|dataset [/e:] [/l:] [/n:] [/o:] [/uri:]
xsd.exe <assembly>.dll|.exe [/outputdir:] [/type: [...]]
xsd.exe <instance>.xml [/outputdir:]
xsd.exe <schema>.xdr [/outputdir:]

Just follow the instructions.

Answer 2

Related question

Answer 3

As XSD in ambiguous in terms of master-detail relations, I doubt an automatic generation is possible.

For example, a declaration such as

<xs:element name="foo" type="footype" minOccurs="0" maxOccurs="unbounded" />

can be interpreted as child table "foo" (1:n) or as an n:m relation.

minOccurs="0" maxOccurs="1" may be a nullable column, or an optional 1:1 relation.

type="xs:string" maxOccurs="1" is a string ((n)varchar) column, or an optional lookup; but type="xs:string" maxOccurs="unbounded" is a detail table with a (n)varchar column.

Answer 4

There's a tool called ShreX that can can makes schemas from xsd and inserts from XML. It tries to do this by itself (you can annotade the xsd to steer it). If you want to decide the structure yourself it might not be what you want.