TheSQLGuru (10/26/2013)
See here for 1 SIGNIFICANT improvement to the XML version, and lots of other helpful information too: http://www.sqlservercentral.com/Forums/Topic1046233-1273-1.aspx
It's a funny thing about that "improvement". While the Actual Execution plan looks totally awesome compared to the orginal, it's not any better than the original from what I've seen in the testing I've done. In fact, both can be pretty well beat up by using a CAST to VARCHAR(MAX) instead of using any reference node references at all.
I had reason to revisit that discussion and I'm not sure how they came up with such a difference in times on that post. I'm still checking to make sure I didn't screw something up in my testing.
--Jeff Moden
Change is inevitable... Change for the better is not.