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.

Comments

Post a Comment

Popular posts from this blog

Azure implementation guidelines

In My post ‘ Service Bus Management ’ I pointed out a way of implementing a DTAP strategy for managing the Service Bus environment. There’s now a great post available from Microsoft which goes into more detail about other aspects of managing the Azure subscriptions and other artefacts which is quite useful when setting up your Azure environments. Follow this link to the ‘Azure Implementation Guidelines’ HTH, Sander
Read more

Focus and innovation - recap of the last 2 years

It's been a long time since my last post. Hereby a partial explanation for that, with the note that this blog will remain active for the purpose of being a resource for historical references. My IT career started with programming, and adventures within holland, as well as overseas. When I realised programming is something you can specify, I figured that providing added value is crucial for a long term career and decided to focus on process centric roles. I started with webservice integration and eventually from that to process integration using BizTalk. The great feeling when discussing high level processes and implementing solutions within days, even highly scalable and interfacing with LOB systems is great. After several years of implementing solutions like these, I started questioning if all of this work was justified by the business case, if the solution was actually contributing, if the choices had been valid. In other words, I needed to understand the choices made. When...
Read more

BizTalk Pub/sub vs Topics based routing–discussion

My colleagues and I had a brief discussion about the subject ‘Topics’ and what it brings… Do you all agree/disagree with the following statement: topic-based pub-sub messaging doesn’t really exist in BizTalk “In my opinion; I disagree, as BizTalk offers the following 'out-of-the-box' • Message Routing (f.e build in context properties like message type, trading partner, operation etc) • Content Routing (f.e. promoted properties, message inspection in orchestrations/pipelines icw orchestration logic)” My colleague Rene Brauwers Here my 2 cents…. I agree that it exists...however the current offerings in BizTalk are tightly coupled, making the usage far from perfect. When doing content based routing on custom fields (promoted properties), you will be making the solution message-coupled; Your custom message property schema is deployed, and the message filter on these fields have a direct relationship with this property schema. Making any change to this schema will g...
Read more