Generating XML Schema for SQL Server Table

[Job Lot]

how can persist schema for a sql server table into an xml file from .net
application?

thanks

[PS]

Post by Job Lot
how can persist schema for a sql server table into an xml file from .net
application?

There are probably many ways to do this. Some quick overviews and then I can
give you more details.

1. Sql Server can provide XML based information that you can use
SqlXmlCommand. I use this to create an XML schema of my complete database
from which I apply an XLST to and generate code from this. This is used when
I know nothing about my database at all, not even a table name.

2. If you know a table name it has some data then you can SELECT TOP 1 *
FROM TableName and then while reading the first row using SqlDataReader you
can get the field count and then get the column name and column type and
create an XML file from this information.

3. I believe that if you have a strongly typed dataset then you can get
schema information but then if you already have the typed dataset then you
probably already have the information you need.

The best answer is probably where and how the information is being used,
e.g. during development, in a production environment, is the table
information dynamic or can you setup your application ahead of time with a
typed dataset.

PS

[Job Lot]

I am intending to use BulkLoad for restoring database from an XML file.
BulkLoad requires schema file as one of the arguments.

Post by PS

Post by Job Lot
how can persist schema for a sql server table into an xml file from .net
application?

There are probably many ways to do this. Some quick overviews and then I can
give you more details.
1. Sql Server can provide XML based information that you can use
SqlXmlCommand. I use this to create an XML schema of my complete database
from which I apply an XLST to and generate code from this. This is used when
I know nothing about my database at all, not even a table name.
2. If you know a table name it has some data then you can SELECT TOP 1 *
FROM TableName and then while reading the first row using SqlDataReader you
can get the field count and then get the column name and column type and
create an XML file from this information.
3. I believe that if you have a strongly typed dataset then you can get
schema information but then if you already have the typed dataset then you
probably already have the information you need.
The best answer is probably where and how the information is being used,
e.g. during development, in a production environment, is the table
information dynamic or can you setup your application ahead of time with a
typed dataset.
PS

[PS]

Post by Job Lot
I am intending to use BulkLoad for restoring database from an XML file.
BulkLoad requires schema file as one of the arguments.

You are wanting to import an XML file into your database? You can also use
bcp with an XML file.

Post by Job Lot

Post by PS

Post by Job Lot
how can persist schema for a sql server table into an xml file from .net
application?

There are probably many ways to do this. Some quick overviews and then I can
give you more details.
1. Sql Server can provide XML based information that you can use
SqlXmlCommand. I use this to create an XML schema of my complete database
from which I apply an XLST to and generate code from this. This is used when
I know nothing about my database at all, not even a table name.
2. If you know a table name it has some data then you can SELECT TOP 1 *
FROM TableName and then while reading the first row using SqlDataReader you
can get the field count and then get the column name and column type and
create an XML file from this information.
3. I believe that if you have a strongly typed dataset then you can get
schema information but then if you already have the typed dataset then you
probably already have the information you need.
The best answer is probably where and how the information is being used,
e.g. during development, in a production environment, is the table
information dynamic or can you setup your application ahead of time with a
typed dataset.
PS

[RobinS]

Here you go...

Two Tables in one dataset:

Dim cn As SqlConnection = _
New SqlConnection(My.Settings.NorthwindConnectionString)
cn.Open()
Dim SQLString As String = _
"SELECT CustomerID, CompanyName FROM Customers;" & _
"SELECT OrderID, CustomerID, OrderDate FROM Orders"

Dim da As SqlDataAdapter = New SqlDataAdapter(SQLString, cn)
da.TableMappings.Add("Table", "Customers")
da.TableMappings.Add("Table1", "Orders")
Dim ds As DataSet = New DataSet("NWDataSet")
da.FillSchema(ds, SchemaType.Mapped)
ds.Relations.Add("Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"))
ds.WriteXmlSchema("E:\NWDataSet.XSD")

One table in a dataset:

Dim da2 As SqlDataAdapter = _
New SqlDataAdapter("SELECT * FROM Customers", cn)
da2.TableMappings.Add("Table", "Customers")
Dim ds2 As DataSet = New DataSet("NWCustomerDataSet")
da2.FillSchema(ds2, SchemaType.Mapped)
ds2.WriteXmlSchema("E:\NWCustomerDataSet.XSD")
cn.Close()

Robin S.
---------------------------------------

Post by Job Lot
how can persist schema for a sql server table into an xml file from .net
application?
thanks