troubleshooting Question

SQL FOR XML EXPLICIT problem with UNION ALL selects returning empty recordsets

Avatar of richardsimnett
richardsimnett asked on
Microsoft SQL Server
7 Comments1 Solution480 ViewsLast Modified:
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.

.thanks.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros