Unanswered: Generate DTD or XSD from SQL Server views
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:
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?