Eric Inman (7/4/2009)
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.
Right, don't get me wrong, I'm not advocating Xml for every data operation. However, sometimes there's no getting around RBAR. Mostly, I've only had the need to parse small amounts of Xml and only at times where I am transmitting data through the Service Broker. It's never been a performance issue because all of the processing happens asynchronously and on different threads. The client application just "fires and forgets". Of course, I'm talking about data that doesn't need to be "real-time" or anything. The one thing I do hate is creating another application that needs to be maintained by the IS/IT and takes a programmer to dig in and change the logic. By having the Xml processed within SQL Server, any DBA (with a little reading) can manipulate the Xml processing logic when the business needs change without recompilation of any external dependencies (i.e. applications).