What do T-SQL, PowerShell and OpenRowSet have in common? A unique method for querying Visual Studio solutions

SSIS Junkie - Querying Visual Studio project files using T-SQL and Powershell

“Earlier today I had a need to get some information out of a Visual Studio project file and in this blog post I’m going to share a couple of ways of going about that because I’m pretty sure I won’t be the only person that ever wants to do this. The specific problem I was trying to solve was finding out how many objects in my database project (i.e. in my .dbproj file) had any warnings suppressed but the techniques discussed below will work pretty well for any Visual Studio project file because every such file is simply an XML document, hence it can be queried by anything that can query XML documents.

Ever heard the phrase “when all you’ve got is hammer everything looks like a nail”? Well that’s me with querying stuff – if I can write SQL then I’m writing SQL. Here’s a little noddy database project I put together for demo purposes:

…

With a lot of help from folks on the SQL Server XML forum  I came up with the following query that nailed what I was after. It reads the contents of the .dbproj file into a variable of type XML and then shreds it using T-SQL’s XML data type methods:

And here’s the output:

…

…”

I’m not really sure what to say about this. When I read it on Monday, I laughed and thought, “what an ‘interesting’ use of T-SQL” But then I looked in the mirror...

Raise your hand if you’ve ever fired up Query Analyzer and executed ‘Select ### + ###’ just to add a couple numbers together? [Hand raised]. Who dreams in TSQL? [Hand Raised]… You has their SQL Server icons at the front of their taskbar? [Hand raised]… As the author said, when you just need to get stuff done, you use the tools you have in hand.

In any case,  sometimes you need to look at solving problems differently and I thought this post a great example of that (and one I would have never thought of…)