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
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.
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
    • UNIQUE
    • COLLATE
      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.
    • RULE
  • Cannot be compared or sorted. This means an xml data type cannot be used in a GROUP BY statement.

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |