Eureka!

A very powerful and extremely simple tool to convert your SQL results into an XML file is the following:

    Dim objDS As New DataSet

    Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click        Dim objConn As New SqlConnection        Dim objDA As SqlDataAdapter

        Try            objConn.ConnectionString = "server=(local);database=northwind;integrated security=SSPI"            objConn.Open()            objDA = New SqlDataAdapter("SELECT * FROM customers ORDER BY companyname", objConn)            objDA.Fill(objDS, "customers")            objConn.Close()            dgData.DataSource = objDS.Tables("customers")        Catch objError As Exception            MessageBox.Show(objError.Message)        End Try    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click        Try            objDS.WriteXml("c:projectscustomers.xml")        Catch objError As Exception            MessageBox.Show(objError.Message)        End Try

Using this method you can save any table in your dataset to an XML file. This can come in handy when transfering data to clients, or even to coworkers. Not to mention to make a backup of your data before making changes.

Well, after reserching this issue for about two days, and in the process even starting to create my XSD from scratch by hand, I found a very simple and elegant method provided by Micro$oft to convert their legacy XDR schema files into a valid and accepted XSD file.

However, the first trick is knowing that you’re dealing with an XDR. The following text posted at TopXML put me on the right track:

XML Data Reduced (XDR): DTDs proved to be inadequate for the needs of users of XML due to to a number of reasons. The main reasons behind the criticisms of DTDs were the fact that they used a different syntax than XML and their non-existent support for datatypes. XDR, a recommendation for XML schemas, was submitted to the W3C by the Microsoft Corporation as a potential XML schema standard which but was eventually rejected. XDR tackled some of the problems of DTDs by being XML based as well as supporting a number of datatypes analogous to those used in relational database management systems and popular programming languages. Below is an XML schema, using XDR, for the above XML fragment.
XDR FOR SAMPLE XML FRAGMENT

<Schema name="myschema" xmlns="urn:schemas-microsoft-com:xml-data"                       xmlns:dt="urn:schemas-microsoft-com:datatypes">  <ElementType name="age" dt:type="ui1" />  <ElementType name="name" dt:type="string" />  <AttributeType name="gtnum" dt:type="string" />  <ElementType name="gatech_student" order="seq">   <element type="name" minOccurs="1" maxOccurs="1"/>   <element type="age" minOccurs="1" maxOccurs="1"/>   <attribute type="gtnum" />  </ElementType> </Schema>

The above schema specifies types for a name element that contains a string as its content, an age element that contains an unsigned integer value of size one byte (i.e. btw 0 and 255), and a gtnum attribute that is a string value. It also specifies a gatech_student element that has one occurence each of a name and an age element in sequence as well as a gtnum attribute.

From there it was a hop-skip-and-a-jump to google the following, also found on TopXML to find out how to convert a XDR to XSD:

In the commandline type:

xsd file.xdr [/o:directory]

My, what a beautiful thing! That simple piece of information saved me hours, and potentially days of work.

Edit: One thing to note is that this conversion process does not bring over the quantifiers minOccurs and maxOccurs on the elements. They default all to one, so this must be updated manually. Note that a ‘*’ in XDR is the equivalent of ‘unbounded’ in XSL.