title: Trouble with inserting specific xml nodes into a table
body: <p>I am very new to xml and I am 95% done with my project for work, But when I try and do the last insert into a table it always duplicates my one value.</p>
  
  <p>For example if I have transactions that needs to be inserted into the table and all the transactions are enclosed in a stage node. each stage has transactions.</p>
  
  <p>Now when I insert stage 1 and it has 5 transactions it must take the id of stage 1 then when the insert is done it must then go to stage 2 and then insert 10 transactions.</p>
  
  <p>My issue is when I insert the transactions it will only take the first stage id for all the trans inserts.</p>
  
  <p>Here is my method how I am inserting the transactions</p>
  
  <pre><code>private List&lt;XMLTransaction&gt; GetAllTransForStage(List&lt;XElement&gt; allTransDetails, string path, int curStage, int nxtStage, int journeyEnd, int IDStage, int IDJourney, int IDDuty, int IDModule, string BoardingStages)
      {
          var end = nxtStage &gt; 0 ? nxtStage : journeyEnd;
          var doc = XDocument.Load(path);
          List&lt;XElement&gt; StageRersult = GetTranseDetails(doc);
          var transForCurrentStage = StageRersult.Where(s =&gt; Int32.Parse(s.Attribute("Position").Value.ToString()) &gt; curStage &amp;&amp; Int32.Parse(s.Attribute("Position").Value.ToString()) &lt; end).ToList();
          var transactions = new List&lt;XMLTransaction&gt;();
  
  
          string TripBal = "";
          string serial = "";
          int IDTranse = 0;
          int hexClassValue = 0;
          int second = 0;
          double seconds = 0;
          TimeSpan t;
          string formatedTime = "";
          string date = "";
          string issueDate = "";
          string issueTime = "";
          int stageno = 0;
          int HexClasValue = 0;
          int Fare = 0;
  
          DataTable dt4 = new DataTable();
          dt4.Columns.AddRange(new DataColumn[17] {
                  new DataColumn("id_Trans",typeof(int)),
                  new DataColumn("id_Stage", typeof(int)),
                  new DataColumn("id_Journey", typeof(int)),
                  new DataColumn("id_Duty",typeof(int)),
                  new DataColumn("id_Module",typeof(int)),
                  new DataColumn("int2_BoardingStageID",typeof(int)),
                  new DataColumn("int2_AlightingStageID",typeof(int)),
                  new DataColumn("int2_Class",typeof(int)),
                  new DataColumn("int4_Revenue",typeof(int)),
                  new DataColumn("int4_NonRevenue",typeof(int)),
                  new DataColumn("int2_TicketCount",typeof(int)),
                  new DataColumn("int2_PassCount",typeof(int)),
                  new DataColumn("int2_Transfers",typeof(int)),
                  new DataColumn("dat_TransDate",typeof(DateTime)),
                  new DataColumn("dat_TransTime",typeof(DateTime)),
                  new DataColumn("str_SerialNumber",typeof(string)),
                  new DataColumn("int4_TripBal",typeof(int))
              });
  
  
          int batchID = Convert.ToInt32(dtImportDateTime.ToString("yyyyMMdd"));
          XDocument xdoc3 = XDocument.Load(path);
          XDocument xdoc4 = XDocument.Load(path);
          //////////////
          string ConString = Settings.Default.ebusimporterConnectionString;
          using (SqlConnection con = new SqlConnection(ConString))
          {
  
              string sql = "SELECT TOP 1 id_Trans FROM Trans ORDER BY 1 DESC";
              SqlCommand cmd = new SqlCommand(sql, con);
              try
              {
                  con.Open();
  
                  object val = cmd.ExecuteScalar();
                  if (val != null)
                  {
                      IDTranse = Convert.ToInt32(val);
                      IDTranse = IDTranse + 1;
                  }
                  else if (val == null)
                  {
                      IDTranse = Convert.ToInt32(val);
                      IDTranse = IDTranse + 1;
                  }
  
              }
              catch (Exception ex)
              {
                  Console.WriteLine(ex.Message);
              }
          }
  
  
          foreach (var tran in transForCurrentStage)
          {
  
  
              string Hex = tran.Element("TicketType").Value.ToString();
              string stageN = tran.Element("StageNo").Value.ToString();
              string Issuetime = tran.Element("IssueTime").Value.ToString();
              string fares = tran.Element("Fare").Value.ToString();
              string Issuedate = tran.Element("IssueDate").Value.ToString();
              string TicketSerialNo = tran.Element("TicketSerialNo").Value.ToString();
  
  
  
  
  
              //  string TicketType = item.TicketType.ToString();
  
              hexClassValue = int.Parse(Hex, NumberStyles.HexNumber);
              stageno = Int32.Parse(stageN);
              HexClasValue = hexClassValue;
              second = Int32.Parse(Issuetime);
              seconds = second;
              t = TimeSpan.FromSeconds(seconds);
              formatedTime = string.Format("{0:D2}:{1:D2}:{2:D2}",
                        t.Hours,
                        t.Minutes,
                         t.Seconds);
              Fare = Int32.Parse(fares);
              string dateLength = Issuedate;
  
              if (dateLength.Length == 5 &amp;&amp; HexClasValue == 17 || HexClasValue == 18 || HexClasValue == 33 || HexClasValue == 34 || HexClasValue == 65 || HexClasValue == 66)
              {
                  date = "0" + Issuedate;
                  issueDate = DateTime.ParseExact(date, "dMMyy", null).ToString("yyyy-MM-d");
                  issueTime = issueDate + " " + formatedTime;
  
                  //Cash
                  if (HexClasValue == 17 || HexClasValue == 18 || HexClasValue == 33 || HexClasValue == 34 || HexClasValue == 65 || HexClasValue == 66)
                  {
  
                      dt4.Rows.Add(IDTranse, IDStage, IDJourney, IDDuty, IDModule, Convert.ToInt16(BoardingStages), Convert.ToInt16(stageN), Convert.ToInt16(HexClasValue), Fare, 0, 1, 0, 0, Convert.ToDateTime(issueDate), Convert.ToDateTime(issueTime), TicketSerialNo, Convert.ToInt32(0));
                      IDTranse++;
  
                  }
              }
  
              if (dateLength.Length == 6 &amp;&amp; HexClasValue == 17 || HexClasValue == 18 || HexClasValue == 33 || HexClasValue == 34 || HexClasValue == 65 || HexClasValue == 66)
              {
                  date = Issuedate;
                  issueDate = DateTime.ParseExact(date, "dMMyy", null).ToString("yyyy-MM-d");
                  issueTime = issueDate + " " + formatedTime;
  
                  //Cash
                  if (HexClasValue == 17 || HexClasValue == 18 || HexClasValue == 33 || HexClasValue == 34 || HexClasValue == 65 || HexClasValue == 66)
                  {
  
                      dt4.Rows.Add(IDTranse, IDStage, IDJourney, IDDuty, IDModule, Convert.ToInt16(BoardingStages), Convert.ToInt16(stageN), Convert.ToInt16(HexClasValue), Fare, 0, 1, 0, 0, Convert.ToDateTime(issueDate), Convert.ToDateTime(issueTime), TicketSerialNo, Convert.ToInt32(0));
                      IDTranse++;
  
                  }
              }
          }
  
          string ConnString4 = Settings.Default.ebusimporterConnectionString;
          if (HexClasValue == 11000)
          {
  
          }
          else if (dt4.Rows.Count != 0)
          {
  
              using (SqlConnection con4 = new SqlConnection(ConnString4))
              {
                  using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con4))
                  {
  
                      //revenuebal  = 0 
                      sqlBulkCopy.DestinationTableName = "dbo.Trans";
  
                      sqlBulkCopy.ColumnMappings.Add("id_Trans", "id_Trans");
                      sqlBulkCopy.ColumnMappings.Add("id_Stage", "id_Stage");
                      sqlBulkCopy.ColumnMappings.Add("id_Journey", "id_Journey");
                      sqlBulkCopy.ColumnMappings.Add("id_Duty", "id_Duty");
                      sqlBulkCopy.ColumnMappings.Add("id_Module", "id_Module");
                      sqlBulkCopy.ColumnMappings.Add("int2_BoardingStageID", "int2_BoardingStageID");
                      sqlBulkCopy.ColumnMappings.Add("int2_AlightingStageID", "int2_AlightingStageID");
                      sqlBulkCopy.ColumnMappings.Add("int2_Class", "int2_Class");
                      sqlBulkCopy.ColumnMappings.Add("int4_Revenue", "int4_Revenue");
                      sqlBulkCopy.ColumnMappings.Add("int4_NonRevenue", "int4_NonRevenue");
                      sqlBulkCopy.ColumnMappings.Add("int2_TicketCount", "int2_TicketCount");
                      sqlBulkCopy.ColumnMappings.Add("int2_PassCount", "int2_PassCount");
                      sqlBulkCopy.ColumnMappings.Add("int2_Transfers", "int2_Transfers");
                      sqlBulkCopy.ColumnMappings.Add("dat_TransDate", "dat_TransDate");
                      sqlBulkCopy.ColumnMappings.Add("dat_TransTime", "dat_TransTime");
                      sqlBulkCopy.ColumnMappings.Add("str_SerialNumber", "str_SerialNumber");
                      sqlBulkCopy.ColumnMappings.Add("int4_TripBal", "int4_TripBal");
  
                      con4.Open();
                      sqlBulkCopy.WriteToServer(dt4);
                      con4.Close();
  
                  }
              }
          }
          LogwriterSuccess("ImportTrans", path);
  
          return transactions;
      }
  </code></pre>