SQL to Dataset to XML via .NET

[benjatado]

I am trying to create several .xml files, mirrored from my SQL database that are shaped according to an XSD schema. However, I have hit a brick wall!
Droving through several materials on how to perform this function has resulted in some slightly jarring results!
Basically, I would like to create a dataset.xsd XML map of a stored procedure from the SQL database, and through use of this .xsd pass the resulting correctly formatted .xml to a file (or heck even the /bin!)
Here is a code that I have been working with to take the stored procedure view and pass it to an .xml file - to the /bin. But the dataset.xsd file does not effect the result, nor return the correct schema using "WriteSchema".
I must be missing something crucial...

CODE: 

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

Module Module 1 
     
    Sub Main() 
Dim objConn As New SqlConnection("Server=...;Database=....;Integrated Security=....") 
objConn. Open() 
Dim objCmd As New SqlCommand("MyProcedure", objConn) 
objCmd.CommandType = CommandType.StoredProcedure 
Dim objAdpt As New SqlDataAadapter (objCmd) 
Dim objDs As New DataSet ("MyDataset") 
objAdpt.Fill (objDs, "Catalog") 

Dim objFile As New FileStream("Catalog.xml", FileMode.Create) 
Dim objSWriter As New StreamWriter(objFile) 

objDs.WriteXml(objSWriter.XmlWriteMode.WriteSchema) 

objSWriter.Close() 
objFile.Close() 
objConn.Close() 

End Sub 

End Module

I hope someone can point me in the right direction here...

Thanks -

Benjatado

 

[dyarwood]

I'm investigating this at the minute aswell and SQL server 2000 can produce XML outputs using query analyser. Yet to try from a .net app though. You write the stored proceedure to result in a string (which would be the XML) which can be read using the app. Not sure if that is any help but might give you something to think about.

 

[simonchristieis]

have a look at "for xml explicit" on the sql forum.