Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.

A while ago I blogged about using xp_cmdshell to execute a PowerShell script in SQL Server and return a result set. At that time I was using PowerShell V1, but now with PowerShell V2 I can clean this up a little. The improved version uses the built-in PowerShell V2 cmdlet ConvertTo-XML with the –AsString parameter. Because SQL Server understands XML we can parse the XML using XQuery. Keep in mind this is still hacky and just as I mentioned last time its far better to execute T-SQL in PowerShell rather than use or more accurately misuse xp_cmdshell. Anyways here’s a improved version: