From MSDN
http://msdn.microsoft.com/en-us/library/ms187107(v=sql.90).aspx[
^]
The following limitations apply to the xml data type:<br />
•Cannot be used as a subtype of a sql_variant instance<br />
•Does not support casting or converting to either text or ntext.<br />
•Does not support the following column and table constraints: <br />
◦PRIMARY KEY/ FOREIGN KEY<br />
◦UNIQUE<br />
◦COLLATE<br />
XML provides its own encoding. Collations apply to string types only. The xml data type is not a string type. However, it does have string representation and allows casting to and from string data types.<br />
◦RULE<br />
<br />
•Cannot be compared or sorted. This means an xml data type cannot be used in a GROUP BY statement.
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.