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.
