SQL UNION FOR XML name output column
Asked Answered
B

1

20

I'm trying to generate an XML output from SQL and need to use a UNION statement and also name the output column.

I had this working before when I didn't need to use a UNION statement using:

select(
SELECT

    [CompanyName],
    [Address1],
    [Address2],
    [Address3],
    [Town],
    [County],
    [Postcode],
    [Tel],
    [Fax],
    [Email],
    [LocMap]

FROM [UserAccs] FOR XML PATH ('AccountDetails'), root ('Root') 
) as XmlOutput

Which named the output XML column as XmlOutput

I am now trying:

select(
SELECT

    [CompanyName],
    [Address1],
    [Address2],
    [Address3],
    [Town],
    [County],
    [Postcode],
    [Tel],
    [Fax],
    [Email],
    [LocMap]

FROM [UserAccs]

UNION

SELECT

    [CompanyName],
    [Address1],
    [Address2],
    [Address3],
    [Town],
    [County],
    [Postcode],
    [Tel],
    [Fax],
    [Email],
    [LocMap]

FROM [UserAppAccs]



 FOR XML PATH ('AccountDetails'), root ('Root')
) as XmlOutput

But receive an error message, does anyone know a way around this?

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.

Thanks J.

Bereave answered 16/2, 2012 at 12:22 Comment(1)
What does the error message say?Vietnam
P
37

Wrap your 2 selects on a single one like so:

select (
    select id, name from (
        select id, name 
        from xmltest 
        UNION
        select id, name 
        from xmltest 
    ) A
    FOR XML PATH ('AccountDetails'), root ('Root')
) As XmlOutput
Phosphene answered 16/2, 2012 at 12:25 Comment(4)
I don't get what you are asking me to try.Bereave
I think that besides the UNION tag the for xml is being applied to the second query only (a parse thing). Maybe if you specify both queries as one it will workPhosphene
ok, if you run a test like my example you will see that it will workPhosphene
Thanks Diego, yeah that seems to have done it...I just had to add the 'As XmlOutput' on the end as it was the naming of the output column that was most important.Bereave

© 2022 - 2024 — McMap. All rights reserved.