Insert XML files to SQL Server using PowerShell

Written by belle
Posted March 10, 2012 at 3:01 pm

One way to bulk upload your XML files into SQL Server is by using PowerShell. In the script below I am using SQL Server 2012 and PowerShell V3 (CTP).

Note that you can use the script in SQL Server 2005/2008/R2 and PowerShell V2, with some slight changes in syntax.

I use Invoke-Sqlcmd to get this done.
First up, I have a prep T-SQL script file that I use to create my tables:

$instanceName = "KERRIGANSQL01"
$databaseName = "SQLSaturday114"
$prepfile = "C:presentationsSQLSaturday114PowerShellStuff.Table.sql"
Invoke-Sqlcmd -ServerInstance $instanceName `
-Database $databaseName -InputFile $prepfile

Nothing fancy about this table. Just a basic table with an XML field:

IF OBJECT_ID('PowerShellStuff') IS NOT NULL
DROP TABLE PowerShellStuff
GO
 
CREATE TABLE PowerShellStuff
(ID INT IDENTITY(1,1) NOT NULL,
 FileName VARCHAR(200),
 InsertedDate DATETIME DEFAULT GETDATE(),
 InsertedBy   VARCHAR(100) DEFAULT SUSER_SNAME(),
 XMLStuff XML,
 BLOBStuff VARBINARY(MAX)
)

To loop through all XML files.

We can use the Get-ChildItem cmdlet. We can filter the files based on the extension.

$xmlfiles = Get-ChildItem "C:presentationsSQLSaturday114xml" `
-Filter "*.xml"

For each file that we find, we can extract the contents and insert into SQL Server using Invoke-Sqlcmd.

 
#remove some illegal characters
#note this is just a basic cleanup, you may need to do a bit more
[string]$xml = (Get-Content $xmlfile.FullName) -replace "'", "''"
 
#note we're using HERETO string
$query = @"
INSERT INTO PowerShellStuff(FileName,XMLStuff) 
VALUES('$xmlfile','$xml')
"@
 
#insert into SQL Server
Invoke-Sqlcmd -ServerInstance $instanceName -Database $databaseName -Query $query

When you go back to query your table, you should see all the XML files inserted into the XML columns

This is the whole script for your reference:

 
#create our test table
$prepfile = "C:presentationsSQLSaturday114PowerShellStuff.Table.sql"
Invoke-Sqlcmd -ServerInstance $instanceName ` 
-Database $databaseName -InputFile $prepfile
 
#get all XML files
$xmlfiles = Get-ChildItem "C:presentationsSQLSaturday114xml" `
-Filter "*.xml"
 
#upload each file to SQL Server
foreach($xmlfile in $xmlfiles)
{
    #need to replace illegal characters
    Write-Host "Importing " $xmlfile.FullName "..."
    [string]$xml = (Get-Content $xmlfile.FullName) -replace "'", "''"
 
#note we're using HERETO string
$query = @"
INSERT INTO PowerShellStuff(FileName,XMLStuff) 
VALUES('$xmlfile','$xml')
"@
 
    #insert into SQL Server
    Invoke-Sqlcmd -ServerInstance $instanceName -Database $databaseName -Query $query
}
 
#display our results
#how many records have been inserted?
$query = @"
SELECT COUNT(*) AS num
FROM PowerShellStuff 
"@
 
$result = Invoke-Sqlcmd -ServerInstance $instanceName -Database $databaseName -Query $query
Write-Host "Inserted " $result.num " records in the table"