This is new to me. I have a new boss at work who is insisting that every query we do from now on be a sproc with XML serialized parameters and return types.
I've not run any tests yet but this strikes me as overkill and possibly a performance killer in many ways. What is your experience?
Though being an obvious performance killer (imagine parsing several megs of XML returned from a sproc), it's even more a productivity, scalability and maintanability killer. Working with XML in T-SQL is not exactly painless neither seamless. Support will be a nightmare: imagine adding a single column to the resultset, which will lead to an avalanche of modifications in both serialization and deserialization code.
Plus, you'll not be able to use neither ORM tools, nor simple result-set mappers (iBATIS or BLToolkit).
It potentially allows you a certain level of backward and forward compatibility (as the proc can start to understand extra parameters) - but I'd say that applying it across the board is ridiculous in just about all ways.
I suggest you ask your boss to give technical reasons why he thinks it's a good idea.
It's a really easy (read: messy) way to use single parameters which might make the code that references the stored procedure call look neater and simpler.
Insisting is a really strong stance for a new boss to take in code review and standardisation. Common coding practices are good things in teams, but it strikes me that you can hide some really bad smalls by passing XML around the place. Yes it might make the responses seem more general, but wouldn't it be better, if you're planning on handing the XML off to another process, to wrap that in a layer: you can tightly bound your Data Access layer to the stored procedure and then build the XML explicitly from there, getting the type-checking, TDD and ancillary good stuff.
Sounds like someone's just read that you can do it and thinks "XML is good, right?' without thinking for too long on whether you should do it.
I was in the same situation a little while back, and as well as the performance overhead we had some really nasty bugs with this approach. Its really easy to add/remove/rename tags from in an Xml document, when you do this unless your SProc is kept upto date you'll find unusual data in the DB. My advice is to avoid this approach - just don't do it ...
BTW Is your boss from AO ?
Hey, lets take our least scalable component, and make it do intensive CPU work ;-p
OK, that was tongue in cheek. Xml as arguments and return values have uses in a few specific cases with structured data, but in general a flat TDS stream (i.e. grids) is far more efficient. For input, either CSV (split via udf) or table-valued-parameters (SQL 2008) are good options.
Sql/xml in 2005+ is much better than with openxml - and indeed, once xml is stored and indexed in the server (using the xml data type) it is quite efficient - but as input and output it can be a bottleneck if you aren't careful.
Don't make it the default, but consider it as one of the available options.
Interesting. Parsing strings in SQL is no easy task. . What would xml achieve? (certainly not loose coupling because decoupling of the database and the application tier is already achieved by having stored procs)
I believe you write two stored procs which do identical things but one written the plain vanilla way and one written in xml. Hopefully your boss can "see" and take the obvious call.
I've said this before, and I'll say it again: There is no silver bullet.
Using XML as both the input and output for your stored procedures is a silver bullet solution, and it proves one thing only: that one solution that might be a good choice in one instance is grossly inappropriate in many (likely the vast majority of) others.
Consider, if you will, a stored procedure that takes no parameters and returns a single scalar value. XML is clearly overkill in this scenario.
Consider also a stored procedure in which you pass a date range (two single date values) and return all rows from a set of joined tables that fall within that range. Encapsulating the dates in XML might not be overkill, but encapsulating the variable amount of data that you'll get back might be. You could get back zero rows. You could get back thousands of them.
Consider the case where the type of the data you get back from the stored procedure depends on the types and values of the arguments that you pass to it. Further, because the nature of the rowsets is fairly volatile, one or more of those rowsets might change down the road, requiring coding changes. Finally, you have a solution that might be amenable to XML.
So, should you penalize the entire codebase because a tiny subset of them suffer from mutability issues? Or should you design a solution that encapsulates those volatile datasets behind a properly designed facade that protects the rest of the system from that volatility?
What you will essentially find is that the silver bullet of XML Everywhere is not a silver bullet, but a nuclear warhead. When you wanted a solution that would strike a single target and solve your problem, what you'll end up with is something that will destroy everything in sight because you will have brought far more firepower to bear on the problem than you could reasonably handle. And then, as is typically the case, the weapon destroyed those who attempted to wield it.
There is a need to transfer data-string messages between the Client (Browser) and Server executed Web-site programs. However these message strings should have simple structural rules that enable quick parsing of its information into named argument and argument-field components. XML was created as an intellectual exercise to somehow describe the complicated object relationships and attributes of a page full of HTML objects. XML is very pedantic esoteric exercise for the professorial theorists, but as a practical tool for most Web-site communication, it is a joke.