Dennis Burton's Develop Using .NET

Change is optional. Survival is not required.

What do you do when the limitations of SQL Management Studio will not show all of your results?

Our application works with an XML file defined by an external industry specification. We found that a significant portion of this file is not required by our application. So, when loading this file into our system, we remove the portions that we do not care about and stuff the rest into a database. What is left can still be a pretty good sized XML string. Common usage shows the reduced size to still be around 300-500 KB.

Recently, we needed to investigate the contents of one of these modified files as well as the history of what was loaded. Using the standard developer playbook, the first response is to quickly fire up SQL Management Studio and craft a query to get the ntext column. At that point, the 65K limit on a column in Management Studio yields about a quarter of the file.

It would be pretty easy to pull up Visual Studio and put together a quick utility to extract the XML into a file. As a developer, I would likely keep this project around for an excessively long period of time. After all, it is now part of my code toolbox. So, rather than squirrel away a project file, source file, and all the related baggage of a VS project, I chose PowerShell as the hammer for this nail. The C# code that would have been created in Visual Studio, in this case, is simply a language that is accessing the functionality in the .net framework. Since PowerShell has access to the .net framework, it is very similar code simply using a different language. Additionally, the PowerShell script is a single file without the weight of a VS project. The only thing that I keep in my toolbox is the code required to complete the task.

What is required is the pretty common code of opening a connection, executing a query, and using the results. This is how this common C# task is represented in PowerShell. We are pulling back several rows that contain this large XML field and tagging the resuts by upload date.

param( $theId, $server,$db )

$connection = New-Object System.Data.SqlClient.SqlConnection("data source=$server;initial catalog=$db;Integrated Security=SSPI")
$command = New-Object System.Data.SqlClient.SqlCommand("select * from MyTable where theItemId=$theId", $connection)


$reader = $command.ExecuteReader()
while( $reader.Read()  ) {
    $fileName = "Item_{0}_DateLoaded_{1:yyyy_MM_dd_HH_mm_ss}.xml" -f $theId,$reader['dStart']
    Out-File  -FilePath $fileName -InputObject $reader['reallyBigXMLText']


Save that text into a .ps1 file and that is all that is necessary to extract the large text field. I am not one that has cut over from the classic command line to PowerShell. At the same time, it is important to know when this tool can be helpful.

Please login with either your OpenID above, or your details below.
(will show your gravatar icon)
Home page

Comment (Some html is allowed: a@href@title, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

Enter the code shown (prevents robots):

Live Comment Preview

Dennis Burton

View Dennis Burton's profile on LinkedIn
Follow me on twitter
Rate my presentations
Google Code repository

Community Events

Windows Azure Boot Camp Lansing GiveCamp