Query XML in SQL Server

-----------------------returns nodes-----------------------

select top 1 
   EventXML.query('/residential/agentID'),EventXML.query
   ('/residential/listingAgent/name'), * from Event (nolock)
   where RowStateTypeId =1 
    and EventTypeID in (1,5) and listingId= 654 
   order by created desc

-----------------------returns data in the nodes-----------------------

select top 1 
   EventXML.value('data((//agentID)[1])','nvarchar(max)') as agentId
   ,EventXML.value('data((//listingAgent/name)[1])','nvarchar(max)') as agentName
   , * 
   from Event (nolock)
    where RowStateTypeId =1 and EventTypeID in (1,5) 
     and listingId= 329
    order by created desc

---

You can also extract attributes and index the xml type based on an XSD schema.

Helpful links:

1. XML data type in SLQ Server 2005
Learning XQeury/XPath