1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vijayrushi
  • 41
  • 3
  • Possible duplicate of [Union in SQL while creating XML file](https://stackoverflow.com/questions/37430288/union-in-sql-while-creating-xml-file) – J Weezy Aug 24 '18 at 20:43
  • 1
    Switch to use UNION ALL and it won't check for duplicates. Then your query should work without error. – Sean Lange Aug 24 '18 at 20:43

2 Answers2

4

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.

sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • This is a well-written answer, but I would take it to the next step and ask if they need the xml column in the result set. Because, as you've implied, they can either eliminate the duplicate rows OR remove the column (but not both) using this method. – Stuart Ainsworth Aug 24 '18 at 21:11
  • Thank you Champ for your explanation... also grateful for all the champs towards contributing their valuable time to 'stack overflow' – Vijayrushi Aug 25 '18 at 13:07
2

When using UNION or UNION ALL, I always specify the column names.

  1. UNION: Sorts and removes duplicate rows
  2. UNION ALL: Does not sort and does not remove duplicate rows.
J Weezy
  • 3,507
  • 3
  • 32
  • 88