SQLChicken.com

SQL Server DBA Tips & Tricks

By

The PowerShell and XML Corollary

Girl Property Surrounded by Geek Objects

I’ve just started watching The Big Bang Theory so I figured I’d borrow a naming convention from their episodes with this blog post. This is a quick post as the problem itself is small and doesn’t have an application (yet).

A co-worker of mine was asked by a higher-up about possibly creating an in-house iPhone application that displays information from an existing SQL Server database. He is currently dabbling in iPhone development and he found that querying SQL Server directly was going to be a bit of a pain so he asked me if we could access the data via other (read also: easier) means such as reading from a data dump file that is in XML format. This limitation comes from the fact that there are no native API’s for Microsoft SQL in Cocoa. As a production DBA seeing anything involving XML gives me the heebie jeebies and I rely on the kindness of strangers, Scarlett O’Hara-style, to help me bridge my ignorance gap. So first thing I needed to find out was what was the easiest way to translate SQL Server data into XML. Now, I’m not completely dense and I know that from SQL Server 2005 and higher there were “a lot of things” put into the product that helped in the XML space but this particular server I am connecting to is SQL 2000 (ewww I know) and I wasn’t sure if it even handled XML the way I needed. I turned on the SQL Bat-signal and asked my Twitter folks to enlighten me on this enigma.

Within a matter of minutes my trusty army of SQL braniacs came to my rescue! The first response came from Buck Woody (Blog | Twitter) who linked me to the fact that SQL Server 2000 does, in fact, have XML features! On the heels of that Nitin Salgar (Twitter) and Argenis Fernandez (Blog | Twitter) also offered up that querying the table and using FOR XML would be a viable solution. Before I could start digging in learning the finer points of querying using XML, Laerte Junior (Blog | Twitter) came out with PowerShell guns a’ blazin! Now, I fully always expect someone to say “oh PowerShell can do that” for just about anything but today my skepticism took a Shoryuken straight to the face. Laerte has been a huge PowerShell advocate and community supporter, and he has helped me in the past so I knew he knows his stuff. Even within the 140 char limit he pretty much sent me an entire working PS script which was very cool. I jumped on Messenger so I could chat with him more about it. Here’s the sample script he gave me:

[sourcecode language="powershell"]
(Invoke-Sqlcmd -ServerInstance $env:servername -Database dbname -Query "SELECT something FROM something" | ConvertTo-XML -NoTypeInformation).save("c:testresult.xml")
[/sourcecode]

One painful lesson I learned right off the bat is that $env: means environment variable so because I was just doing this against one server, and it was not a named instance, I didn’t need to add the $env part to my final script. The rest of the string is pretty standard as far as SQL goes. After they query portion there is a pipe ( | ) which means the results of said query are sent to the next part of the PowerShell script. The result set is passed to a cmdlet called ConvertTo-XML which, well you can figure out what it does. There is one parameter we use -NoTypeInformation which omits the Type attribute from the object nodes. Finally we use the Save method to save our file to a location of our choosing. In one simple line of PowerShell code I get everything I need neatly packaged in to XML. How cool is that?!? If you’d like a more in-depth step through of this Cmdlet check out this article on the ConvertTo-XML Cmdlet at Microsoft’s Script Center.  Big thanks to Laerte again for your help and showing me how powerful and EASY PowerShell can be!

Share

9 Responses to The PowerShell and XML Corollary

  1. Pingback: uberVU - social comments

  2. Thanks for the nice words my friend. I hope this solutions help you. And you can count one me every time.
    Powershell just rocks !!!!

  3. Pingback: Tweets that mention The PowerShell and XML Corollary - The SQL UPDATE Statement -- Topsy.com

  4. I’m wondering when you are going to head out and order the MidnightDBA Powershell shirt

  5. Bruce says:

    Social comments and analytics for this post…

    This post was mentioned on Twitter by onpnt: RT @SQLChicken: [NEW BLOG POST]: The PowerShell and XML Corollary http://bit.ly/cm7jld #powershell…

  6. Freddie Cook says:

    penny is really cute in The Big Bang Theory, great comedy tv series too””

  7. Leo King says:

    penny is just so adorable in the big bang theory.:,

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">