CODEWITHSUNDEEP
sql-servervb.net
Rahul Dubey
Rahul Dubey

Reputation: 208

How to generate XML from SQL Database in VB.NET?

How to generate a XML with a Dataset obtained from (in this case) a SQL Server database, and then write it out to an XML Document ?

Upvotes: 0

Views: 2236

Answers (1)

Sandip_JACK
Sandip_JACK

Reputation: 228

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()
'********************************************

Upvotes: 2

Related Questions