Union in SQL query which create XML file

Question

Union in SQL query which create XML file

Pro100

Right there with Babe

Points: 769
More actions
May 25, 2016 at 1:20 am

#400200

I got some problem with my SQL query which create a XML file, i want to do UNION it this query but it doesnt 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. "

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Russel Loski SSCertifiable Points: 7059 More actions · Answer 1

You are looking at your problem backwards. You don't want to have a union of two rows of XML. You want xml of a union of two select statement.

So to fix your problem you need to start with a UNION ALL (don't use UNION) of your select statements. That you would wrap in FOR XML PATH.

Russel Loski, MCSE Business Intelligence, Data Platform

Pro100 Right there with Babe Points: 769 More actions · Answer 2

I forgot to post it here but iv already change it to UNION ALL and it work correctly :).

At this moment im facing another problem, here is example :

WITH XMLNAMESPACES(DEFAULT 'example')

SELECT id, symbol from table1

WHERE id in (1,2,3)

UNION ALL

SELECT id, nrdok from table2

WHERE id in (4,5,6)

FOR XML PATH(''),root('test');

Which give me output :

</test>

And here is output which i want to have :

</test>

So i write this :

WITH XMLNAMESPACES(DEFAULT 'example')

SELECT 1,(

SELECT id, symbol from table1

WHERE id in (1,2,3)

UNION ALL

SELECT id, nrdok from table2

WHERE id in (4,5,6)

FOR XML PATH(''),TYPE)

FOR XML PATH('test')

But it gives me an error

"The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it."

This

Should be in output only at the begining and then the both selects form union statement.