Home Forums Programming XML Union in SQL query which create XML file RE: Union in SQL query which create XML file

  • 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.