2005 : XML Indexes

Find tables with XML Indexes

SELECT  s.name as SchemaName,  t.name as TableName, xi.name as XMLIndexName
FROM sys.internal_tables AS it
JOIN sys.tables AS t
  ON it.parent_id = t.object_id
JOIN sys.schemas as s
on t.schema_id = s.schema_id
JOIN sys.xml_indexes AS xi
  ON it.parent_id = xi.object_id
  AND it.parent_minor_id  = xi.index_id

Finding disabled XML Indexes

SELECT  *
FROM    sys.xml_indexes
WHERE   is_disabled != 0;

Disabling an XML Index

ALTER INDEX indexname ON tablename DISABLE