Generating XML Schema for SQL Server Table

[=?Utf-8?B?Sm9iIExvdA==?=]

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

thanks

[PS]

"Job Lot" <Jo****@discussions.microsoft.comwrote in message
news:CA**********************************@microsof t.com...

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.NorthwindConnectionStrin g)
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.
---------------------------------------
"Job Lot" <Jo****@discussions.microsoft.comwrote in message
news:CA**********************************@microsof t.com...

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

thanks

[=?Utf-8?B?Sm9iIExvdA==?=]

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

"PS" wrote:

>
"Job Lot" <Jo****@discussions.microsoft.comwrote in message
news:CA**********************************@microsof t.com...

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]

"Job Lot" <Jo****@discussions.microsoft.comwrote in message
news:5E**********************************@microsof t.com...

>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.

>
"PS" wrote:

>>
"Job Lot" <Jo****@discussions.microsoft.comwrote in message
news:CA**********************************@microso ft.com...

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