Using SqlXmlAdapter in .NET

The last technique we examine in more detail is the insert (and update) capability of the DataSet in combination with the SqlXmlAdapter. Of all the insert techniques this one requires by far the least amount of code:

public static InsertWithDataSet( string xml, string connectionString,

  string schemaPath )

{

  DataSet ds = new DataSet();

  SqlXmlCommand cmd = new SqlXmlCommand( connectionString );

  cmd.SchemaPath = schemaPath;

  SqlXmlAdapter adapter = new SqlXmlAdapter( cmd );

  ds.ReadXml( xml );

  adapter.Update(ds);

}

The code to let a DataSet object and a SqlXmlAdapter object collaborate to insert data into SQL Server is very re-usable, since it does not require any XML specific code. Also, the DataSet has built-in support to process more than root object at a time. Again the mappings from XML to the database are defined in a mapping schema so we do not have to write and maintain any code to parse XML and compose a SQL statement. The SqlXmlAdapter() calls get GetChanges() on the passed in DataSet to get one of those DiffGrams which it then hands off to the SqlXmlCommand for execution. The SqlXmlCommand then transforms the DiffGram into an Updategram and transforms that into SQL. Now you can already guess that this doesn’t perform as well as directly composing an updategram with the extra transformation step in the chain of execution.

Another disadvantage is that we now have the three representations of the data in the XML document in memory: The original document, the DataSet object and the DiffGram, but the real show stopper is that you cannot insert nested XML types into the database if children require a foreign key from an automatically generated IDENTITY. The DiffGram does not know how to propagate the parent’s IDENTITY to the children and the complete insert fails. In SQLXML release 3.0 there is no work-around for this problem. The only way to avoid this problem is to not handle those types of inserts from a DataSet.

Also Ran … Bulk Load

SQLXML offers one more technique to insert data into SQL Server: XML Bulk Load. I want to mention it here because it has some nice features that are not available through any other upload technique: Fully transacted upload, for example, and automatic table generation if a table referenced in the source document is not present in the database. We’re not going into detail here because it is not accessible through a managed .NET class, only through COM interop.

As you can derive from the name, the XML Bulk Load COM component is designed for loading large feeds of XML data. In contrast to all the other techniques we examined so far, BulkLoad does not require loading the complete source document into memory. It also relies on an annotated schema to map the XML to the database, i.e. we can build a very generic component to handle bulk uploads. On the negative side Bulk Load also cannot handle nested types where children require the IDENTITY of the parent as a foreign key.