i have a question about FOR XML EXPLICIT
this sproc has a SQL XML of many levels (8)
now sometimes the selects wont always be returning anything. (ie empty result set)
so how do i avoid this error when thats the case?
Server: Msg 6833, Level 16, State 1, Procedure sp_xxxxxxxxx
Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.
or please correct me if i am wrong about that being the reason i see that error msg.
however, i doubt that's the case because when all the tables have valid data the same sproc works beautifully.
also this sproc is almost too large for it to be built up dynamically and run with a call to sp_executeSQL.
and to be honest, i am hoping there is an easier way to handle the problem than that. not to mention that doing a select count(*) to determine whether or not to add in the next subsection's UNION ALL select code will kill the performance of this sproc.