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

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

  
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

Output data in database to XML directly

 
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

Save data from database table directly to XML document

 
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

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

 
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

Serializing DataSet into an XML DOM

 
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