Using OPENXML in .NET

The second approach is a slight evolution of the first one. Instead of writing .NET code to extract the data from the XML document we write SQL code containing a SELECT statement combined with the OPENXML clause as shown in the next method.

public static void InsertOrderHeaderWithOPENXML( string xml,

  string connectionString )

{

 string statement = String.Format(

    @"DECLARE @idoc int

    DECLARE @doc varchar(1000)

    SET @doc ='{0}'

    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

    INSERT OrderHeader ( OrderID, CustomerID )

    SELECT  OrderID, CustomerID

    FROM OPENXML (@idoc, '/OrderHeader',2)

      WITH (

      OrderID int,

      CustomerID int)

    EXEC sp_xml_removedocument @idoc", xml );

    ExecuteNonQuerySQL( statement, connectionString );   

}

Coding effort and maintenance liability for this technique are very similar to the previous one. The SQL code in this method is just as much special purpose as the C# code that composed the SQL from the content of an XmlDocument. The opportunity for re-using this function in any other context than inserting data from this particular XML format is zero. Handling more complex XML formats is also possible. We would simple add more INSERT…SELECT … OPENXML …WITH statements to insert data into more than one table.

The performance characteristics are slightly different than the last one. On the plus side, you don’t need as much memory on the application server because you are not parsing the XML document to compose a SQL statement. On the other hand, you need the extra memory and the processing cycles on your database server, which lowers the overall scalability of your application. Of course this point is moot if your application is not deployed to separate servers.

Depending on your company’s database philosophy you may want to move the SQL code into a stored procedure, which would yield better separation of XML conversion and database access code.  In some workplaces, however access to databases is much more restricted than access to an application server, in which case you might rather want to create SQL in your .NET components than executing stored procedures.