Feeding XML to SQL2000 database

[Froefel]

I'm starting research on how I could best approach feeding XML files
to a relational database.
Here's the scenario:
- we receive several hundred XML files per week, all of which adhere
to the same structure (we have complete control over the structure and
the data that goes into the XML).
- upon receipt of an XML file, we should extract the data into a
Sql2000 relational database. Once that's done, we can discard the XML
(only used for transporting the data).
- the data in the database will be used for extensive querying

- we currently have an XSD file (though we don't use it for any other
purpose than to validate the XML files when they come in).
- we have not created the relational database yet.

I'm trying to figure out how I can create the DB schema from the XSD,
and whether that's the best approach, given that the XSD doesn't
contain much info on field types or sizes, relationships, keys,
indexes etc...
Secondly, I'm also trying to figure out how to import the data from
the XML to the DB in a reliable way. Should I create an XSD that
contains all the mapping information (tables, fields, relationships,
keys, etc...), or should I write the import functionality in a .NET
module for more flexibility and logging control?
If the latter approach is more flexible, I assume that I would load
the XML and use the DOM to read nodes and put them in the appropriate
place in the database.

Finally, I'm playing around with XMLSpy. Do any of you have a
recommendation for tools to assist in what I need to achieve?

If anyone can give some hints on how to approach this endeavor, I'd
greatly appreciate it.

-- Hans.

[clintonG]

Its called "shredding" not "feeding" so there's a useful search term.

--
<%= Clinton Gallagher
NET csgallagher AT metromilwaukee.com
URL http://clintongallagher.metromilwaukee.com/
"Froefel" <ha************@gmail.comwrote in message
news:ab**********************************@o42g2000 hsc.googlegroups.com...

I'm starting research on how I could best approach feeding XML files
to a relational database.
Here's the scenario:
- we receive several hundred XML files per week, all of which adhere
to the same structure (we have complete control over the structure and
the data that goes into the XML).
- upon receipt of an XML file, we should extract the data into a
Sql2000 relational database. Once that's done, we can discard the XML
(only used for transporting the data).
- the data in the database will be used for extensive querying

- we currently have an XSD file (though we don't use it for any other
purpose than to validate the XML files when they come in).
- we have not created the relational database yet.

I'm trying to figure out how I can create the DB schema from the XSD,
and whether that's the best approach, given that the XSD doesn't
contain much info on field types or sizes, relationships, keys,
indexes etc...
Secondly, I'm also trying to figure out how to import the data from
the XML to the DB in a reliable way. Should I create an XSD that
contains all the mapping information (tables, fields, relationships,
keys, etc...), or should I write the import functionality in a .NET
module for more flexibility and logging control?
If the latter approach is more flexible, I assume that I would load
the XML and use the DOM to read nodes and put them in the appropriate
place in the database.

Finally, I'm playing around with XMLSpy. Do any of you have a
recommendation for tools to assist in what I need to achieve?

If anyone can give some hints on how to approach this endeavor, I'd
greatly appreciate it.

-- Hans.