This below source code shows how to transfer data from MS Access database to an XML file. Change the XML file name and your database connection string in below code and run it.
Imports System.Data.OleDb
Imports System.Xml
Public Class Form1
Dim strAcessFilePath As String
Dim strXmlFilePath As String
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
strAcessFilePath = System.Windows.Forms.Application.StartupPath & "\DataReport.mdb"
strXmlFilePath = "C:\CustomerData.xml"
GetDataFromAcessFile()
End Sub
Private Sub GetDataFromAcessFile()
Dim aCon As OleDbConnection
Dim aCmd As OleDbCommand
Dim strCon As String
Dim strQuery As String
'create connection with mdb file
strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strAcessFilePath & ";User ID=Admin;Password="
aCon = New OleDbConnection(strCon)
aCon.Open()
strQuery = "Select * from Customers"
aCmd = New OleDbCommand(strQuery, aCon)
Dim da As New OleDbDataAdapter
da.SelectCommand = aCmd
Dim ds As DataSet
ds = New DataSet("Customers")
da.Fill(ds, "New Customers")
Dim dt As DataTable
dt = New DataTable()
dt = ds.Tables("New Customers")
CreateXMLFile()
WriteToXml(dt)
aCon.Close()
End Sub
Private Sub WriteToXml(ByVal Acessdt As DataTable)
'Write the elements to the file
If Dir(strXmlFilePath).Length = 0 Then
CreateXMLFile()
End If
Dim XmlDoc As New XmlDocument
Dim xNode As XmlNode
Dim xElement As XmlElement
Dim NewxElement As XmlElement
XmlDoc.Load(strXmlFilePath)
If XmlDoc.DocumentElement.HasChildNodes = False Then
Dim inti As Integer
Dim intCol As Integer
'Add the node here
For inti = 1 To Acessdt.Rows.Count - 1
xNode = XmlDoc.CreateNode(XmlNodeType.Element, "customer", Nothing)
xElement = XmlDoc.DocumentElement.AppendChild(xNode)
xNode = Nothing
For intCol = 0 To Acessdt.Columns.Count - 1
xNode = XmlDoc.CreateNode(XmlNodeType.Element, Acessdt.Columns(intCol).Caption, Nothing)
NewxElement = xElement.AppendChild(xNode)
NewxElement.SetAttribute("value", Acessdt.Rows.Item(inti).Item(intCol))
Next intCol
Next
End If
XmlDoc.Save(strXmlFilePath)
End Sub
Private Sub CreateXMLFile()
'Create an XML File
If Dir(strXmlFilePath).Length <> 0 Then
Kill(strXmlFilePath)
End If
Dim xTextWriter As New XmlTextWriter(strXmlFilePath, Nothing)
xTextWriter.WriteStartDocument() 'this line writes the version <?xml version="1.0" ?>
xTextWriter.WriteComment("This file consists of Customers data")
xTextWriter.WriteComment("This data is collected from Access Customers table.")
xTextWriter.WriteStartElement("Customers")
xTextWriter.WriteEndElement()
xTextWriter.Flush()
xTextWriter.Close()
End Sub
End Class