Extracting XML from SQL Server

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

Getting ready

For this recipe, we will use the table 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 you change the table name in the script.

How to do it...

These are the steps to extract XML from SQL Server:

1. Open the PowerShell console application by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

2. Import the SQLPS module as follows:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking

3. Add the following script and run:

   $VerbosePreference = "Continue"
   $instanceName = "KERRIGAN"
   $databaseName = "SampleDB"
   $foldername = "C:\XML Files\"
   
   #we will save all retrieved files in a new folder
   $newchildfolder = "Retrieved XML $(Get-Date -format 'yyyy-MMM-dd-hhmmtt')"
   $newfolder = Join-Path -Path "$($foldername...