MSSQL "for xml", multiple tables, multiple nodes
- by Nelson
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>
