Root element with FOR XML

[theoxyde]

Is there any way to get a root element into a FOR XML AUTO query?

For example:

select * from tableA
join tableB on tableA.id = tableB.idA
for xml auto

Each record for tableB is nested properly within the tableA result, but there is no root node above tableA's results.

I've seen examples using SQL Server over IIS, but that isn't an option in this case.

 

[jms8222]

If using "FOR XML AUTO, ELEMENTS" does not get you what you want, then you will probably have to use "FOR XML EXPLICIT". You would then have to re-write your query using UNION. Here is an example:

SELECT     1 As Tag, 
        NULL As Parent, 		
 [tableA.id] As [MyRoot!1!MyId!element]
        NULL As [MyID!2!MyField]
FROM tableA

UNION

SELECT     2, 
           1,
 [tableA.id]
 [tableB.MyField]

FROM tableA
JOIN tableB
ON tableA.id = tableB.idA
ORDER BY [MyRoot!1!MyId!element]

FOR XML EXPLICIT

Good Luck!

 

[theoxyde]

AUTO and AUTO, ELEMENTS is fine if there is only one result being returned. I'll look into the EXPLICIT though.