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

UK Connected Systems User Group – BizTalk Services questions

This Wednesday I’m attending the CSUG – The Hybrid Organisation (sold out!). During my evaluation of several Integration products (BizTalk / BizTalk Services / Neuron ESB / MuleESB) I had some concerns/questions on BizTalk Services. My goal is to get answers on the following scenario a) I receive a HTTP Post with an Xml Body payload b) This contains a signature which i need to validate c) Transform the Xml body payload to another Xml message d) Perform a HTTP Post with the new Xml message to an external HTTP url and sign the message e) Route the response back, similar to the aforementioned steps, where an error needs to be mapped to a dedicated error response Challenges I'm wondering how to cope with some of the following challenges in this scenario, and would like to know resource information available which addresses some of these challenges; Validating / Signing of messages A requirement is to validate the incoming message by inspecting an signature in the message
Read more

Setting up a build server with the BizTalk Deployment Framework

Image
This chapter describes the steps to a TFS Build Server to set up BizTalk deployment with the following functionality: • Build solution • Perform unit testing Using BizUnit • Create Deployment Packages Using BDTF • Deploy to Server Using R packages created in previous step • Create documentation BizTalkDocumenter For this the following steps are required: • Installation Components (30 min) • Configuring a build account (30 min) • Setting up the build service (30 min) • Scrum template installation (30 min) • Share for the build output directory (10 min) • Adjustments Build Template for use BTDF (30 min) • Design of build definitions (60 min) • Setting BTDF (60 min) • Use BTDF • Build execution / scheduling UPDATE (21-06-2013) - When using sharedassemblies, the following URL is a big help http://mohamedradwan.wordpress.com/2013/01/31/set-custom-assemblies-path-for-host-build-service-on-the-cloud/ - For viewing the basic instructions a video is available on the following blog http:
Read more