Union in SQL query which create 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 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. "

  • 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

  • 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 xmlns="example">

    <id>1</id>

    <symbol>test10</symbol>

    <id>2</id>

    <symbol>test10</symbol>

    <id>3</id>

    <symbol>test10</symbol>

    <id>4</id>

    <symbol>test11</symbol>

    <id>5</id>

    <symbol>test11</symbol>

    <id>6</id>

    <symbol>test11</symbol>

    </test>

    And here is output which i want to have :

    <test xmlns="example">

    <creation>1</creation>

    <id>1</id>

    <symbol>test10</symbol>

    <id>2</id>

    <symbol>test10</symbol>

    <id>3</id>

    <symbol>test10</symbol>

    <id>4</id>

    <symbol>test11</symbol>

    <id>5</id>

    <symbol>test11</symbol>

    <id>6</id>

    <symbol>test11</symbol>

    </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

    <creation>1</creation>

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

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

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