Load XML data without XSL into sql table using ssis script task

Question

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.

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

<ExportPackageResult xmlns="http://schemas.data.org/2004/07/Services" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"><ResponseDateTime>2015-09-22T19:02:44.2363931-04:00</ResponseDateTime>
<datapoints>
<PackageDataPoint>
  <ADDON>
    .....
  </ADDON>
  <FIELDSET>
    .....
  </FIELDSET>
</PackageDataPoint>

The data for my table resides inside each PackageDataPoint.. /PackageDataPoint and there will be various childnodes like ADDON../ADDON, FIELDSET../FIELDSET etc inside each

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.

Below is my script task 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;
            }
        }

When I run the code i'm getting the error

data at the root level is invalid. Line 1, position 1.

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.

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".

Importing data from XML file to SQL database

Pass XML as Parameter from C# to SQL Stored Procedure in VB.NET

My SQL procedure that i use to save xml data to sql table will be similar to this

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

Can anyone help me to resolve the error i am facing.

Answer 1

You are trying to load Xml from something like "somefilename.xml"

XmlDocument readXml = new XmlDocument();

try {
    readXml.Load(strFileName);

You want to load the xml data from this file first. Try something like this.

XmlDocument readXml = new XmlDocument();

try {
    var xmlFileContents = File.ReadAllText(strFileName);
    readXml.Load(xmlFileContents );