Posts

Showing posts from May, 2010

SQL Server – Searching through an XML Field

In our project we are doing some additional logging like message payload, customer ids etc. The other day i want to provide the request/response times based on the starting point of the business transaction and information in an XML column that was filled when the message was sent. On a lot of websites i could find some examples but they all involved using namespaces etc. I thought i could do that with the lazy-goggles on and hereby the starting point of what is now a monster query ;) SELECT m.id as MessageID, i.createdon MessageReceived, CAST( m.messagesent.query('//*[local-name() = ''Timestamp'']/text()') as varchar) MessageSent FROM instance i, message m WHERE m.parentid = i.id and i.customerref = 'CUSTID_00014041' and CAST (m.messagesent.query('//*[local-name() = ''Action'']/text()') as varchar) = 'SendResponse' Note: the is required for the <xmlcolumn>