VB.Net/Database ADO.net/Database to XML

Create and configure a new command that includes the FOR XML AUTO clause

<source lang="vbnet"> Imports System Imports System.Xml Imports System.Data Imports System.Data.SqlClient

   Public Class MainmClass
       Public Shared Sub DisconnectedExample()
           Dim doc As New XDocument
           Using con As New SqlConnection
               con.ConnectionString = "Data Source=.\sqlexpress;Database=AdventureWorks;Integrated Security=SSPI;"
               Using com As SqlCommand = con.CreateCommand
                   com.rumandType = CommandType.Text
                   com.rumandText = "SELECT DepartmentID, [Name], GroupName FROM HumanResources.Department FOR XML AUTO;"
                   con.Open()
                   Using reader As XmlReader = com.ExecuteXmlReader
                       Dim root As XElement = <Results></Results>
                       While reader.Read
                           If reader.NodeType = XmlNodeType.Element Then
                               Dim newChild As XNode = XElement.ReadFrom(reader)
                               root.Add(newChild)
                           End If
                       End While
                       doc.Add(root)
                   End Using
                   con.Close()
               End Using
           End Using
           Console.WriteLine(doc.ToString)
       End Sub
       Public Shared Sub Main()
           DisconnectedExample()
       End Sub
   End Class
  
   
 </source>

Output data in database to XML directly

<source lang="vbnet"> Imports System Imports System.Collections Imports System.Data Imports System.IO Imports System.Xml.Serialization Imports System.Xml Imports System.Windows.Forms Imports System.Data.SqlClient

Public Class MainClass

   Shared Dim WithEvents con As SqlConnection
   Shared Sub Main()
       con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI") 
       Dim cmd As New SqlCommand()
       cmd.rumandText = "SELECT * FROM Employee FOR XML AUTO, XMLDATA"
       cmd.Connection = con
       Try
           con.Open()
           Dim reader As XmlReader = cmd.ExecuteXmlReader()
           While reader.Read()
               Console.WriteLine(reader.ReadOuterXml())
           End While
           reader.Close()
       Finally
           con.Close()
       End Try
   End Sub

End Class

 </source>

Save data from database table directly to XML document

<source lang="vbnet"> Imports System Imports System.Xml Imports System.Xml.Schema Imports System.IO Imports System.Data.SqlClient Imports System.Collections Imports System.Data

Public Class MainClass

   Shared Sub Main()
       Dim da As New SqlDataAdapter( _
                  "SELECT ID, FirstName, LastName FROM Employee", _
                  "Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI")
       Dim ds As New DataSet("Employee")
       da.FillSchema(ds, SchemaType.Source)
       ds.ReadXml("Employee.xml")
       Dim Table As DataTable = ds.Tables(0)
       Dim numCols As Integer = Table.Columns.Count
       Dim Row As DataRow
       For Each Row In Table.Rows
           Dim i As Integer
           For i = 0 To numCols - 1
               Console.WriteLine(Table.Columns(i).ColumnName & " = " & Row(i))
           Next
           Console.WriteLine()
       Next
   End Sub

End Class

 </source>

Save result from "select" command directly to a XML file

<source lang="vbnet"> Imports System Imports System.Data Imports System.Data.SqlClient

public class MainClass

  Shared Sub Main()
     Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
         "integrated security=sspi;database=MyDatabase")
     " Sql Query 
     Dim sql As String = _
        "SELECT * FROM Employee"
     Try
        " Create Data Adapter
        Dim da As New SqlDataAdapter
        da.SelectCommand = New SqlCommand(sql, thisConnection)
        " Create and fill Dataset
        Dim ds As New DataSet
        da.Fill(ds, "Employee")
        " Extract DataSet to XML file
        ds.WriteXml("Employee.xml")
     Catch ex As SqlException
        " Display error
        Console.WriteLine("Error: " & ex.ToString())
     Finally
        " Close Connection
        thisConnection.Close()
        Console.WriteLine("Connection Closed")
     End Try
  End Sub

End Class

 </source>

Serializing DataSet into an XML DOM

<source lang="vbnet"> Imports System Imports System.Xml Imports System.Xml.Schema Imports System.IO Imports System.Data.OleDb Imports System.Data.rumon Imports System.Data.SqlClient Imports System.Data Imports System.Runtime.Serialization.Formatters Imports System.Runtime.Serialization.Formatters.Soap Imports System.Runtime.Serialization.Formatters.Binary Imports System.Xml.Serialization

Public Class MainClass

   Shared Sub Main()
       Dim Connection As SqlConnection = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI")
     Dim MyDA As SqlDataAdapter = New SqlDataAdapter( _
        "SELECT * FROM Employee", Connection)
     Dim MyDS As DataSet = New DataSet()

     Console.WriteLine("Serializing DataSet into an XML DOM...")
     Dim xStream As Stream = File.Open("MyDS.xml", _
        FileMode.Create, FileAccess.ReadWrite)
     Dim xs As XmlSerializer = New XmlSerializer(GetType(DataSet))
     xs.Serialize(xStream, MyDS)
     xStream.Close()
   End Sub

End Class

 </source>