PowerShell 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 an 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, variables are defined to facilitate the connection and authentication to the database. This is followed by a query string to extract, or select, the data from the ‘person’ table, as well as variables for the XML file path and name. If the file path exists, the ‘Invoke-Sqlcmd’ cmdlet is used to extract the data. If data has been returned, a new XML file is created and its contents is constructed.

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-1-monday.xml', for the backup on a Monday. Here, Sunday is classed as the first day of the week, with an index value of zero. 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.

# Clear the console window.
Clear-Host

# Database variables.
$server = "MSSQLSERVERDEMO"
$database = "Demo"
$username = "DemoUN"
$password = "DemoPW"

# SQL query to select all data in the person table.
$query = "SELECT * FROM [dbo].[person] ORDER BY [id]"

# Export path and file.
$exportPath = "C:\Demo\"
$exportXml = "personexport.xml"

# Check to see if the file path exists.
if (Test-Path $exportPath)
{

    try
    {

        # Extract the data.
        $people = Invoke-Sqlcmd -ServerInstance $server -Database $database `
        -Username $username -Password $password -Query $query -ErrorAction Stop

        # If data has been returned, do the export.
        if ($people.Count -gt 0)
        {

            # Construct the full file path.
            $fullFilePath = $exportPath + $exportXml

            # Create the XML file.
            $xmlWriter = New-Object System.XMl.XmlTextWriter($fullFilePath,$Null)

            # Set the formatting for the document.
            $xmlWriter.Formatting = 'Indented'
            $xmlWriter.Indentation = 1
            $XmlWriter.IndentChar = "`t"

            # Add the declaration for the document.
            $xmlWriter.WriteStartDocument()

            # Add the root element.
            $xmlWriter.WriteStartElement('people')

            # Process the rows of data.
            foreach ($person in $people)
            {

                # Open the person element.
                $xmlWriter.WriteStartElement('person')

                # Add the person details.
                $xmlWriter.WriteElementString('id', $person.id)
                $xmlWriter.WriteElementString('firstname', $person.firstname)
                $xmlWriter.WriteElementString('lastname', $person.lastname)
                $xmlWriter.WriteElementString('title', $person.title)
                $xmlWriter.WriteElementString('dob', $person.dob)

                # Close the person element.
                $xmlWriter.WriteEndElement()

            }

            # Close the root XML element.
            $xmlWriter.WriteEndDocument()

            # Flush the internal buffer.
            $xmlWriter.Flush()

            # Close the XML document.
            $xmlWriter.Close()

            # Today's date.
            $today = Get-Date

            # Construct the backup file name.
            $exportBackupXml = $exportXml.Substring(0, $exportXml.Length-4) + "-" + `
                               [int]$today.DayOfWeek + "-" + `
                               $today.DayOfWeek.ToString().ToLower() + ".xml"

            # Check if the backup file does not exist, or if it does, check that
            # today's date is different from the last modified date.
            if (-not (Test-Path ($exportPath + $exportBackupXml)) -or `
               ((Test-Path ($exportPath + $exportBackupXml)) -and `
                ((Get-Item ($exportPath + $exportBackupXml)).LastWriteTime.date -ne $today.Date)))
            {

                # Copy the XML export.
                Copy-Item ($exportPath + $exportXml) `
                -Destination ($exportPath + $exportBackupXml) -Force

            }

            # Message stating export successful.
            Write-Host "Data export successful."

        }
        else
        {

            # Message stating no data to export.
            Write-Host "There is no data to export."
          
        }

    }
    catch
    {

        # Message stating export unsuccessful.
        Write-Host "Data export unsuccessful."

    }

}
else
{

    # Message stating file path does not exist.
    Write-Host "File path does not exist."

}

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