Simply query an XML column in SQL Server 2005

Imagine you wanted to just capture a context dump of everything, every time an exception is raised...

Imagine that you did, in fact, create an XML context dump...

Imagine this XML looks a bit like this...

<ContextData>
  <Section Name="Exception Report">
    <Section Name="Request Information">
      <NVP N="Date &amp; Time" V="Friday, July 04, 2008 1:09:26 PM" />
      <NVP N="Url" V="https://url" />
      <NVP N="Query" V="querystring" />
      <NVP N="Current User" V="Kristan Dyson" />
      <NVP N="Browser" V="AppleMAC-Safari" />
      <NVP N="User Agent" V="Mozilla/5.0 (Macintosh; blah)" />
      <NVP N="IP Address" V="" />
      <NVP N="Url Referrer" V="https://url" />
    </Section>

</ContextData> 

Now imagine you stored that in a database table called 'Exception' with the schema...

id    int
ContextData    xml
Date    datetime
Message    nvarchar
GUID    uniqueidentifier

and you wanted to find all exceptions that a particular user had experienced... you can use xpath in the where clause

SELECT  guid,message, ContextData

from Exception 

where date > '20080701'

and ContextData.exist('(/ContextData/Section[@Name="Exception Report"]/Section[@Name="Request Information"]/NVP[@V="username"])') = 1

This queries the XML data and returns rows like

111D0332-7F95-4929-A129-79715974BB38    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

This is just a user support scenario I had to cover.