The xml data type cannot be compared or sorted, except when using the IS NULL operator.

Hi,

I have a 3 tables and get the values using RIGHT OUTER JOIN for my requirements.

I can get the correct value as what i need. But values are coming with duplication. So avoid duplication i will put group by for two columns that datatype is xml. I got the below error when i select the query. I already saw the solution in this forum. But it is not working for me. I got the same problem. Hope you understand my problem.

Msg 305, Level 16, State 1, Line 2
The xml data type cannot be compared or sorted, except when using the IS NULL operator.

Regards
Guvera

From MSDN (Books OnLine):

http://msdn.microsoft.com/en-us/library/ms187107(v=sql.90).aspx

You need to convert to another data type and then perform the operation.
But generally xml data type is quite large. Using them in Group BY, CONVERT etc tend to take more memory. Do you really want to have the column in the select statement.
You can perform a distinct select on the other columns, and find the primary key and then connect to this table to get the xml column.
Please check whether that option performs better.