Manually inserting SQLXML data

In this section we will examine different approaches to inserting data from an XML document into the database. The tables used in some of the examples are very simplified versions of tables in the database for the Southrain sample application, which you can download from the book’s website at URL. Specifically we will insert data as shown in the following diagram:

Insert operation performed by the examples in this section

Note that this table does not define an automatically generated identity column. Identities add a whole order of complexity when we are inserting XML data. 15.3.4.4 will discuss the limitations and show some possible solutions.

Handling update and delete operations wit updategrams is very similar to the inserts we are about to examine. In fact, INSERTs and DELETEs are special cases of an UPDATE as we have already seen in section 15.3.2.3. Hence we do not need separate sections for updates and deletes. The characteristics of the approaches we discuss in the section are equally true for update and delete operations.

The first approach we examine is again the conversion of XML to SQL by hand, just like you would if you were not using SQL Server 2000. In order to access the data in the XML document we first have to parse it. After reading your way up to this point you’ve seen quite a few different ways how to accomplish this with the classes in the .NET Framework.

Parsing with the XmlTextReader is not a good choice in this particular situation because of the forward-only access to the document. While accessing the XML data node-by-node is sufficient in simple cases like this example, it makes composing a SQL statement very hard once the document contains nested types that map to multiple database tables. You would have create multiple SQL statements in parallel, which gets pretty tricky, or you would have to parse over your XML document multiple times, which would impact the performance of your insert method. I think you agree that the XmlTextReader is not a good choice here.

The other two options for parsing and XML document for random access are Xml serialization and, of course, the XmlDocument. The performance of the two approaches will vary with the complexity of the XML document you are parsing, for simple documents the XmlDocument has a slight edge over Xml Serialization. Both techniques do have similar memory requirements, too. You need available memory about twice the size of the Xml document to convert the raw document into the parsed representation. Keep this in mind if your application frequently inserts data from large XML documents.

The example below shows how we would parse the XML document with an XmlDocument object to create a SQL statement:

public static void InsertWithXmlDocument( string xml,

  string connectionString )

{

  XmlDocument doc = new XmlDocument();

  doc.LoadXml( xml );

  XmlNode orderNode = doc.FirstChild;

  string statement = String.Format( "INSERT OrderHeader ("

    +"OrderID, CustomerID"

    +" ) VALUES (" {0}, {1} )"

    , orderNode.ChildNodes[0].InnerXml

    , orderNode.ChildNodes[1].InnerXml );

  ExecuteNonQuerySQL( statement, connectionString )

}

public static void ExecuteNonQuerySQL( statement, ConnectionString )

{

  SqlConnection cn = new SqlConnection( connectionString );

  cn.Open();

  SqlCommand cmd = new SqlCommand( statement );

  cmd.Connection = cn;

  cmd.ExecuteNonQuery();

  cmd.Dispose();

  cn.Close();

}

Looking at the first method in this example you can imagine that generating the SQL INSERT for a large XML type quickly gets tedious. If the XML document we are trying to insert is made up from a heavily nested structure we will have to write code to create one SQL statement for each table into which we are inserting data. To make matters worse, none of the code in the first method is reusable. We will have to write more methods like this for every XML type we have to insert into our database and we can only hope that our schemas, both XML and the database rarely change, because changes might impact several methods in our data access component. 

On the upside, inserting data this way is one of the fastest techniques in terms of overall performance. Not putting additional load on the database server because it never sees an XML document is another benefit of this approach. Finally, because we write custom code for every type anyway, there are no limitations as to what XML structures we can map to the database and in which format they are coming in. We can even map data from the XML document to different databases altogether.