SQL Server "for xml", multiple tables, multiple nodes

Question

Is it possible to select multiple tables at once? For example, I can do:

SELECT (
  SELECT * FROM Articles
  FOR XML PATH('article'), TYPE
)
FOR XML PATH('articles'), ROOT('data')

and

SELECT (
  SELECT * FROM ArticleTypes
  FOR XML PATH('articleType'), TYPE
)
FOR XML PATH('articleTypes'), ROOT('data')

Can I join both so that I get the following output? I can't use UNION because the table structures don't match.

<data>
  <articles>
    <article>...</article>
    ...
  </articles>
  <articleTypes>
    <articleType>...</articleType>
    ...
  </articleTypes>
</data>

Answer 1

Each column in a union has only one column name, and that column name is taken from the first query. You can get around that by set columns from the other table to null, instead of omitting them.

select *
from (
    select 
        1 as 'Articles/Id'
    ,   'Name' as 'Articles/Name'
    ,   null as 'ArticleType/Id'
    ,   null as 'ArticleType/Type'
) Articles
union all
select *
from (
    select 
        null as 'Articles/Id'
    ,   null as 'Articles/Name'
    ,   1 as 'ArticleType/Id'
    ,   'Type' as 'ArticleType/Type'
) ArticleType
FOR XML PATH(''), ROOT('data')

This results in:

<data>
    <Articles>
        <Id>1</Id>
        <Name>Name</Name>
    </Articles>
    <ArticleType>
        <Id>1</Id>
        <Type>Type</Type>
    </ArticleType>
</data>

Answer 2

Depending on the version of SQL Server you are using. How about something like this stack overflow post?

Answer 3

It is possible to NOT list out all the columns in the tables, NOT have to use a UNION and not have to have repeating columns using the following method:

1. Grab the data you need into temp tables (in my case it was from a different server using openquery so it was simpler but can be skipped if the rows are local)
2. Cursor through your main control table and for each Primary Key row:
   • generate your multi-level XML Path Output and place it into an xml variable
   • insert your xml variable into an output tempXMLOutput table with a column of type xml
3. Once you fetched all data in the cursor output your tempXMLOutput table

This will display all the data without the funky repeating columns and it does not take long to code for.