A quicker way of performing this XML query?

Hi all

I have the following query that makes up part of a table-value-function i've written in SQL 2K5.

XML Query

SELECT CONVERT(XML, objectdata).value('(/xmlData/IsTrue)[1]', 'bit') as IsTrue
from myTable
where idfield = [@.FunctionTable].ID

Until adding this query, the function ran in under 60 seconds. Adding this query has added an extra 120 seconds to the function execution time.

This query is called around 200 times in the function as part of an update:

UPDATE @.FunctionTable set ...

....

, FieldValue = (SELECT CONVERT(XML, objectdata).value('(/xmlData/IsTrue)[1]', 'bit') as IsTrue
from myTable
where idfield = [@.FunctionTable].ID)

Is there a more performant way to do the same XML lookup?

Many Thanks

Are there any XML indexes created?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Probabaly not, would this index be placed upon the source field?
|||

You can create an index on the field where you do the XQuery.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com