Generate SQL script to insert XML from files using Powershell

I have a bunch of (50+) XML files in a directory that I would like to insert into a SQL server 2008 table.

How can I create a SQL script from the command prompt or Powershell that will let me insert the files into a simple table with the following schema:

XMLDataFiles
(
  xmlFileName varchar(255),
  content xml
)

All I开发者_Python百科 need is for something to generate a script with a bunch of insert statements. Right now, I'm contemplating writing a silly little .NET console app to write the SQL script.

Thanks.

Tested on 2005 rather than 2008 but should still work

foreach ($file in get-childitem -path ".\" -filter "*.xml" -name) {

   [string]$content = get-content -path ".\$file"

   $content = $content.Replace("`'", "`"")

   "insert XMLDataFiles (xmlFileName, content) select '$file', '$content'" | Out-File -filepath "insertXML.sql" -append

}

The Powershell escape character is not displaying so make sure to add it in the replace("‘'", "‘"")