Geek Answers Handbook

SQL UNION for XML Name Output Column

I am trying to generate XML output from SQL and should use the UNION operator as well as the name of the output column.

I worked on this before when I didn't need to use the UNION operator using:

select( SELECT [CompanyName], [Address1], [Address2], [Address3], [Town], [County], [Postcode], [Tel], [Fax], [Email], [LocMap] FROM [UserAccs] FOR XML PATH ('AccountDetails'), root ('Root') ) as XmlOutput

What called the XML output column as XmlOutput

Now I am 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 get an error message, does anyone know about 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.

+11
sql-server tsql sql-server-2008 sqlxml
source share
1 answer

Wrap your 2 choices on one, for example like this:

 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
+20
source share

More articles:


All Articles