title: Load XML data without XSL into sql table using ssis script task
body: <p>I have a xml data that do not have xslt associated with it. Its an auto generated xml provided by my client. below is the sample of the xml document.</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="false" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>HTTP/1.1 200 OK
  Content-Length: 47033212
  Content-Type: application/xml; charset=utf-8
  Server: Microsoft-IIS/7.5
  X-Powered-By: ASP.NET
  Date: Tue, 22 Sep 2015 23:02:56 GMT
  
  &lt;ExportPackageResult xmlns="http://schemas.data.org/2004/07/Services" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;ResponseDateTime&gt;2015-09-22T19:02:44.2363931-04:00&lt;/ResponseDateTime&gt;
  &lt;datapoints&gt;
  &lt;PackageDataPoint&gt;
    &lt;ADDON&gt;
      .....
    &lt;/ADDON&gt;
    &lt;FIELDSET&gt;
      .....
    &lt;/FIELDSET&gt;
  &lt;/PackageDataPoint&gt;</code></pre>
  </div>
  </div>
  </p>
  
  <p>The data for my table resides inside each  PackageDataPoint.. /PackageDataPoint and there will be various childnodes like ADDON../ADDON, FIELDSET../FIELDSET etc inside each </p>
  
  <p>I am trying to load each data values inside addon, fieldset into a sql table using SSIS script task. the column names in my sql table would be addon, fieldset and so on.</p>
  
  <p>Below is my script task code</p>
  
  <pre><code>public void Main()
          {
              // TODO: Add your code here
              System.Data.SqlClient.SqlConnection sqlConn;
              System.Data.SqlClient.SqlCommand sqlCmd;
  
              string strFtpath = Dts.Variables["User::strFTPpath"].Value.ToString();
              string strArchivePath = Dts.Variables["User::strArchivePath"].Value.ToString();
              string strConnectionStr = Dts.Variables["User::strStagingConnstr"].Value.ToString();
              string strFileName = string.Empty;
  
              DirectoryInfo di = new DirectoryInfo(strFtpath.ToString());
              FileInfo[] getCSVFile = di.GetFiles("*.xml");
              foreach (FileInfo fi in getCSVFile)
              {
                  strFileName = fi.FullName.ToString();
              }
              DataSet dsXml = new DataSet();
  
              XmlDocument readXml = new XmlDocument();
  
              try
              {
                  readXml.Load(strFileName);
                  using (sqlConn = new SqlConnection(strConnectionStr));
                  {
                      sqlConn.Open();
                      sqlCmd = new SqlCommand("usp_XMLtoTable", sqlConn);
                      sqlCmd.CommandType = CommandType.StoredProcedure;
                      sqlCmd.Parameters.Add("@xml", SqlDbType.Xml).Value = dsXml.GetXml();
                      sqlCmd.ExecuteNonQuery();
                      sqlConn.Close();
                  }
  
                  Dts.TaskResult = (int)ScriptResults.Success;
                  Dts.Variables["User::flgDataLoadStatus"].Value = 1;
              }
              catch (Exception e)
              {
                  Dts.Log(e.Message, 0, null);
                  Dts.TaskResult = (int)ScriptResults.Failure;
                  Dts.Variables["User::flgDataLoadStatus"].Value = 0;
              }
          }
  </code></pre>
  
  <p>When I run the code i'm getting the error </p>
  
  <blockquote>
    <p>data at the root level is invalid. Line 1, position 1.</p>
  </blockquote>
  
  <p>for the above code. I know its because of the first 7 lines in my xml document however i dont know how to remove it from the xml document as I cannot do it manually.</p>
  
  <p>I tried few workarounds by reading the xml document using XMLTextReader, XMLDocument, StreamReader, however i am getting the same error "data at the root level is invalid".</p>
  
  <p><a href="https://stackoverflow.com/questions/10795787/importing-data-from-xml-file-to-sql-database">Importing data from XML file to SQL database</a></p>
  
  <p><a href="http://www.aspdotnet-suresh.com/2012/12/aspnet-send-xml-file-as-parameter-to.html" rel="nofollow noreferrer">Pass XML as Parameter from C# to SQL Stored Procedure in VB.NET</a></p>
  
  <p>My SQL procedure that i use to save xml data to sql table will be similar to this</p>
  
  <pre><code>CREATE PROCEDURE prc_readxmldata
  (
  @XMLdata XML
  )
  AS
  BEGIN
  SELECT
  t.value('(FirstName/text())[1]','nvarchar(120)')AS FirstName ,
  t.value('(LastName/text())[1]','nvarchar(120)')AS LastName,
  t.value('(UserName/text())[1]','nvarchar(120)')AS UserName,
  t.value('(Job/text())[1]','nvarchar(120)')AS Job
  FROM
  @XMLdata.nodes('/datapoints/packagedatapoints')AS TempTable(t)
  END
  </code></pre>
  
  <p>Can anyone help me to resolve the error i am facing.</p>