title: Import huge XML data (> 1Gb) into SQL Server 2008 daily
body: <p>I have encountered a problem that I need to import a huge XML (> 1Gb) into SQL Server 2008 daily. What I have now is a sample XML file and the XML schema of it. The XML schema is pretty complex which contains many custom defined simple type, and element with complex type such as:</p>
  
  <pre><code>&lt;xs:element name="xxxx_url"&gt;
          &lt;xs:complexType&gt;
              &lt;xs:simpleContent&gt;
                  &lt;xs:extension base="xs:anyURI"&gt;
                      &lt;xs:attribute ref="target" use="optional"/&gt;
                      &lt;xs:attribute ref="abc" use="optional"/&gt;
                  &lt;/xs:extension&gt;
              &lt;/xs:simpleContent&gt;
          &lt;/xs:complexType&gt;
  &lt;/xs:element&gt;
  </code></pre>
  
  <p>After import, a WCF service will be implemented to retrieve the data stored in SQL Sever, something like search, retrieve etc (read-only operations).</p>
  
  <p>The implementation steps I can think of are like:</p>
  
  <ol>
  <li>Define an object model according to the provided XSD (manually), the object model will be used for WCF service to return values.</li>
  <li>Define a database schema from the provided XSD (manually), the schema is estimated to have about 20 - 30 tables.</li>
  <li>Create a SSIS package to load XML into database daily.</li>
  <li>Create a WCF service which reads from database, populates data into the object model defined in step 1 and returns the object to service client.</li>
  </ol>
  
  <p>The problem is that these steps involve lots of manual work. I have to research the XSD row by row, and transform it to object model and database schema mannualy.</p>
  
  <p>I did some research that there're some automation tools to transform XSD into classes, and also transform XSD into database schema. But the classes transformed from XSD with the tool are pretty messed, and the transformation to schema is failed because it does not conform to MS dataset format.</p>
  
  <p>I am wondering is there any good solution to this problem, to save a lot of manual work?</p>
  
  <p>Any suggestion is appreciated !</p>