A few things I've learned in the past few days on
SELECT {something} FOR XML AUTO, ELEMENTS
- Use subquery to denormalize data so that they can be grouped under the same parent element.
- Do not use synonym to get to your linked server. If you want any elements to be named properly, use full linked server syntax and alias the table/subquery.
I'll add more as I learn them.