Extracting XML from SQL Server

In this recipe, we will extract the XML content from SQL Server and save each record in individual files in the filesystem.

Getting ready

For this recipe, we will use the table that we created in the previous recipe, Inserting XML into SQL Server, to extract files. Feel free to use your own tables that have XML columns; just ensure that you change the table name in the script.

How to do it...

These are the steps required to extract XML from SQL Server:

1. Open PowerShell ISE as an administrator.
2. Import the SQLPS module as follows:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking

3. Add the following script and run it:

   $VerbosePreference = "Continue"
   $instanceName = "localhost"
   $databaseName = "SampleDB"
   $sourceFolder = "C:\ XML Files\"
   $destinationFolder = "C:\XML Files\"
   
   #we will save all retrieved files in a new folder
   $newFolder = "XML $(Get-Date -format 'yyyy-MMM-dd-hhmmtt')"
   $newfolder...