CODEWITHSUNDEEP
sqlsql-serverxml
Mati
Mati

Reputation: 511

Union in SQL while creating XML file

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.

Upvotes: 4

Views: 3023

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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');

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

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.

Upvotes: 4

Related Questions