Thanks! Chris will try this out and test the performance.

Thank you so much Thom. Your solution works...

Here CMDTY and SRC_Name are just an example. And it can vary in length.

For instance for SRC_Name i need to retrieve the value after first occurance of '/'...

Hi,

I modified the query to use SQL count function instead of xml count as suggested by drew .With this change i got 25~30 percent improvement in comparison to existing code.

I...

Hi Igor,

One quick query, how do i add index on xml column and on which node using sql server 2005. If you take the above xml for instance.

Thanks,

sam

Hi Igor,

Here is the execution plan for the query that is having performance issue (Attached).

There are lot of tables used in that so not possible to send all the details...

Im getting the below error when trying to use above code

Msg 4104, Level 16, State 1, Line 47

The multi-part identifier "c.query" could not be bound.

Am i missing something over here.

Here...

XML is stored in a table in a different database. Will try your approach thank you so much.

After running server side trace I was able to detect the SQL:statement that was taking too much time to execute and due to time out error was happening.

Next I need...

Thanks Gail for your suggestion.

Now if i had to ascertain the exact reason as to why the query takes so much time to complete the execution only for specific time...

Hi,

I executed the Stored procedure with same set of parameters that is passed from client application in SQl Management studio and it executed in few seconds. I didn't get any...

Hi John,

There are no remote server here. The SP refers to a table that is present in another database on the same server.

While running trace on the server I did...

Hi Eric,

Your solution will work however I have one query . Can I handle lock/deadlock issue with this approach. For instance if in any of the sub-procedures there is a...