Insert XML files to SQL Server using PowerShell

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"


Fun fun!

Advertisements

One comment

  • Really good job, thank you !

    I’m trying to do exactly the same thing but by inserting the xml file in a VARBINARY(MAX) field… I can see you create the field BLOBStuff in your table : can you tell us how you do that ?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s