Building schema and tables from XML?
I've been given a web site that generates some XML. I need to load the XML from the site (that's trivial, of course) and then populate a database table with the information contained in the XML.
For example, the XML looks something like this:
<OutputData>
<Response>
<Result code="0">Operation Successful</Result>
<Agents>
<Agent code="452">Bill</Agent>
<Agent code="999">Fred</Agent>
</Agents>
<Stats>
<UpSince>3993848</UpSince>
<LastHit>88288</LastHit>
</Stats>
</Response>
</OutputData>
I could make an XSD by hand, but that would be very cumbersome (the XML is actually quite huge - this is just an example of part of it). I could create the database tables and generate the XSD from them, but the tables wouldn't reflect the schema of the XML perfectly, I suspect.
The goal, of course, being the automation of reading the XML into the database.
So the question - given some XML that someone just throws at you, what's the proper way to create an XSD to read it in and eventually get it into tables in SQL 2005?
Many thanks in advance for some adult supervision :-)
