C# / XML issues d'insertion de données dans la base de données - Stack Overflow

I have this situation. My company wants me to create this app that reads and inserts data from a XML file that we get from another source. The part I am stuck on is that when I receive the XML file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<queryConfirmation xmlns="http://www.npdb-hipdb.hrsa.gov/QueryResponseCommon">
<submitter xmlns="">
    <entityDBID>800000000000001</entityDBID>
    <vendorID>1111514</vendorID>
</submitter>
<submissionFilename xmlns="">test.xml</submissionFilename>
<certification xmlns="">
    <name>JOE SMITH</name>
    <title>SUPERVISOR</title>
    <phone>
        <number>4075556647</number>
    </phone>
    <date>2008-02-26</date>
</certification>
<batchStatus xmlns="">
    <dcn>2000000000388120</dcn>
    <processDate>2014-04-03</processDate>
    <successfullyProcessed>false</successfullyProcessed>
    <error>
        <code>09</code>
        <message>09: This entity does not have the privilege to perform this transaction.</message>
    </error>
</batchStatus>
</queryConfirmation>

I can't insert the data into the database with my current code unless I take out the mlns="" and xmlns="http://www.npdb-hipdb.hrsa.gov/QueryResponseCommon" from the XML file manually. I feel like taking this information out of the file is wrong but the following is my code:

private void insertResponseXml()
    {

        OpenFileDialog ofd = new OpenFileDialog();
        ofd.Filter = "XML|*.xml";
        if (ofd.ShowDialog() == DialogResult.OK)
        {
            XmlDocument doc = new XmlDocument();
            doc.Load(ofd.FileName);




            XmlElement xelRoot = doc.DocumentElement;
            XmlNodeList rootNodes = xelRoot.SelectNodes("/queryConfirmation");
            XmlNodeList submitterNodes = xelRoot.SelectNodes("/queryConfirmation/submitter");
            XmlNodeList certNode = xelRoot.SelectNodes("/queryConfirmation/certification");
            XmlNodeList certPhoneNode = xelRoot.SelectNodes("/queryConfirmation/certification/phone");
            XmlNodeList batchNodeList = xelRoot.SelectNodes("/queryConfirmation/batchStatus");
            XmlNodeList bsErrorList = xelRoot.SelectNodes("/queryConfirmation/batchStatus/error");





                foreach (XmlNode xndNode in submitterNodes)
                {
                    entityDBID = xndNode["entityDBID"].InnerText;
                    vendorID = xndNode["vendorID"].InnerText;

                    foreach (XmlNode submisFilenameNode in rootNodes)
                    {
                        submissionFilename = submisFilenameNode["submissionFilename"].InnerText;

                    }

                    foreach (XmlNode cfn in certNode)
                    {
                        name = cfn["name"].InnerText;
                        title = cfn["title"].InnerText;
                        certificationDate = cfn["date"].InnerText;
                    }

                    foreach (XmlNode cfnp in certPhoneNode)
                    {
                        phoneNumber = cfnp["number"].InnerText;

                        if (cfnp["extension"] == null)
                        {
                            phoneExtension = "null";
                        }
                        else
                        {
                            phoneExtension = cfnp["extension"].InnerText;
                        }
                    }

                    foreach (XmlNode bsNode in batchNodeList)
                    {
                        dcn = bsNode["dcn"].InnerText;
                        processDate = bsNode["processDate"].InnerText;
                        successfullyProcessed = bsNode["successfullyProcessed"].InnerText;
                    }

                    foreach (XmlNode bsError in bsErrorList)
                    {
                        if (bsError["code"] == null)
                        {
                            code = "null";
                        }
                        else
                        {
                            code = bsError["code"].InnerText;
                        }

                        if (bsError["message"] == null)
                        {
                            message = "null";
                        }
                        else
                        {
                            message = bsError["message"].InnerText;
                        }

                    }
                    try
                    {
                        DA.InsertCommand = new SqlCommand("INSERT INTO response VALUES (@entityDBID, @vendorID, @submissionFilename, @fullName, @title, @number, @extension, @certificationDate, @dcn, @processDate, @successfullyProcessed, @code, @message);", DRDB);
                        DA.InsertCommand.Parameters.AddWithValue("@entityDBID", SqlDbType.NVarChar).Value = entityDBID;
                        DA.InsertCommand.Parameters.AddWithValue("@vendorID", SqlDbType.NVarChar).Value = vendorID;
                        DA.InsertCommand.Parameters.AddWithValue("@submissionFilename", SqlDbType.NVarChar).Value = submissionFilename;
                        DA.InsertCommand.Parameters.AddWithValue("@fullName", SqlDbType.NVarChar).Value = name;
                        DA.InsertCommand.Parameters.AddWithValue("@title", SqlDbType.NVarChar).Value = title;
                        DA.InsertCommand.Parameters.AddWithValue("@number", SqlDbType.NVarChar).Value = phoneNumber;
                        DA.InsertCommand.Parameters.AddWithValue("@extension", SqlDbType.NVarChar).Value = phoneExtension;
                        DA.InsertCommand.Parameters.AddWithValue("@certificationDate", SqlDbType.NVarChar).Value = certificationDate;
                        DA.InsertCommand.Parameters.AddWithValue("@dcn", SqlDbType.NVarChar).Value = dcn;
                        DA.InsertCommand.Parameters.AddWithValue("@processDate", SqlDbType.NVarChar).Value = processDate;
                        DA.InsertCommand.Parameters.AddWithValue("@successfullyProcessed", SqlDbType.NVarChar).Value = successfullyProcessed;
                        DA.InsertCommand.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = code;
                        DA.InsertCommand.Parameters.AddWithValue("@message", SqlDbType.NVarChar).Value = message;

                        DRDB.Open();
                        DA.InsertCommand.ExecuteNonQuery();
                        DRDB.Close();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }

                }


        }
    }

