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:

(Invoke-Sqlcmd -ServerInstance $env:servername -Database dbname -Query "SELECT something FROM something" | ConvertTo-XML -NoTypeInformation).save("c:\test\result.xml")

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!