Generating XML from SQL Database in VB.NET

Description

This sample shows how you can obtain a Dataset from (in this case) a SQL Server database, and then write it out to an XML Document. As an "Extra Added Bonus(tm)", it can show you how to write the schema as well. The SQL Server database in question is the venerable and useful Northwind database.

The significant parameters are all declared at the beginning as strings, so that you can easily debug and tweak for your own usage. In this section, we create a connection string, a query string (in standard SQL) and specify a target filename.

Then, we call the function,

Shared Sub writeSQLQueryToFileAsXML(ByVal connString As String, ByVal query As String, ByValfilename As String)

This function first gets a connection, creates an adapter and specifies the connection and command to run through it, then obtains a dataset from that adapter.

This function then shows how you can write that DataSet out to a stream (in this case, a FileStream) as an XML document through the DataSet member, public void WriteXml(Stream). And if you've got some extra time on your hands, you can even uncomment the code in the last section, to write the DataSet's SCHEMA to a file!

An interesting corollary to this example would be to stream the DataSet's XML out to an HTTPResponse's HTTPWriter.outputStream, but that is both the subject of another sample, and also perhaps more easily done through native SQL Server capabilities.

Note that the XML that is written is output as a fragment, that is, there is no element, <?xml version="1.0"?> at the beginning, nor is there a unifying, single root node. While you will have to add both of these, this is really a sensible choice. First of all, only you, and your particular application context, can say what is the right name for the root node - is it <DelinquentCustomers>, or <GoldStarCustomers>? Secondly, it may be a node or set of nodes buried down inside a much larger document, such as a <Contacts> node under one of several <Salesman> nodes. Get the picture?

Compiling and Running the code:

vbc XMLGenFromSQLDbPB.vb.
Or
vbc /r:System.dll,System.Data.dll,System.Xml.dll  XMLGenFromSQLDbPB.vb.

This will return you with the location where it had saved the XML file.You can specify it in the String s.

Source Code:

Imports System
Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient
Imports System.IO
Namespace WriteXML
Public Class WriteXML
Shared Sub Main()
'*******************************************************************
' NOTE : YOU WILL NEED TO HAVE SQL SERVER (or MSDE) AVAILABLE, AND
' YOU WILL NEED THE NORTHWIND DATABASE INSTALLED IN THE SQL SERVER
' INSTANCE IN ORDER FOR THIS TO WORK. 
' MODIFY THE FOLLOWING CONNECTION STRING AND QUERY STRING TO RUN
' THIS SAMPLE AGAINST A DIFFERENT DATABASE AND/OR QUERY.
'*******************************************************************
Dim outputFileName As String = "C:/myXmlData" ' ".xml" will be appended.
Dim connString As String = "user id=sa;password=password;" + "Database=northwind;server=(local);"
Dim sqlQueryString As String = "SELECT * FROM Suppliers"
' Here's the meat of the demonstration.
writeSQLQueryToFileAsXML(connString, sqlQueryString, outputFileName)
Console.WriteLine("Wrote query results to {0}", outputFileName)
End Sub 'Main
Shared Sub writeSQLQueryToFileAsXML(ByVal connString As String, ByVal query As String, ByValfilename As String)
Dim myConn As New SqlConnection(connString)
Dim adapter As New SqlDataAdapter
adapter.SelectCommand = New SqlCommand(query, myConn)
' Build the DataSet
Dim myDs As New DataSet
adapter.Fill(myDs)
Dim myFs As FileStream = Nothing
' Get a FileStream object
myFs = New FileStream(filename + ".xml", FileMode.OpenOrCreate, FileAccess.Write)
' Apply the WriteXml method to write an XML document
myDs.WriteXml(myFs)
' It is always good housekeeping to close a file.
myFs.Close()
End Sub 'writeSQLQueryToFileAsXML 
End Class 'WriteXML '***************************************************
End Namespace 'WriteXML ' Uncomment the following code if you also want to
' dump the DataSet's schema to a file...
'***************************************************
' Get a FileStream object
myFs = new FileStream(filename + "_Schema.xml", FileMode.OpenOrCreate, FileAccess.Write)
myDs.WriteXmlSchema(myFs)
' It is always good housekeeping to close a file.
myFs.Close()
'***************************************************