Nonclustered index which includes XML column

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi again,
I have a table which contains an XML column, and I have a stored procedure which returns records based on two columns, e.g.

CREATE TABLE tblTest(
intRecordId int not null,
intUserId int not null,
xmlWhatever XML not null,
datDateProcessed datetime null,
intNumTries int not null default (0)
)

(something like that)

Procedure:

SELECT
intRecordId,
intUserId,
xmlWhatever,
intNumTries
FROM
tblTest
WHERE
datDateProcessed IS NULL
AND
intNumTries < 5


I can't seem to create a good index for this query which avoids a clustered index scan, because I can't add the xml column as an included column in the index, therefore the only way for the query optimiser to do it (without performing a bookmark lookup) seems to just be to scan the whole table. Is there a way to optimise this query? The table will end up having a very large number of rows in it.
.

Relevant Pages

  • RE: Query xml field in Query Plan DMV
    ... You could query the XML column in the DMV as any other XML column in SQL by ... For more detailed information about how to query the XML date using XQuery, ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.xml)
  • XML Column Advice for XML Newbie
    ... done some reading on it and have tried to create what I thought was a simple ... This is a list of multiple recommendations elements, ... I'd like to be able to query for elements that have an attribute value of ... How should I set the xml column attribute "Is XML Document" ...
    (microsoft.public.sqlserver.xml)