I'm trying to return rows in Open XML form from a table value function in SQL Server (2008). I'm occasionally getting errors when I select the results with "for xml explicit" which suggest that the order of the results is not guaranteed to be the same as the order I insert into the output of the TVF.
So my first question is: is that the case?
If so, my next question is: is there any way around this? The best I have been able to come up with so far is to include an id column in the TVF output, then select only the other columns when I call it, and order by id. The problem here is that it means I have to specify all the columns every time, rather than just selecting *, which is really awkward in this case (especially since I have 42 of these TVFs - all with different columns).
Do TVFs and Explicit Open XML just not mix (which would be a real shame as they seem to be a great fit), or am I missing something that will help?