Query optimization in an xml column

We have a column which has xml data with a text datatype. My query has about 10 joins. I would like to optimize the query. Below is the case statement. Is there a better way to query an xml node so that we can increase the performance to that query?

Suppose the table name is XYZ. Using SQL Server 2005/2008

CASE 
   WHEN CONVERT(xml,CAST(XYX as nvarchar(MAX))).value('count(//CurrentStudents)','nvarchar(max)')=1 
      THEN 'All Students'
   WHEN CONVERT(xml,CAST(XYZ as nvarchar(MAX))).value('count(//CurrentStudents/Value)','nvarchar(max)开发者_如何学Go') = 0 AND CONVERT(xml, CAST(XYZ as nvarchar(MAX))).value('(//previousStudent/Value/text())[1]','nvarchar(max)') LIKE '%Studied Before%' OR CONVERT(xml,CAST(XYZ as nvarchar(MAX))).value('(//previousStudent/Value/text())[1]','nvarchar(max)') LIKE '%Studied Before but transferred%' OR CONVERT(xml,CAST(XYZ as nvarchar(MAX))).value('(//previousStudent/Value/text())[1]','nvarchar(max)') LIKE '%Have taken admission but didnt study%' 
      THEN 'Some Students'
   WHEN CONVERT(xml,CAST(XYZ as nvarchar(MAX))).value('count(//CurrentStudents/Value)', 'nvarchar(max)') = 0 AND CONVERT(xml, CAST(XYZ as nvarchar(MAX))).value('count(//CurrentStudents)','nvarchar(max)') = 0 AND CONVERT(xml,CAST(XYZ as nvarchar(MAX))).value('count(//previousStudent)','nvarchar(max)') = 0 
      THEN 'No Students'
   ELSE
      'Bla Bla'
END

And also how can I put a DISTINCT in this query. When I try to it throws an error say xml data cannot be used as distinct.

The error you get from using distinct is not in this case statement. You are returning column that is an XML column.

When you do a count query like this .value('count(//CurrentStudents)','nvarchar(max)') you could use the data type int to avoid an implicit conversion to int .value('count(//CurrentStudents)','int').

It is more efficient to expand the entire path instead of using //. Change //CurrentStudents to '/SomeRoot/itemWhatever/CurrentStudents'. This is doable if all nodes of CurrentStudents is located at the same place in the XML.

You are querying the same value multiple times. That can be avoided by using a cross apply. You can also use cross apply to avoid casting your text column to XML multiple times. Something like this.

declare @T table(XMLCol text)

insert into @T values ('<root><i></i><i></i><i></i></root>')

select R.N
from @T
  cross apply (select cast(cast(XMLCol as varchar(max)) as xml)) as X(Col)
  cross apply (select X.Col.query('count(/root/i)').value('.', 'int')) as R(N)

It would also be better if you could change your table to hold an XML column instead. It will be good for performance and consistency. No risk of having invalid XML in the column.

Is there a better way to query an xml node so that we can increase the performance to that query?

Since your data is stored in a text format, you should use string parsing functions (such as PATINDEX) instead of xml functions to avoid unnecessary conversions. When in Rome...

If you must use xml functions, make a subquery to do the conversion once, and use the converted value many times. DRY...

SELECT sub.myXml.value(....
(
SELECT CONVERT(xml,CAST(XYX as nvarchar(MAX))) myXml
FROM ...
) as sub