Monday, May 31, 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>. The query returns a Xml datatype, if you do not perform a cast you will receive an error.

No comments: