Best way to create a DB-tablestructure

Question

DUPLICATE: http://stackoverflow.com/questions/467176/generating-sql-server-db-from-xsd

And here

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

---

I have loads and loads of xml files with data, and a schema-file (.xsd) which describes the structure of the xml.

I want to store the data in a MSSQL-database so that I can query it later and display it on a web-site.

I must now create the db-structure, and have so far thought of 3 ways of creating the tables:

1. Using xmlspy I could load the xsd and use the "create DB from xsd" there. The "trouble" is that I have to manually add the relations between the tables, and also add the columns that is used for these relations.
2. Using Microsoft SQL Management Studio I could graphically create the tables and relations. The "trouble" here is that the xsd describes about 100 tables and the thought of manually doing this in a GUI way is scary. I would loose track of where I was somewhere in there.
3. Handwriting the sql in notepad or something. Boring, but then I could do it in small steps, something I could not do with the two other options.

Is there any other way I havent't thought of?

Answer 1

You could do something similar to option (1): import the xsd into a database design tool (e.g. ERwin or PowerDesigner) then do the editing steps in a "graphical" environment, and then have the tool generate the database.

I'm not sure how well these tools work with xml and xsd and you may have to generate the db using xmlspy and then reverse engineer the database. But a good tool will make this easier than "just" with the database.

Hope that this is not too similar to the option (2) you mentioned ...