5

I have a XSD file and many XML data files. What's the fastest way to create the database/tables from the XSD file and then load all XML files into the generated database schema?

For now, I don't care if it's using a code generation tool or whatever else. I just want to know the fastest way to do this.

Earlz
  • 62,085
  • 98
  • 303
  • 499
Hamarict
  • 143
  • 1
  • 2
  • 11
  • 1
    Look at the `XMLBulkLoad`. – Hamlet Hakobyan Jan 03 '13 at 16:09
  • xml data can have a lot of different form... From a simple "list" to a full graph of object. I bet such tool won't exists, as it's virtually impossible to achieve. OR you use Sql Server as a xml repository (a single table with the Xml datatype, or a simple string). In fact it will depend both on what you are trying to achieve, and what you will do with the data once loaded. – Steve B Jan 03 '13 at 16:10
  • Note that modern (>2008?) versions of SQL server do allow indexing XML "paths" and querying of XML via XPath. Unless your XML files are very simple, it'll probably be a lot easier and maybe even better in the long term to just use SQL server as a xml repository with XPath capabilities – Earlz Jan 03 '13 at 16:13

1 Answers1

3

Store the data as xml in the database:

  1. Create schema collection and create table: http://msdn.microsoft.com/en-us/library/ms176009.aspx
  2. Load xml into database: http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx
  3. Query data: http://beyondrelational.com/modules/2/blogs/28/posts/10292/xml-workshops.aspx

Then you don't need to worry about having to create and maintain a load of tables.

tom redfern
  • 30,562
  • 14
  • 91
  • 126
  • Thanks, that what i did, had a little help with this question http://stackoverflow.com/questions/1890923/xpath-to-fetch-sql-xml-value Its probably not the best option but the fastest one (as development speed not performance) for sure! – Hamarict Jan 03 '13 at 18:59
  • that #3 link seems to be not what i was expecting... maybe it is gone? – Taylor Brown Jul 23 '21 at 17:13