title: SQL UNION FOR XML name output column
body: <p>I'm trying to generate an XML output from SQL and need to use a UNION statement and also name the output column.</p>
  
  <p>I had this working before when I didn't need to use a UNION statement using:</p>
  
  <pre><code>select(
  SELECT
  
      [CompanyName],
      [Address1],
      [Address2],
      [Address3],
      [Town],
      [County],
      [Postcode],
      [Tel],
      [Fax],
      [Email],
      [LocMap]
  
  FROM [UserAccs] FOR XML PATH ('AccountDetails'), root ('Root') 
  ) as XmlOutput
  </code></pre>
  
  <p>Which named the output XML column as XmlOutput</p>
  
  <p>I am now trying:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But receive an error message, does anyone know a way around this?</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>Thanks
  J.</p>