Union in SQL while creating XML file

Question

I got some problem with my SQL query which create a XML file. I want to do UNION it this query but it doesn't work.

(SELECT 1 AS "ns0:kindOfItem",
code AS "ns0:wholeCode",
REPLACE(weight, ',', '.') AS "ns0:weight",
1 AS "ns0:ammountOfNumbers",
(SELECT price AS "ns0:value",
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:sendedItems'), TYPE),
(SELECT 
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:present'), TYPE)
FROM [PL].[dbo].[dk_documents] where id in (1,2,3)
FOR XML PATH('test'))

This query works fine but when I try to do UNION like here:

(SELECT 1 AS "ns0:kindOfItem",
code AS "ns0:wholeCode",
REPLACE(weight, ',', '.') AS "ns0:weight",
1 AS "ns0:ammountOfNumbers",
(SELECT price AS "ns0:value",
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:sendedItems'), TYPE),
(SELECT 
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:present'), TYPE)
FROM [PL].[dbo].[dk_documents] where id in (1,2,3)

UNION

(SELECT 1 AS "ns0:kindOfItem",
code AS "ns0:wholeCode",
REPLACE(weight, ',', '.') AS "ns0:weight",
1 AS "ns0:ammountOfNumbers",
(SELECT price AS "ns0:value",
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:sendedItems'), TYPE),
(SELECT 
'EUR' as "ns0:currency"
FOR XML PATH ('ns0:present'), TYPE)
FROM [PL2].[dbo].[dk_documents] where id in (1,2,3)
FOR XML PATH('test'))

This query give me an error:

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

The error message is quite clear, your selects have incompatible data types for at least one column. (1st columns must be compatible all the way, 2nd, 3rd etc as well.) — jarlh, May 25 '16 at 07:21
I kinda know that :) but i had no idea how to fix it, when i do this query without "FOR XML PATH ('ns0:sendedItems'), TYPE)," and "FOR XML PATH ('ns0:present'), TYPE)" it works correctly but then xml schema isnt ok. — Mati, May 25 '16 at 07:24
I'm not sure I completely understand what you are trying to do, but probably `union` data first, and then make xml from it, instead of unioning two xmls. — i-one, May 25 '16 at 07:41

score 4 · Answer 1 · answered May 25 '16 at 08:26

4

Using UNION will remove duplicate values for the result so SQL Server has to compare the XML from the first part with the second part and decide if they are equal and that is not implemented for XML.

You probably don't want the duplicate check so change to UNION ALL instead and it will work fine for XML data as well.

answered May 25 '16 at 08:26

Mikael Eriksson

136,425
22
210
281

Thanks mate :) I'v change it to UNION ALL and it worked correctly – Mati May 25 '16 at 08:31

score 2 · Accepted Answer · edited May 23 '17 at 11:48

You might be interested in this:

Please compare the following

The word "test" occurs in both lists. UNION will do a DISTINCT implicitly, so "test" appears only once.

SELECT * 
FROM (VALUES('this'),('is'),('a'),('test')) AS tbl(Words)
UNION
SELECT * 
FROM (VALUES('and'),('another'),('test')) AS tbl(Words);

The same with UNION ALL will let the "test" appear twice

SELECT * 
FROM (VALUES('this'),('is'),('a'),('test')) AS tbl(Words)
UNION ALL
SELECT * 
FROM (VALUES('and'),('another'),('test')) AS tbl(Words);

You can put your UNION SELECT into a surrounding SELECT (either UNION or UNION ALL and set the FOR XML PATH for the whole result-set

The namespace is created repeatedly, not wrong, but annoying (see this: https://stackoverflow.com/a/35648751/5089204 and the linked Connect-Article)

WITH XMLNAMESPACES(DEFAULT 'Dummy') 
SELECT *
FROM
(
    SELECT * 
    FROM (VALUES('this'),('is'),('a'),('test')) AS tbl(Words)
    UNION
    SELECT * 
    FROM (VALUES('and'),('another'),('test')) AS tbl(Words)
) AS MetaTable
FOR XML Path(''),ROOT('UNION_TEST');

This will bring back both lists, each in its own XML tag, also repeated namespace (see before)

WITH XMLNAMESPACES(DEFAULT 'Dummy') 
SELECT
 (
    SELECT * 
    FROM (VALUES('this'),('is'),('a'),('test')) AS tbl(Words)
    FOR XML PATH(''),ROOT('FirstBlock'),TYPE
 )
,(
    SELECT * 
    FROM (VALUES('and'),('another'),('test')) AS tbl(Words)
    FOR XML PATH(''),ROOT('FirstBlock'),TYPE
 )
FOR XML Path(''),ROOT('UNION_TEST');

And finally you can use this too (either with ALL or not):

WITH XMLNAMESPACES(DEFAULT 'Dummy') 
SELECT * 
FROM (VALUES('this'),('is'),('a'),('test')) AS tbl(Words)
UNION ALL
SELECT * 
FROM (VALUES('and'),('another'),('test')) AS tbl(Words)
FOR XML PATH(''),ROOT('UNION_TEST');

Union in SQL while creating XML file

2 Answers2

Linked