How to create an XML file from SQL in VB.NET

XML is a general purpose tag based language and very easy to transfer and store data across applications. The .Net technology is widely supported XML file format. The .Net Framework provides the Classes for read, write, and other operations in XML formatted files . Moreover the Dataset in ADO.NET uses XML format as its internal storage format.

There are several ways to create an XML file . In the previous sections we already saw how to create an XML file using XmlTextWriter and also created an XML file using manually created Dataset. Here we are going to create an XML file from Database. Make an SQL connection to the Database and execute the sql and store the data in a Datset. Call Dataset's WriteXml() method and pass the file name as argument.

Imports System.Xml
Imports System.Data.SqlClient
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connetionString As String
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim ds As New DataSet
        Dim sql As String

        connetionString = "Data Source=servername;Initial Catalog=databsename;User ID=username;Password=password"
        connection = New SqlConnection(connetionString)
        sql = "select * from Product"
        Try
            connection.Open()
            adapter = New SqlDataAdapter(sql, connection)
            adapter.Fill(ds)
            connection.Close()
            ds.WriteXml("Product.xml")
            MsgBox("Done")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Sub
End Class