title: Upsert on SQL Server table from XML
body: <p>I'm attempting to create a small console app in C# to perform inserts on a table of Products (ITEMS) in SQL Server 2008 according to the contents of an XML file in the FASTEST way possible.  I already have an .XSD file that contains the proper mappings to the SQL table (which may not be necessary with the approach outlined below).  </p>
  
  <p>Here's a high-level of my approach:</p>
  
  <ol>
  <li>Read the XML, using it to create a table.</li>
  <li>Perform a MERGE against the ITEMS table using the table created from the XML file.<br>
  2a. If the item exists, update it.<br>
  2b. If the item does not exist, insert it.  </li>
  <li>Create a log of only the records inserted in XML.  </li>
  </ol>
  
  <p>Consider the following ITEMS table and XML file:  </p>
  
  <p><strong>ITEMS</strong></p>
  
  <pre><code>  Item_Id    Name    Price  
       1       Coke     5.00  
       2       Pepsi    3.00  
       3       Sprite   2.00   
  </code></pre>
  
  <p><strong>ITEMS.XML</strong>  </p>
  
  <pre><code>   &lt;?xml version="1.0" encoding="ISO-8859-1"?&gt;
     &lt;Item&gt;
      &lt;Id&gt;5&lt;/Id&gt;
      &lt;Name&gt;Mountain Dew&lt;/Name&gt;
      &lt;Price&gt;4.50&lt;/Price&gt;
     &lt;/Item&gt;
     &lt;Item&gt;
      &lt;Id&gt;3&lt;/Id&gt;
      &lt;Name&gt;Sprite Zero&lt;/Name&gt;
      &lt;Price&gt;1.75&lt;/Price&gt;
     &lt;/Item&gt;
  </code></pre>
  
  <p>After the import, the ITEMS table should look like:  </p>
  
  <p><strong>ITEMS</strong></p>
  
  <pre><code>  Item_Id    Name         Price  
       1       Coke          5.00  
       2       Pepsi         3.00  
       3       Sprite Zero   1.75  
       5       Mountain Dew  4.50
  </code></pre>
  
  <p>Once that's done, I also need to generate an XML formatted log file that contains the "new" record that was inserted into the table (ITEMS_LOG.XML):  </p>
  
  <p><strong>ITEMS_LOG.XML</strong></p>
  
  <pre><code>   &lt;?xml version="1.0" encoding="ISO-8859-1"?&gt;
     &lt;Item&gt;
      &lt;Id&gt;5&lt;/Id&gt;
      &lt;Name&gt;Mountain Dew&lt;/Name&gt;
      &lt;Price&gt;4.50&lt;/Price&gt;
     &lt;/Item&gt;
  </code></pre>
  
  <p>I have tried implementing this using SQLXMLBulkLoad, but unfortunately it does not provide the logging that I need, nor does it permit me to access any of the messages returned from SQL Server (i.e. what's been inserted/updated).  Although I have an intermediate level of SQL expertise, I am fairly new to working with XML, especially in this context.  Any help/guidance would be greatly appreciated!</p>