VBScript 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.

The example below exports data from a table called ‘person’, which contains five columns, ‘id’, ‘firstname’, ‘lastname’, ‘title’ and ‘dob’, from within an SQL Server database, to a XML file in a specified location.

id firstname lastname title dob
1 Bob Smith Mr 1980-01-20
3 Fred Bloggs Mr 1975-05-07
4 Alan White Mr 1989-03-20
5 Fiona Bloggs Mrs 1985-05-19
6 Zoe Davis Miss 1979-07-11
7 Tom Ingram Mr 1971-10-04
8 Karen Thomas Mrs 1969-03-08
9 Samantha Yates Miss 1995-08-27

Firstly, the XML file path and name are set, and a check is made to see if the file path exists. If it does exist, a connection to the database is established and the data is retrieved using an SQL statement. If data has been returned, the XML file is created and the header row is written to the file. The root node is then added to the file, followed by all the data rows in XML format. The ‘chr’ function is used to indent the text, where appropriate, using a tab. Finally, the root node is closed to complete the file.

A rolling seven day backup is also included. This makes a copy of the XML file that has just been created, giving it a name that includes the index number for the day of the week, along with the day itself, for example, 'personexport-2-monday.xml', for the backup on a Monday. Here, Sunday is classed as the first day of the week, with an index value of one. Note that the backup is only done for the first time that this is run in a given day. Backups are then overwritten each week.

Feedback is provided as to the success or failure of the task.

' File system object.
Dim fso
Set fso = WScript.CreateObject("Scripting.FileSystemObject")

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

' Check if the file path exists.
If fso.FolderExists(exportPath) Then

    ' Database connection.
    Dim connection
    Set connection = WScript.CreateObject("ADODB.Connection")
    connection.Open "Provider=SQLOLEDB.1;Data Source=MSSQLSERVERDEMO;" & _
                "Initial Catalog=Demo;UID=DemoUN;PWD=DemoPW"
	
    ' SQL to retrieve data from the person table.
    Dim sqlText
    sqlText = "SELECT id, firstname, lastname, title, dob " & _
              "FROM dbo.person " & _
              "ORDER BY id"
	
    ' Retrieve the data into a record set.
    Dim recordSet
    Set recordSet = connection.Execute(sqlText)
	
    ' Check if any records have been returned.
    If recordSet.EOF Then
	
        ' Message stating no data to export.
        WScript.Echo "There is no data to export."
	
    Else
	
        ' Create the XML file.
        Dim xmlFile
        Set xmlFile = fso.CreateTextFile(exportPath & exportXml, True)
		
        ' Add the header row to the XML file.
        xmlFile.WriteLine("<?xml version=""1.0"" encoding=""utf-8""?>")
        
        ' Open the root element in the XML file.
        xmlFile.WriteLine("<people>")
	
        ' Process the rows of data.
        Do While Not recordSet.EOF
            
            ' Open the person element in the XML file.
            xmlFile.WriteLine(chr(9) & "<person>")
            
            ' Add the id child element to the XML file.
            xmlFile.WriteLine(chr(9) & chr(9) & "<id>" & _
                              Cstr(recordSet("id")) & _
                              "</id>")
            
            ' Add the firstname child element to the XML file.
            xmlFile.WriteLine(chr(9) & chr(9) & "<firstname>" & _
                              recordSet("firstname") & _
                              "</firstname>")
            
            ' Add the lastname child element to the XML file.
            xmlFile.WriteLine(chr(9) & chr(9) & "<lastname>" & _
                              recordSet("lastname") & _
                              "</lastname>")
            
            ' Add the title child element to the XML file.
            xmlFile.WriteLine(chr(9) & chr(9) & "<title>" & _
                              recordSet("title") & _
                              "</title>")
            
            ' Add the dob child element to the XML file.
            xmlFile.WriteLine(chr(9) & chr(9) & "<dob>" & _
                              Cstr(recordSet("dob")) & _
                              "</dob>")
            
            ' Close the person element in the XML file.
            xmlFile.WriteLine(chr(9) & "</person>")
			
            ' Move to the next record.
            recordSet.MoveNext
		
        Loop
        
        ' Close the root element in the XML file.
        xmlFile.WriteLine("</people>")

        ' Close the XML file.
        xmlFile.Close

        ' Today's date.
        Dim today
        today = date()

        ' Construct the backup file name.
        Dim exportBackupXml
        exportBackupXml = Left(exportXml, Instr(exportXml, ".")-1) & "-" & Weekday(today) & _ 
            "-" & LCase(WeekdayName(Weekday(today))) & ".xml"
        
        ' Check if the backup file already exists.
        If fso.FileExists(exportPath & exportBackupXml) Then

            ' Get the backup file.
            Dim backupFile
            Set backupFile = fso.GetFile(exportPath & exportBackupXml)

            ' Check if the last modified date is not the same as today's date.
            If Not(today = CDate(Left(backupFile.DateLastModified, 10))) Then

                ' Copy the XML export.
                fso.CopyFile exportPath & exportXml, exportPath & exportBackupXml, True

            End If

        Else

            ' Copy the XML export.
            fso.CopyFile exportPath & exportXml, exportPath & exportBackupXml, True

        End If

        ' Message confirming successful data export.
        WScript.Echo "Data export successful."
	
    End If
	
    ' Close the record set and database connection.
    recordSet.Close
    connection.Close

Else

    ' Message stating file path does not exist.
    wscript.Echo "File path does not exist."

End If

The XML file produced contains the following data.

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