StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POQuery optimization in an xml column
text
Body
copied!<p>We have a column which has xml data with a <code>text</code> 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?</p> <p>Suppose the table name is XYZ. Using SQL Server 2005/2008 </p> <pre><code>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)') = 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 </code></pre> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload