Integrating to the max...BizTalk Style

An excuse to post something small, just passed the SQL Exam for database implementation and administration. Next time when I’m the BizTalk Guy and SQL Guy I’m at least certified

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...