Upload XML file to MS SQL 2005

[exchange keys]

NOTE: I wasn't sure where to post this, so if there is a different forum for my type of question, please move thread accordingly.

We have a market research company that uses Confirmit to create/design surveys. Whenever the end user wants to export any data from it, the data gets compiled as a SSS file (which really is a XML file). This SSS/XML file has the schema of how the data should be manipulated and presented.

Sample of SSS/XML file:

<?xml version="1.0" encoding="utf-8"?>
<sss version="1.1">
  <date>14 6 2011</date>
  <time>12:15</time>
  <origin>Confirmit Export</origin>
  <user>username</user>
  <survey>
    <title>Survey Title Here</title>
    <record ident="A">
      <variable ident="1" type="quantity">
        <name>responseid</name>
        <label>responseid</label>
        <position start="1" finish="8" />
        <values>
          <range from="1" to="99999999" />
        </values>
      </variable>
      <variable ident="2" type="quantity">
        <name>respid</name>
        <label>respid</label>
        <position start="9" finish="16" />
        <values>
          <range from="1" to="99999999" />
        </values>
      </variable>
    </record>
  </survey>
</sss>

I was wondering if the end user(s) could just "import" XML files into a database (MS SQL 2005) so they could keep track of trending data.

Anyone ever had any experience with doing something like this?

Thank you for your time.

 

[dsd311]

You could import the XML into SQL Server using SSIS (Integration Services). Depending on the requirements you are looking for you can either store the XML in a single column as a BLOB or parse the XML out to populate into predefined schema.