There are a few new SqlXml-related articles up on MSDN.
First we have Amar Nalla's XML to SQL: Using SQLXML Bulkload in the .NET Framework which gives great examples on constructing annotated schemas and using Bulkload within .Net. Amar goes into great detail on how to feed Bulkload with a stream, a process that takes some work when going from .Net through managed interop to Bulkload. If you're using Bulkload within .Net check it out, its a great article and well worth your time to read.
A little bit older is Michael Rys's article What's New in FOR XML in Microsoft SQL Server 2005 which discusses enhancements to the FOR XML extensions to T-SQL offered by SQL Server. I've played with the new extensions a bit, not nearly as much as I should have, but am none the less very enthused about them. I think the new PATH syntax will go a long way toward helping people construct complex shapes from there relational table quicker and easier than they could be for with EXPLICIT.
Of course, this brings up the question, if we have this great new PATH mode for making complex XML shapes, why do we need mapping? Its a question I've been asked on more than a few occasions, so here are a few reasons why I still consider mapping a valuable investment:
Composability - Let's say you want to produce results that conform to the same XML schema from several queries. If you're using FOR XML, each time you have a new query, you have to make sure the output matches your target schema. However, with mapping, you define your output once, and can then query it in many ways, each time only re-writing the query. You can also then allow others to give you queries to execute and you're still guaranteed the same output format - not possible with FOR XML.
Updates - FOR XML is wonderful for producing XML from your relational data. But what about if you want to update that same data? Now you have to have a stored procedure to do the update and a FOR XML to retrieve the data, and you have to figure out how the data produced by the FOR XML statement maps back to the database. With mapping, we provide both query and update technologies, so you only have to think about one thing - the XML.
Multiple Databases - This actually relates back to Composability. You have a few different SQL databases, some 2000, some 2005, maybe with the same relational schemas, maybe not. With mapping, you can establish a mapping to each database and then query them using the same XPath queries each time, and let us handle generating the query for that particular database. If you're using FOR XML, you have to write a query for each of those databases and make sure they all resolve to the same schema.
This isn't to say I don't think FOR XML is useless or anything (though I'm obviously a bit biased toward mapping), quite the contrary. You will get much more control of the queries you're doing by using it. There is just simply more you can do by constructing the query yourself, things SQLXML does not support. You can store those queries inside stored procedures within the database, making your DBA much happier I'm sure. You can leverage your existing SQL knowledge without having to know about mapping and XPath as well.
So what am I saying here? Which one you use depends on what you consider most valuable. Do you need every last bit of performance and do you need to fine tune every query? Go with FOR XML. Can you give up some control of your queries in exchange for more composability or the ability to update? Mapping is the way to go. You can even mix the two together, don't be afraid! Query all you like with FOR XML, but when you get that 30 meg XML document, look to Bulkload to get it loaded into your database. Its ok, all of the pieces will play very nicely together, I guarantee.
