Wednesday, 9 July 2008

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.

Friday, 4 July 2008

Note to Self

In LINQ to SQL, If SubmitChanges() on the DataContext doesn't do anything having just updated a row, remember to ensure the Primary key is set on the table in the schema.