Visual Basic and SQL Server – Exporting Data (XML)

Sometimes it can be useful to export data from a database, so that it can be analysed, or, to import in to another computer system. XML or eXtensible Markup Language files are an alternative file format to CSV, that can sometimes be used for both of these scenarios.

Below is an example of how Visual Basic can be used to export data to an XML file called ‘personexport.xml’, from an SQL Server database table called ‘person’, which was used in the examples for selecting, inserting, updating, deleting, importing and exporting data in CSV format.

Firstly, a connection to the database is established, the XML file path and name are set and a check is made to see if the path actually exists. If it does, a query is executed to extract the data from the database and if data is returned, the XML file is opened for writing. The contents of the file is then constructed. Feedback is provided as to the success or failure of the task.

' Database connection variable.
Dim connect = New SqlConnection(
    "Server=localhost\\MSSQLSERVERDEMO; Database=Demo;" &
    "User Id=DemoUN; Password=DemoPW")

Try

    ' Connect to database.
    connect.Open()

Catch ex As Exception

    ' Confirm unsuccessful connection and stop program execution.
    Console.WriteLine("Database connection unsuccessful.")
    End

End Try

' Export path and file.
Dim exportPath As String = "C:\demo\"
Dim exportXml As String = "personexport.xml"

' Check to see if the file path exists.
If Directory.Exists(exportPath) Then

    Try

        ' Query text.
        Dim sqlText As String =
            "SELECT id, firstname, lastname, title, dob " &
            "FROM dbo.person " &
            "ORDER BY id"

        ' Query text incorporated into SQL command.
        Dim sqlSelect As New SqlCommand(sqlText, connect)

        ' Execute SQL and place data in a reader object.
        Dim reader As SqlDataReader = sqlSelect.ExecuteReader()

        ' If data has been returned, do the export.
        If reader.HasRows Then

            ' Create the XML file.
            Using writer = New XmlTextWriter(exportPath + exportXml, System.Text.Encoding.UTF8)

                ' Set the formatting for the document.
                writer.Formatting = Formatting.Indented
                writer.Indentation = 4

                ' Add the declaration for the document.
                writer.WriteStartDocument()

                ' Open the root element.
                writer.WriteStartElement("people")

                ' Process the rows of data.
                While reader.Read()

                    ' Open the person element.
                    writer.WriteStartElement("person")

                    ' Add the person details.
                    writer.WriteElementString("id", reader.GetValue(0).ToString())
                    writer.WriteElementString("firstname", reader.GetValue(1).ToString())
                    writer.WriteElementString("lastname", reader.GetValue(2).ToString())
                    writer.WriteElementString("title", reader.GetValue(3).ToString())
                    writer.WriteElementString("dob", reader.GetValue(4).ToString())

                    ' Close the person element.
                    writer.WriteEndElement()

                End While

                ' Close the root element.
                writer.WriteEndElement()

                ' Close the XML document.
                writer.WriteEndDocument()

                ' Flush the internal buffer.
                writer.Flush()

            End Using

        Else

            ' Message stating no data to export.
            Console.WriteLine("There is no data to export.")
            End

        End If

        ' Message stating export successful.
        Console.WriteLine("Data export successful.")

    Catch ex As Exception

        ' Message stating export unsuccessful.
        Console.WriteLine("Data export unsuccessful.")
        End

    Finally

        ' Close the database connection.
        connect.Close()

    End Try

Else

    ' Display a message stating file path does not exist.
    Console.WriteLine("File path does not exist.")

End If

The XML file produced contains the following data. Note that, although dates in an SQL Server database are stored in the format YYYY-MM-DD (four digit year, two digit month and two digit day), they are output to XML in a day, month, year and time format.

<?xml version="1.0" encoding="utf-8"?>
<people>
    <person>
        <id>1</id>
        <firstname>Bob</firstname>
        <lastname>Smith</lastname>
        <title>Mr</title>
        <dob>20/01/1980 00:00:00</dob>
    </person>
    <person>
        <id>3</id>
        <firstname>Fred</firstname>
        <lastname>Bloggs</lastname>
        <title>Mr</title>
        <dob>07/05/1975 00:00:00</dob>
    </person>
    <person>
        <id>4</id>
        <firstname>Alan</firstname>
        <lastname>White</lastname>
        <title>Mr</title>
        <dob>20/03/1989 00:00:00</dob>
    </person>
    <person>
        <id>5</id>
        <firstname>Fiona</firstname>
        <lastname>Bloggs</lastname>
        <title>Mrs</title>
        <dob>19/05/1985 00:00:00</dob>
    </person>
    <person>
        <id>6</id>
        <firstname>Zoe</firstname>
        <lastname>Davis</lastname>
        <title>Miss</title>
        <dob>11/07/1979 00:00:00</dob>
    </person>
    <person>
        <id>7</id>
        <firstname>Tom</firstname>
        <lastname>Ingram</lastname>
        <title>Mr</title>
        <dob>04/10/1971 00:00:00</dob>
    </person>
    <person>
        <id>8</id>
        <firstname>Karen</firstname>
        <lastname>Thomas</lastname>
        <title>Mrs</title>
        <dob>08/03/1969 00:00:00</dob>
    </person>
    <person>
        <id>9</id>
        <firstname>Samantha</firstname>
        <lastname>Yates</lastname>
        <title>Miss</title>
        <dob>27/08/1995 00:00:00</dob>
    </person>
</people>