Errors using UNION in SQL Server

Question

I tried to understand how UNION works in SQL Server by executing the following script:

select * 
from Production.ProductModel
where ProductModelID IN (3,4, 5, 6)
UNION
select * 
from Production.ProductModel
where ProductModelID NOT IN (3,4, 5, 6)

Got following error:

The data type xml cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

It seems few other people got similar and posted in stack overflow; but, unfortunately I didn't understand the explanation by some champs. Can any please help me to understand.

Answer 1

In SQL Server values of some data types cannot be compared to each other. xml is one of that types. Try

SELECT 1
       WHERE convert(xml, '<r/>') = convert(xml, '<r/>');

and you'll get

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

UNION does eliminate duplicates in the end result of the operation and therefore must compare the rows and ultimately the values in the columns for equality.

Now you apparently have an xml column in productmodel and the UNION triggers such a comparison. That comparison however fails, because, as mentioned, xmls cannot be compared.

That's why you see

The data type xml cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

You can use UNION ALL instead of UNION however, as this doesn't eliminate duplicates and therefore doesn't need to compare the xmls.