title: Best way to bulk insert XML data into SQL Server 2005 database
body: <p>I have to shred around 25 - 30 XMLs into my SQL Server 2005 database (the total size would be around 10 MB). And I need this logic to run automatically as soon as new xml files are copied to the server.</p>
  
  <p>Ive read many posts on this site and also other sites, but can't still conclude on what must I use to shred data.</p>
  
  <p>Pls let me know which option must I should go with</p>
  
  <ol>
  <li>SqlBulk Copy</li>
  <li>C# deserialization</li>
  <li>SSIS</li>
  </ol>
  
  <p>I have to create C# classes for my data models. So C# deserialization was my first choice.
  But pls let me know which option will be right from a performance perspective.</p>
  
  <p>Another thing I forgot to mention was the structure of the XML files will vary. It wouldnt be the same. I will have tables that will have all the columns that could possibly be populated. But the xmls will not have all the data at all times.</p>
  
  <p>Sample of the xml</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;estateList date="2012-08-06T12:17:05"&gt;
    &lt;uniqueID&gt;22XXln&lt;/uniqueID&gt;
    &lt;category name="Apartment" /&gt; 
    &lt;listingAgent&gt;
       &lt;name&gt;DIW Office&lt;/name&gt;  
       &lt;telephone type="BH"&gt;96232 2345&lt;/telephone&gt; 
       &lt;telephone type="BH"&gt;9234 2399&lt;/telephone&gt;
       &lt;email&gt;abcd@abc.com&lt;/email&gt;    
    &lt;/listingAgent&gt;
    &lt;inspectionTimes /&gt; 
    &lt;description&gt;AVAILABLE NOW. &lt;/description&gt; 
    &lt;price&gt;0&lt;/price&gt;  
    &lt;address display="yes"&gt;      
      &lt;street&gt;Lachlsan Street&lt;/street&gt;        
      &lt;ImagesContainer&gt;        
         &lt;img id="m" modTime="2012-08-06-12:17:05" url="http://images/2409802.jpg" format="jpg" /&gt; 
         &lt;img id="a" modTime="2012-08-06-12:17:05" /&gt; 
      &lt;/ImagesContainer&gt;     
    &lt;/address&gt;
  &lt;/estateList&gt;
  </code></pre>
  
  <p>Thanks.</p>