The xml data type cannot be compared or sorted, except when using the IS NULL operator. | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
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
From MSDN (Books OnLine):
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.
The following limitations apply to the xml data type:
- Cannot be used as a subtype of a sql_variant instance
- Does not support casting or converting to either text or ntext.
- Does not support the following column and table constraints:
- PRIMARY KEY/ FOREIGN KEY
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.
- Cannot be compared or sorted. This means an xml data type cannot be used in a GROUP BY statement.