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

How to read an XML file in VB.NET using ADO.NET - Dataset

XML is a platform independent language, so the information formatted in XML can be use in any other platforms (Operating Systems) . 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 .
In the previous section we already saw how to read an XML file through Node navigation . Here we are going to read an XML file using an DataSet. Here Dataset is using an XmlReader for read the content of the file. Locate the XML file using XmlReader and pass the XmlReader as argument of Dataset.
Click here to download the input file product.xml

Imports System.Xml
Imports System.Data
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xmlFile As XmlReader
        xmlFile = XmlReader.Create("Product.xml", New XmlReaderSettings())
        Dim ds As New DataSet
        ds.ReadXml(xmlFile)
        Dim i As Integer
        For i = 0 To ds.Tables(0).Rows.Count - 1
            MsgBox(ds.Tables(0).Rows(i).Item(1))
        Next
    End Sub
End Class

How to create an XML file in VB.NET using Dataset

XML is a tag based language, that means the document is made up of XML tags that contain information. We can create an XML file in several ways.

In the previous section we create an XML file using XmlTextWriter Class. Here we are creating an XML file Product.XML using an ADO.NET Dataset. For that we have to manually create a Datatable first and add the data of Product.XML in the Datatable . Then add the Datatable in a Dataset . Call the method WriteXml of Dataset and pass the file name Product.XML as argument.

Imports System.Xml
Imports System.Data

Public Class Form1
    Dim dt As DataTable
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim ds As New DataSet
        dt = New DataTable()
        dt.Columns.Add(New DataColumn("Product_ID", Type.GetType("System.Int32")))
        dt.Columns.Add(New DataColumn("Product_Name", Type.GetType("System.String")))
        dt.Columns.Add(New DataColumn("product_Price", Type.GetType("System.Int32")))
        fillRows(1, "product1", 1111)
        fillRows(2, "product2", 2222)
        fillRows(3, "product3", 3333)
        fillRows(4, "product4", 4444)
        ds.Tables.Add(dt)
        ds.Tables(0).TableName = "product"
        ds.WriteXml("Product.xml")
        MsgBox("Done")
    End Sub

    Private Sub fillRows(ByVal pID As Integer, ByVal pName As String, ByVal pPrice As Integer)
        Dim dr As DataRow
        dr = dt.NewRow()
        dr("Product_ID") = pID
        dr("Product_Name") = pName
        dr("product_Price") = pPrice
        dt.Rows.Add(dr)
    End Sub
End Class

How to open and read an XML file in VB.NET

XML is a self describing language and it gives the data as well as the rules to extract what the data it contains. Reading an XML file means that we are reading the information embedded in XML tags in an XML file.

In the previous program we create an XML file and named it as products.xml. The following program read that file and extract the contents inside the XML tag. We can read an XML file in several ways depends on our requirement. This program read the content in Node wise . Here we are using XmlDataDocument class to read the XML file . In this program it search the Node < Product > and its child Nodes and extract the data in child nodes.

Imports System.Xml
Imports System.IO
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xmldoc As New XmlDataDocument()
        Dim xmlnode As XmlNodeList
        Dim i As Integer
        Dim str As String
        Dim fs As New FileStream("products.xml", FileMode.Open, FileAccess.Read)
        xmldoc.Load(fs)
        xmlnode = xmldoc.GetElementsByTagName("Product")
        For i = 0 To xmlnode.Count - 1
            xmlnode(i).ChildNodes.Item(0).InnerText.Trim()
            str = xmlnode(i).ChildNodes.Item(0).InnerText.Trim() & " | " & xmlnode(i).ChildNodes.Item(1).InnerText.Trim() & " | " & xmlnode(i).ChildNodes.Item(2).InnerText.Trim()
            MsgBox(str)
        Next
    End Sub
End Class

How to create an XML file in VB.NET

XML is a platform independent language, so the information formatted in XML can be used in any other platforms (Operating Systems). If we create an XML file in one platform it can be used in other platforms also.

For creating a new XML file in VB.NET, we are using XmlTextWriter class . The class takes FileName and Encoding as argument. Also we are here passing formating details . The following source code creating an XML file product.xml and add four rows in the file.

Imports System.Xml
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim writer As New XmlTextWriter("product.xml", System.Text.Encoding.UTF8)
        writer.WriteStartDocument(True)
        writer.Formatting = Formatting.Indented
        writer.Indentation = 2
        writer.WriteStartElement("Table")
        createNode(1, "Product 1", "1000", writer)
        createNode(2, "Product 2", "2000", writer)
        createNode(3, "Product 3", "3000", writer)
        createNode(4, "Product 4", "4000", writer)
        writer.WriteEndElement()
        writer.WriteEndDocument()
        writer.Close()
    End Sub
    Private Sub createNode(ByVal pID As String, ByVal pName As String, ByVal pPrice As String, ByVal writer As XmlTextWriter)
        writer.WriteStartElement("Product")
        writer.WriteStartElement("Product_id")
        writer.WriteString(pID)
        writer.WriteEndElement()
        writer.WriteStartElement("Product_name")
        writer.WriteString(pName)
        writer.WriteEndElement()
        writer.WriteStartElement("Product_price")
        writer.WriteString(pPrice)
        writer.WriteEndElement()
        writer.WriteEndElement()
    End Sub
End Class

How to XML in VB.NET

XML is a general purpose tag based language and very easy to transfer and store data across applications. Like HTML , XML is a subset of SGML - Standard Generalized Markup Language. XML is a platform independent language, so the information formatted in XML can be used in any other platforms (Operating Systems). XML is a self describing language and it gives the data as well as the rules to identify what information it contains.

XML files are made up of tags that contains data. Generally a start tag and end tag to hold the data. For example, if you want to create an XML tag name "Header" , the start tag is like < Header > and the end tag is like < /Header > . We can fill our information between these tags.

< Header > Header Content Here < /Header >

While creating an XML file , some important points have to remember :

* XML is case sensitive

ex: < Header > is not same as < HeadeR > .

* Tags must be closed in the reverse order that they were opened

ex : < first-tag >< second-tag > Data here < /second-tag > < /first-tag >

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 . These classes are stored in the namespaces like System.Xml, System.Xml.Schema, System.Xml.Serialization, System.Xml.XPath, System.Xml.Xsl etc. The Dataset in ADO.NET uses XML as its internal storage format.

You can use any text editor to create an XML file . More over XML files are readable by humans as well as computers. From the following links you can see how to use XML in VB.NET.