Update Technologies
The four options for updating SQL Server through XML are OpenXML, updategrams, DiffGrams, and XML Bulk Load. The following guidelines will help you choose the appropriate XML update technology for your situation.
OpenXML. OpenXML is a T-SQL feature that provides a relational view of an XML document. Although OpenXML doesn't directly update the database, you can use it within a T-SQL script or stored procedure to extract data from an XML document and store that data in database tables. In addition, you can write T-SQL business logic that uses OpenXML to validate the XML data before you update your database. OpenXML works best for relatively small documents. Because OpenXML runs directly within SQL Server, processing the XML—which includes parsing the XML document and allocating memory for the XML Document Object Model (DOM) that XML uses—can introduce a significant processing load to SQL Server. I recommend that you limit the size of the document to less than 1MB. Passing an XML document to the stored procedure that uses OpenXML can also be inefficient. Because you can't pass a document stream or a file reference to a stored procedure, you have to load the entire XML document into a string buffer, which is passed to the stored procedure. Allocating an in-memory buffer for the entire XML document could cause a large memory overhead on the client machine. Still, OpenXML is the best choice when you don't have an XML schema that describes the structure of the data or when the XML is semi-structured. Semi-structured XML documents contain known data elements, but the location of the elements within the document's XML hierarchy might vary. Overall, OpenXML provides the most programmatic flexibility for XML updates to SQL Server.
Updategrams. Updategrams use optimistic updates (updates that don't lock the tables in the database) to modify data in SQL Server and use an XML grammar to specify before and after images for fragments of the modified XML data. (You don't have to include in the updategram the whole data set obtained from the original query.) The primary advantage to using updategrams is the data abstraction they provide. Updategrams implement an XML-to-SQL mapping that eliminates the need to write T-SQL update queries and to understand the database structure. Although this advantage might seem cool at first glance, two obstacles limit the usefulness of updategrams. First, Microsoft hasn't built updategram generation into either the XML DOM or ADO. So if you want to use updategrams, you need to write code that tracks changes to your data and builds the updategrams. After you've built that code, you'll likely encounter the next obstacle: the lack of an object model to write business logic. And the need for business logic that validates the correctness of data before you execute a database update is commonplace. The only object model available for processing updategrams is DOM. Although you can build business logic by using the DOM, the code must tediously navigate the updategram's structure, making the updategram difficult to maintain over time.
One other somewhat useful updategram feature is the parameterized updategram. When you combine parameterized updategrams with SQLXML templates, you can use the updategrams to update the database by simply posting parameters to a URL. All things considered, updategrams have limited applicability in the real world.
DiffGrams. Microsoft introduced DiffGrams in ADO.NET and now supports them in SQLXML 2.0. DiffGrams are essentially updategrams, although you use a different XML grammar to express the updates. Another difference between the two is that you can generate a DiffGram automatically from the ADO.NET DataSet object, but you can't generate an updategram the same way. If you're using the Microsoft .NET Framework, DiffGrams are your best choice for XML updates.
XML Bulk Load. As its name implies, XML Bulk Load is best for loading extremely large XML data sets into SQL Server. Because XML Bulk Load doesn't load an entire XML document into memory, you can use it to load XML documents hundreds of megabytes in size. But XML Bulk Load isn't limited to just large documents. XML Bulk Load supports annotated schemas and transactions, so you can use it as a generic update mechanism. Like updategrams, this option's lack of an object model to write business rules hampers more frequent use for small documents. However, a hybrid approach that compensates for the lack of an object model can produce good results. You can use XML Bulk Load for loading XML data into temporary tables and use stored procedures for executing business rules and transferring data into your database tables.