Hi

In our work we have a table with 37000 records (aprox). The table have a xml
column, with primary xml index and a secondary path index.
The xml structure is something like this:


ukel1ele@mail.cl
....
....



I execute the following query to check if an XML value contains the letter
"c" in tbus_Email:

select tbob_idObj
from proOBJ_Object
where
tbob_ObjData.exist('/Usuario/mbUSR_Usuario/tbus_Email[contains(text()[1],"c")]') = 1

this query returns 2099 records in 47 seconds, which is too long for our
time requirements.

I don't know if this will go any faster, but have you tried shredding the XML
column and then querying it, eg

SELECT *
FROM
(
SELECT tbob_ObjData.value('(Usuario/mbUSR_Usuario/tbus_Email/text())[1]',
'VARCHAR(100)' )
FROM proOBJ_Object
) x (email_address)
WHERE email_address Like '%%c%%'

It's not a very selective search you're doing, combined with the use of the
wildcards, I'm not sure indexes are going to help much.

"byteman" wrote:

Bob
Your query is more slow. In fact working with XML is very slow in SQL Server
2005.

In my experience, especially when you excute for example "select xmlcolumn
from MyTable" and the Xml Column is medium size, can will be very slow.

"Bob" wrote:

I tested it out on a sample table, with 40,000 records I'd generated, and it
wasn't slow, with out without clustered index.

"sqlextreme" wrote:

Bob
Maybe your xml is very short, test with some like this.



ukel1ele@mail.cl
Secure
2007-05-07T11:53:38.880

Are you actually trying to search using 'c' in Full-Text Search? By default
the 'c' would be stripped out as a noise word. I assume you're using
something other than 'c' in your FTS predicate?

--

========
Michael Coles
"Pro T-SQL 2008 Programmer's Guide"
http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X

"byteman" discussions.microsoft.com> wrote in message
news:D40F8210-B7FD-4299-A8EE-8AACF6AAADCA@microsoft.com...