tymberwyld (7/3/2009)
I don't see how performance could be improved by using an application solution. SQL is already set-based as can insert, update, and delete anything from the Xml you send in in one pass. CLR or any C# solution on the other hand will need to issue multiple statements to the database (and since you're talking about an application service - you also talking about multiple network passes back and forth between SQL Server). No, this would slow down performance drastically. You'd be surprised how efficient the Xml query engine is in SQL 2005 and up.
The Insert Update and Delete are of course set based, parsing the DOM is about as RBAR as you can get. Who cares if its not apparnt to the developer writing the code. In modern web applications, round trips are not as costly as they used to be in client server set ups. The data tier and the web tier are usuallu a few firewalls away on a very fast backplane. That is no argument to introduct xpath and its friends into the data tier.
Do not even get me started with what the TDS protocol will do to a 200k xml blob. Which is what you will start getting if your app group reliazes you are in bed with the XML mistress.
Have you taken into account what XML parsing will do to tempdb? All of this for what, to be able to play with xpath in your stored procs. No thanks. XML is a data strucutre for the application tier.