Funny Issue with For XML Explicit | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Funny Issue with For XML Explicit

Hi All,
I am facing a funny problem while using "For Xml Explicit" in my stored procedure. The procedure make joins in 3 tables and then returns me an xml. The problem:
–The procedure executes perfectly fine, but some times it stops giving back the values in XML. It just returns empty tags. –When I recreate the stored procedure again it starts working and gives the data correctly. I found that we should use WITH RECOMPLIE option in stored procedure to resolve this issue, but it does not seem feasible to me, because in actual env. this procedure will be referenced by many users and will create performance issue.
Help in this issue will be higly appreciated. Thanks,
Check whether this link is any help and also refer to books online formore information on this FOR XML clause. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
This posting is provided �AS IS� with no rights for the sake of knowledge sharing.