What's XQuery? It's a language backed by the W3C to standardize querying XML.

Why XQuery? Microsoft started offering support for it in SQL Server 2000 and in SQL Server 2005, extended it with more support with the introduction of the XML data type column. It's fast, it's compact, and you don't need to add 10 million trivial columns. You just add one XML column and stick raw XML in there. It's that easy, a caveman can do it. Here's more information about Microsoft and XQuery.

So what's the bad news? It's yet another language to learn and the syntax is pretty cryptic for simple operations. For those developers that aren't DBA's, it's likely you won't be touching this very often. So since this came up at work, I'm writing part one of XQuery so I can refer back to it at some point in the future when I know I'll be using it once again.

So I've got a table with an XML data type in one of the columns.

CREATE TABLE [dbo].[User](
    [UserID] [uniqueidentifier] NOT NULL,
    [WebAddressName] varchar(100) NOT NULL,
    [Properties] [xml] NULL
) ON [PRIMARY]

In the Properties XML data type column, I've got a few rows... and the XML looks like this:

  2.iiDhbDmiWSt4UA5CJWRDzg__.3600.1234461600-625164376
  Kenny Lai
  http://kennydust.com/v227/1279/94/q625164376_5201.jpg
  2/12/2009 4:38:47 PM
  12/30/2008 8:27:54 PM

So imagine we've got a couple of thousand records. I just need one record with the DisplayName = "Kenny Lai". How do I grab that row?

declare @Key varchar(50)
set @Key ='Kenny Lai'

SELECT * FROM User
WHERE [properties].exist('/properties/property/text()[contains(.,sql:variable("@Key"))]') = 1

So there you have it, there are other techniques and ways to get more granular with your data, including targeting attributes and whatnot -- but this is the simplest way for the goal that I needed to achieve.

Happy coding.