performance of EXPLICIT vs PATH

[Dan Holmes]

has anyone noticed any performance benefits from changing FOR XML EXPLICIT SELECTs to FOR XML PATH(...) SELECTs? I have
some large EXPLICIT SELECTs that were written during the sql2000 days and haven't been changed for the new XML features.
I only want to spend the time on this if there is the chance of performance benefits.

thanks
danny

[Bob]

I'm not aware of any specific performance benefits with FOR XML PATH, apart
from the gains in maintainability, due to the queries being a good deal
simpler.

I've refactored a fair few on the forums changing 30, 50 or 100 line FOR XML
EXPLICITs to 5 or 10 line FOR XML PATH statements, eg

http://www.eggheadcafe.com/software/aspnet/34113846/xml-explicit-help.aspx

plus there's other examples:

Quote from Ward Pond's blog:
http://207.46.16.252/en-us/magazine/2005.05.sqlxml.aspx
"...my team recently rewrote one 5000-line SQL Server 2000 user-defined
function (which was basically a 43-level FOR XML EXPLICIT query) in 497 lines
using this new syntax..."

So you could say "development peformance" is improved, ie developers won't
have to spend hours unpicking complex FOR XML EXPLICIT statement and
understanding the universal table, which would pay for itself fairly quickly.

FOR XML PATH isn't perfect though, eg it can add additional namespaces which
is a bit of a nuisance.

I guess there is a lot to weigh up! The best thing to do is try refactoring
one of you candiate FOR XML EXPLICIT queries and test it with your data.
Assess how how much effort it was to refactor, how complex the query was
before and after.

Let us know how you get on!

"Dan Holmes" wrote:

> .
>