HOW TO: Write an XSD Schema from a Database by Using a DataSet

• SUMMARY
  • Write an XSD Schema from a DataSet with One Table Where All Columns Are Not Required in the Resulting XSD Schema
    • Create a Visual Basic .NET Application
  • Write an XSD Schema from a DataSet with Multiple DataTables Where All Tables Are Not Required in the Resulting XSD Schema

Expand all | Collapse all

SUMMARY

Use this step-by-step guide to write an XSD schema from a database. This articl...

Use this step-by-step guide to write an XSD schema from a database. This article focuses on the following two scenarios:

• How to write an XSD schema from a DataSet object with one table where all columns are not required in the resulting XSD schema.
• How to write an XSD schema from a DataSet object with multiple datatables where all tables are not required in the resulting XSD schema.

To create an XSD schema from a database, you can use the DataSet object in .NET Framework. The DataSet object reads and writes XML files. The DataSet object can contain heirarchical or relational data. Therefore, it can take data from a relational store and render it in a hierarchical format.

Back to the top

Write an XSD Schema from a DataSet with One Table Where All Columns Are Not Required in the Resulting XSD Schema

1. Create a connection to the database.
2. Fill the DataSet with the schema of the required table. To do this, use DataAdapter and the FillSchema method.
3. Delete the columns in the table that are not required in the resulting schema.
   NOTE: This step is included to show you how to remove a DataColumn from a DataTable.
4. Write the schema to an external file or to a Stream object by using the WriteXMLSchema method of the DataSet.

Create a Visual Basic .NET Application

1. Create a New Console application by using Visual Basic .NET projects.
2. Import the following namespaces:

   Imports System.Data
   Imports System.Data.SqlClient
   Imports System.IO
        

3. Type or paste the following code in the sub Main() area:

   Dim cnNwind As New SqlConnection("Data Source=myserver;user id=myusername;password=mypassword;Initial catalog=Northwind;")
           Dim daOrders As New SqlDataAdapter("Select * from Orders", cnNwind)
   
           Dim ds As New DataSet()
           Try
               daOrders.FillSchema(ds, SchemaType.Mapped, "Orders")
                                
               Dim dt As DataColumn
               dt = ds.Tables("Orders").Columns("Employeeid")
               ds.Tables("Orders").Columns.Remove(dt)
               
               ds.WriteXmlSchema("C:\Orders.xsd")
               Console.WriteLine("The XSD schema is written to C:\Orders.xsd.")
               Console.Read()
           Catch ex As Exception
               Console.WriteLine(ex.Message)
               Console.Read()
           End Try
        

4. In the aforementioned code, change the Sql Server name, username, and password.
5. Build and run the program. The following message appears in the Console window:
   The XSD schema is written to C:\Orders.xsd.

Back to the top

Write an XSD Schema from a DataSet with Multiple DataTables Where All Tables Are Not Required in the Resulting XSD Schema

1. Create a connection to the database.
2. Fill the DataSet with the schema of the required tables. To do this, use DataAdapter and the FillSchema method.
3. Delete the tables that are not required in the resulting schema. Remove any relationships to other tables before you remove the table.
   NOTE: This step is included to show you how to remove a datatable from a DataTable.
4. Write the schema to an external file or to a Stream object by using the WriteXMLSchema method of the DataSet.
5. Type or paste the following code into the Sub Main area of the Console application. If necessary, comment out the previous code before you insert the new code sample.

   Sub Main()
           Dim cnNwind As New SqlConnection("Data Source=myservername;user id=myusername;password=mypassword;Initial catalog=Northwind;")
           Dim daEmployees As New SqlDataAdapter("Select * from Employees", cnNwind)
           Dim daOrders As New SqlDataAdapter("Select * from Orders", cnNwind)
           Dim daOrderDetails As New SqlDataAdapter("select * from [Order details]", cnNwind)
   
           Dim ds As New DataSet()
           Try
               daEmployees.FillSchema(ds, SchemaType.Mapped, "Employees")
   
               daOrders.FillSchema(ds, SchemaType.Mapped, "Orders")
               daOrderDetails.FillSchema(ds, SchemaType.Source, "OrderDetails")
               Dim drEmpOrders As New DataRelation("EmpOrder", ds.Tables("Employees").Columns("Employeeid"), ds.Tables("Orders").Columns("Employeeid"))
               ds.Relations.Add(drEmpOrders)
               ds.Tables("Orders").Constraints.Remove("EmpOrder")
   
               Dim drOrderdetails As New DataRelation("OrderDetails", ds.Tables("Orders").Columns("Orderid"), ds.Tables("Orderdetails").Columns("Orderid"))
               ds.Relations.Add(drOrderdetails)
   
               ds.Relations.Remove("EmpOrder")
               ds.Tables.Remove("Employees")
   
               Dim strm As New StreamWriter("C:\OrderDetails.xsd", False)
               ds.WriteXmlSchema(strm)
               
               Console.WriteLine("The XSD schema is written to C:\OrderDetails.xsd.")
               Console.Read()
           Catch ex As Exception
               Console.WriteLine(ex.Message)
               Console.Read()
           End Try
   
       End Sub

6. Build and run the program. The following message appears in the Console window:
   The XSD schema is written to C:\OrderDetails.xsd.