Querying an XML Column in SQL Server

User the ‘Value’ method on a column in t-sql to query the values of an XML column. So for example, if you have XML that looks like this:

   1: <EventDetails>

   2:   <EventSource>CAST</EventSource>

   3:   <EventType>CastSubjectAreaPublished</EventType>

   4:   <EventTimestamp>2008-12-16T00:42:34</EventTimestamp>

   5:   <EventArguments>

   6:     <SubjectAreaId>1000</SubjectAreaId>

   7:     <ClientId>3450</ClientId>

   8:     <UserId>3</UserId>

   9:     <WorkEventId>6515</WorkEventId>

  10:   </EventArguments>

  11: </EventDetails>

You can query it with T-SQL that looks like this:

   1: select top 20  

   2:     messagebody.value('(/EventDetails/EventType)[1]', 'varchar(50)') as EventType,

   3:      ( select max(SubjectAreaName) from EnterpriseODB..SubjectArea where

   4:         SubjectAreaId = messagebody.value('(/EventDetails/EventArguments/SubjectAreaId)[1]', 'int')) as SubjectArea

   5:     from processedmessagelog 

   6:     where messagebody.value('(/EventDetails/EventArguments/ClientId)[1]', 'varchar(10)') = 3450

Note that the ‘[1]’ tells sql to retrieve the first instance of that value.