In this post I’ll demo how to export XML files from SQL Server via PowerShell.
It’s pretty much the same method I use in PSBlitz to export execution plans and deadlock graphs.
Why?
SSMS doesn’t really have a way to output XML data into files directly, and doing it manually starts to become a pain after the 3rd or 4th file.
You can do it via SSIS, but not everyone has SSIS installed or the time to figure it out.
Here’s where PowerShell comes in
PowerShell is a great way to automate tasks and, being built on top of the .NET Framework, means it has access to a bunch of nifty .NET Framework classes.
The three .NET Framework classes needed here are XmlDocument, StringWriter, and XmlTextWriter.
These are used in the below function to ingest the XML that’s passed to it, format and properly indent it, and then output it.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | function Format-XML { [CmdletBinding()] Param ( [Parameter( ValueFromPipeline=$true, Mandatory=$true)] [string]$XMLInput ) $XMLDoc = New-Object -TypeName System.Xml.XmlDocument $XMLDoc.LoadXml($XMLInput) $SW = New-Object System.IO.StringWriter $Writer = New-Object System.Xml.XmlTextwriter($SW) $Writer.Formatting = [System.XML.Formatting]::Indented $XMLDoc.WriteContentTo($Writer) $SW.ToString() } |
Note that I didn’t came up with this function, I found it on Stack Overflow.
Exporting the XML files
For this demo, I’ll be using the following query to get the XML data from AdventureWorks2019.
| 1 2 3 | SELECT [JobCandidateID], [Resume] FROM [HumanResources].[JobCandidate]; |
There are a few ways of getting the data from SQL Server and into PowerShell for further handling and outputting.
The following block of code will be used by all 3 examples, the only part that changes is the part marked in the middle by comments.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | #Path where the .xml files will be saved $XMLOutDir = "F:\XMLDir" #The SQL Server Instance $ServerName = "Localhost\VSQL2019" #Database name $DBName = "AdventureWorks2019" #The query that will be executed $Query = @" SELECT [JobCandidateID], [Resume] FROM [HumanResources].[JobCandidate]; "@ #Function to ingest and format the XML data correctly function Format-XML { [CmdletBinding()] Param ( [Parameter( ValueFromPipeline=$true, Mandatory=$true)] [string]$XMLInput ) $XMLDoc = New-Object -TypeName System.Xml.XmlDocument $XMLDoc.LoadXml($XMLInput) $SW = New-Object System.IO.StringWriter $Writer = New-Object System.Xml.XmlTextwriter($SW) $Writer.Formatting = [System.XML.Formatting]::Indented $XMLDoc.WriteContentTo($Writer) $SW.ToString() } #Execute the query and save the output in a DataSet object #Start of the code portion that changes in each example #End of the code portion that changes in each example #Set counter used for row retrieval [int]$RowNum = 0 #Iterate through each row in the data set foreach($row in $XMLTbl){ #Set the filename [string]$XMLFileName = $XMLTbl.Rows[$RowNum]["JobCandidateID"] $XMLFileName = "CandidateID_$XMLFileName.xml" #Write execution plan to file $XMLTbl.Rows[$RowNum]["Resume"] | Format-XML | Set-Content ` -Path $XMLOutDir\$($XMLFileName) -Force #Increment row retrieval counter $RowNum+=1 } |
dbatools
This method uses dbatools’ Invoke-DbaQuery to query SQL Server and retrieve the data.
In this case, the relevant portion of the code would look like this:
| 1 2 3 4 5 | #Execute the query and save the output in a DataSet object #Start of the code portion that changes in each example $XMLTbl = Invoke-DbaQuery -SqlInstance $ServerName -Database ` $DBName -Query $Query -As DataTable #End of the code portion that changes in each example |
I’ve left the surrounding comments in to make it easier to identify where this portion goes in the rest of the code.
Invoke-Sqlcmd
Here, I’m using Invoke-Sqlcmd to achieve the same result.
In this case, the relevant portion of the code would look like this:
| 1 2 3 4 5 | #Execute the query and save the output in a DataSet object #Start of the code portion that changes in each example $XMLTbl = Invoke-Sqlcmd -ServerInstance $ServerName -Database ` $DBName -Query $Query -MaxCharLength 9999999 -As DataTable #End of the code portion that changes in each example |
.NET
This uses plain .NET in case you can’t use any of the above methods.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | #Execute the query and save the output in a DataSet object #Start of the code portion that changes in each example $SqlConnection = New-Object System.Data.SqlClient.SqlConnection #Using trusted connection here, update to user and pass in case of SQL auth $SqlConnection.ConnectionString = "Server=$ServerName;Database=$DBName;trusted_connection=true" $XMLQuery = new-object System.Data.SqlClient.SqlCommand $XMLQuery.CommandText = $Query $XMLQuery.Connection = $SqlConnection $XMLQuery.CommandTimeout = 200 $XMLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $XMLAdapter.SelectCommand = $XMLQuery $XMLSet = new-object System.Data.DataSet $XMLAdapter.Fill($XMLSet) | Out-Null $SqlConnection.Dispose() $XMLTbl = New-Object System.Data.DataTable $XMLTbl = $XMLSet.Tables[0] #End of the code portion that changes in each example |
End result
Regardless which of the above 3 methods you use to get the data from SQL Server, the results are the same.
$XMLTbl contains the data returned by the query.
| 1 | $XMLTbl |
The files are created in the destination directory.
| 1 | gci $XMLOutDir |
And the content of the XML files is formatted correctly.
| 1 | more $XMLOutDir\CandidateID_4.xml |
Conclusion
You can easily export XML files from SQL Server with a little PowerShell magic. And you can do this either by using additional modules or via readily available .NET.
If you’ve liked this, you might also be interested in my post about exporting SQL Server query results to HTML tables.