Powershell to write bunch of data from sql to the file system


I recently had need to write a bunch of xml we had stored in sql out to a bunch of files. So we have one xml file per row, and I wanted to out put these into a folder, each one in it’s own file. After some Googling I got this working;

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Data Source=localhost;Initial Catalog=testDB;User Id=sa;Password=thepassword;Persist Security Info=True”
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = “select top 500 id, [xml] from xmlLog order by id desc”
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
for ($i=0;$i -le $DataSet.Tables[0].Rows.Count – 1;$i++) { $DataSet.Tables[0].Rows[$i][1] >> (“c:\xml\file” + $i + “.xml”) }

This gave me a folder called xml in my c:\ containing 500 xml files!

  1. No comments yet.
(will not be published)