Also to make matters worse I am using Visual Studio 2005(C# 2.5). Any help would be appreciated.

I have this situation. My company wants me to create this app that reads and inserts data from a XML file that we get from another source. The part I am stuck on is that when I receive the XML file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<queryConfirmation xmlns="http://www.npdb-hipdb.hrsa.gov/QueryResponseCommon">
<submitter xmlns="">
    <entityDBID>800000000000001</entityDBID>
    <vendorID>1111514</vendorID>
</submitter>
<submissionFilename xmlns="">test.xml</submissionFilename>
<certification xmlns="">
    <name>JOE SMITH</name>
    <title>SUPERVISOR</title>
    <phone>
        <number>4075556647</number>
    </phone>
    <date>2008-02-26</date>
</certification>
<batchStatus xmlns="">
    <dcn>2000000000388120</dcn>
    <processDate>2014-04-03</processDate>
    <successfullyProcessed>false</successfullyProcessed>
    <error>
        <code>09</code>
        <message>09: This entity does not have the privilege to perform this transaction.</message>
    </error>
</batchStatus>
</queryConfirmation>

I can't insert the data into the database with my current code unless I take out the mlns="" and xmlns="http://www.npdb-hipdb.hrsa.gov/QueryResponseCommon" from the XML file manually. I feel like taking this information out of the file is wrong but the following is my code:

private void insertResponseXml()
    {

        OpenFileDialog ofd = new OpenFileDialog();
        ofd.Filter = "XML|*.xml";
        if (ofd.ShowDialog() == DialogResult.OK)
        {
            XmlDocument doc = new XmlDocument();
            doc.Load(ofd.FileName);




            XmlElement xelRoot = doc.DocumentElement;
            XmlNodeList rootNodes = xelRoot.SelectNodes("/queryConfirmation");
            XmlNodeList submitterNodes = xelRoot.SelectNodes("/queryConfirmation/submitter");
            XmlNodeList certNode = xelRoot.SelectNodes("/queryConfirmation/certification");
            XmlNodeList certPhoneNode = xelRoot.SelectNodes("/queryConfirmation/certification/phone");
            XmlNodeList batchNodeList = xelRoot.SelectNodes("/queryConfirmation/batchStatus");
            XmlNodeList bsErrorList = xelRoot.SelectNodes("/queryConfirmation/batchStatus/error");





                foreach (XmlNode xndNode in submitterNodes)
                {
                    entityDBID = xndNode["entityDBID"].InnerText;
                    vendorID = xndNode["vendorID"].InnerText;

                    foreach (XmlNode submisFilenameNode in rootNodes)
                    {
                        submissionFilename = submisFilenameNode["submissionFilename"].InnerText;

                    }

                    foreach (XmlNode cfn in certNode)
                    {
                        name = cfn["name"].InnerText;
                        title = cfn["title"].InnerText;
                        certificationDate = cfn["date"].InnerText;
                    }

                    foreach (XmlNode cfnp in certPhoneNode)
                    {
                        phoneNumber = cfnp["number"].InnerText;

                        if (cfnp["extension"] == null)
                        {
                            phoneExtension = "null";
                        }
                        else
                        {
                            phoneExtension = cfnp["extension"].InnerText;
                        }
                    }

                    foreach (XmlNode bsNode in batchNodeList)
                    {
                        dcn = bsNode["dcn"].InnerText;
                        processDate = bsNode["processDate"].InnerText;
                        successfullyProcessed = bsNode["successfullyProcessed"].InnerText;
                    }

                    foreach (XmlNode bsError in bsErrorList)
                    {
                        if (bsError["code"] == null)
                        {
                            code = "null";
                        }
                        else
                        {
                            code = bsError["code"].InnerText;
                        }

                        if (bsError["message"] == null)
                        {
                            message = "null";
                        }
                        else
                        {
                            message = bsError["message"].InnerText;
                        }

                    }
                    try
                    {
                        DA.InsertCommand = new SqlCommand("INSERT INTO response VALUES (@entityDBID, @vendorID, @submissionFilename, @fullName, @title, @number, @extension, @certificationDate, @dcn, @processDate, @successfullyProcessed, @code, @message);", DRDB);
                        DA.InsertCommand.Parameters.AddWithValue("@entityDBID", SqlDbType.NVarChar).Value = entityDBID;
                        DA.InsertCommand.Parameters.AddWithValue("@vendorID", SqlDbType.NVarChar).Value = vendorID;
                        DA.InsertCommand.Parameters.AddWithValue("@submissionFilename", SqlDbType.NVarChar).Value = submissionFilename;
                        DA.InsertCommand.Parameters.AddWithValue("@fullName", SqlDbType.NVarChar).Value = name;
                        DA.InsertCommand.Parameters.AddWithValue("@title", SqlDbType.NVarChar).Value = title;
                        DA.InsertCommand.Parameters.AddWithValue("@number", SqlDbType.NVarChar).Value = phoneNumber;
                        DA.InsertCommand.Parameters.AddWithValue("@extension", SqlDbType.NVarChar).Value = phoneExtension;
                        DA.InsertCommand.Parameters.AddWithValue("@certificationDate", SqlDbType.NVarChar).Value = certificationDate;
                        DA.InsertCommand.Parameters.AddWithValue("@dcn", SqlDbType.NVarChar).Value = dcn;
                        DA.InsertCommand.Parameters.AddWithValue("@processDate", SqlDbType.NVarChar).Value = processDate;
                        DA.InsertCommand.Parameters.AddWithValue("@successfullyProcessed", SqlDbType.NVarChar).Value = successfullyProcessed;
                        DA.InsertCommand.Parameters.AddWithValue("@code", SqlDbType.NVarChar).Value = code;
                        DA.InsertCommand.Parameters.AddWithValue("@message", SqlDbType.NVarChar).Value = message;

                        DRDB.Open();
                        DA.InsertCommand.ExecuteNonQuery();
                        DRDB.Close();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }

                }


        }
    }

Also to make matters worse I am using Visual Studio 2005(C# 2.5). Any help would be appreciated.

Source