I apologize in advance for a lengthy, and at times, not clear problem definition and appreciate your time reading this post.

I would like to use XML as a staging storage before I feed data into the production SQL Server 2000 database. I would like to have each incoming record in a separate XML file and then load each file into SQL server using .NET.

I got stuck on the preparation of a DTD or an XSD that I can use to generate individual XML files.

The problem is that all the tools want to create a relation between entities while I want the DTD to nest the entities without using relations. This is because when I get a new record from the supplier, it doesn’t have any of the keys from my database, and since I have only one record per XML file, I know from nesting how information is related.

Here is a small sample of the desired XML structure:

<model>Grand Cherokee</model>

The challenge is that in order to prepare the DTD or XSD I want to auto-derive it from the SQL Schema, and so far I was unable to find a tool that would allow me to prepare a schema without using the relations. For example, .NET, let’s me create a Dataset, load Vehicle, Colors and Engines tables, but then I have no means of defining that Colors should be nested within the Vehicle element without creating a DataRelation object.

Am I trying to do something unreasonable or is there a way to accomplish this task?