Friday, July 15, 2011

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.