Home » Export XML files from SQL Server using PowerShell

Export XML files from SQL Server using PowerShell

by Vlad Drumea
0 comment

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.

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.


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.

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:

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:

.NET

This uses plain .NET in case you can’t use any of the above methods.

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.


The files are created in the destination directory.


And the content of the XML files is formatted correctly.


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.

You may also like

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

This site uses Akismet to reduce spam. Learn how your comment data is processed.