On various MS code help forums I've seen dozens of posts along the lines of "How do I get a root tag in an FOR XML EXPLICIT statement. There have been numerous responses:
Do three SELECTs, with a "SELECT '<root>' FROM [Foo]" as the first one
Read the data back into one string, and append the opening/closing tag
Etc. etc.
These all feel like "hacks" to get round the real issue - SQL Server creates structured XML by converting flat, tabular data into a hierarchy using level-indices (i.e. an integer from 0..N). All we actually need to do is create a single element at a level higher. So, given the following SQL (Run against Northwind):
SELECT
1 AS Tag,
NULL AS Parent,
[CustomerID] AS [customer!1!customerid]
FROM
[Customers]
FOR XML EXPLICIT
We can add a root element of "customers" by adding the following preceding SQL:
SELECT
1 AS Tag,
0 AS Parent,
NULL AS [customers!1!foo],
NULL AS [customer!2!customerid]
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
[CustomerID] AS [customer!2!customerid]
FROM
[Customers]
FOR XML EXPLICIT
To see how this tabular -> hierarchical conversion takes place, simply omit the FOR XML EXPLICIT statement from the end of each statement when run in Query Analyzer.