ucbus 92278 (10/20/2009)I have tried to use "FOR XML" tag by creating a view of the data, However this resulted in multiple rows in a FLAT structure. To get the XML in the format I needed, I would have to use XSLT transformation I guess.
Not necessarily. You're making a couple of assumptions here. Just because the XML looks flat doesn't mean that the XSD file won't read it. I have XML files like that which I import into SQL Server using SSIS with an XSD file all the time. It works just fine.
Also, there are three different versions of the FOR XML command. Are you sure you tried all three of them to get the exact format you wanted? (aside from the flat issue, that is).
If you don't want to use SSMS for your results, create an SSRS report and a subscription to that report that exports into an XML format. I've done that quite often and it works amazingly well. Even if it looks "flat", an XSD file will read it. I know this for a fact.