Getting XML Schema from MS SQL Database

Question

Is it possible to generate a XML Schema of a Database programatically with .Net and C#? I want to look into NDbUnit but for big databases it would not really be feasible to make a Schema manually?

EDIT: Question wasn't clear.

Answer 1

Is it possible to generate a XML Schema from a Database?

It sure is, XMLSpy can generate XML Schema from a database.

There's another way, though I've never tested it:

create table Person
(
Age int not NULL check( Age > 0) ,
Height numeric(10,2) not NULL check( Height > 5),
Gender varchar(5) not null check( Gender in ('M', 'F', 'O')),
BirthDate datetime null,
)

DECLARE @schema xml
SET @schema = (SELECT * FROM Person FOR XML AUTO, ELEMENTS, XMLSCHEMA('PersonSchema'))
select @schema

Answer 2

I could do it like this:

DataSet results = new DataSet();

SqlCommand command = new SqlCommand("SELECT * FROM table", new SqlConnection(connectionString));

SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);

sqlAdapter.FillSchema(results, SchemaType.Mapped);//Fills dataset with schema from query
results.WriteXmlSchema(mySchema);

Problem is, how could I adapt this method so that it could be used with a indeterminate amount of tables? Without building up a string though concatenation which is not exactly ideal!

Answer 3

Use MyGeneration's typed-dataset-template to auto-generate the XSD (http://www.mygenrationssoftware.com). Its free, OSS, and works great. Also, the NDbUnit team is presently working on an add-on tool to NDbUnit that will make it possible to not only extract the schema from the database, but also to edit the test data that is contained within the accompanying XML dataset. ETA for this tool is about mid-July.

Answer 4

Thanks for your information. I am going to try the different solutions that you have.