title: Loop through xml elements using ssis script task, insert into sql table using stored procedure
body: <p>I am trying to use SSIS calling a stored procedure to insert the values from a XML file into a SQL Server table, but I am having trouble because there can be multiple elements that I need a loop.</p>
  
  <p>XML file:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="ISO-8859-1"?&gt; 
  &lt;Order xmlns="wpa-net:2000:Order:xdr:1.0"&gt;   
     &lt;OrderHead&gt;   
        &lt;Order&gt;
           &lt;OrderNo&gt;123456&lt;/OrderNo&gt;
           &lt;Address&gt;
              &lt;AddressLine&gt;Test Road&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Test2&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Test3&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Test4&lt;/AddressLine&gt;
              &lt;PostCode&gt;SE1 1OB&lt;/PostCode&gt;
           &lt;/Address&gt;   
        &lt;/Order&gt;   
        &lt;DespatchLine&gt;
            &lt;LineNumber&gt;1&lt;/LineNumber&gt;
            &lt;DespatchLineReferences&gt;
               &lt;CustomersOrderReference&gt;012345&lt;/CustomersOrderReference&gt;
            &lt;/DespatchLineReferences&gt;
            &lt;Product&gt;
               &lt;SuppliersProductCode&gt;00111&lt;/SuppliersProductCode&gt;
               &lt;ConsumerUnitCode&gt;1&lt;/ConsumerUnitCode&gt;
               &lt;Description&gt;Test product&lt;/Description&gt;
            &lt;/Product&gt;   
         &lt;/DespatchLine&gt;   
         &lt;DespatchLine&gt;
            &lt;LineNumber&gt;2&lt;/LineNumber&gt;
            &lt;DespatchLineReferences&gt;
               &lt;CustomersOrderReference&gt;012345&lt;/CustomersOrderReference&gt;
            &lt;/DespatchLineReferences&gt;
            &lt;Product&gt;
               &lt;SuppliersProductCode&gt;00111&lt;/SuppliersProductCode&gt;
               &lt;ConsumerUnitCode&gt;1&lt;/ConsumerUnitCode&gt;
               &lt;Description&gt;Test product&lt;/Description&gt;
            &lt;/Product&gt;   
         &lt;/DespatchLine&gt;   
         &lt;DespatchLine&gt;
            &lt;LineNumber&gt;3&lt;/LineNumber&gt;
            &lt;DespatchLineReferences&gt;
               &lt;CustomersOrderReference&gt;012345&lt;/CustomersOrderReference&gt;
            &lt;/DespatchLineReferences&gt;
            &lt;Product&gt;
               &lt;SuppliersProductCode&gt;00111&lt;/SuppliersProductCode&gt;
               &lt;ConsumerUnitCode&gt;1&lt;/ConsumerUnitCode&gt;
               &lt;Description&gt;Test product&lt;/Description&gt;
            &lt;/Product&gt;  
         &lt;/DespatchLine&gt;   
     &lt;/OrderHead&gt; 
  &lt;/Order&gt;
  </code></pre>
  
  <p>The issue I am having is that the file can contain a number of <code>DespatchLine</code> elements, in the example above there is 3 however the number of lines on a order could vary. So far I have managed to insert data but I cant get it to loop through the despatchline elements.</p>
  
  <pre><code>public void Main()
  {
      //Set Reference to database class
      WriteToDatabase db = new WriteToDatabase();
  
      string filename = Dts.Variables["xmlTest"].Value.ToString();
      //MessageBox.Show(filename);
  
      XmlDataDocument xmldoc = new XmlDataDocument();
      XmlNodeList xmlnode;
  
      //XmlNodeList xmlnodeDespatchReference;
      int i = 0;
      string str = null;
  
      string orderNo = "";
      string buyerAddressLine1 = "";
      string buyerAddressLine2 = "";
      string buyerAddressLine3 = "";
      string buyerAddressLine4 = "";
      string buyerPostCode = "";
      string lineNumber = "";
      string customersOrderReference = "";
      string suppliersProductCode = "";
      string consumerUnitCode = "";
      string description = "";
  
      // Open the filestream
      FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
  
      //Load the XML file for reading
      xmldoc.Load(fs);
  
      XmlNamespaceManager nsmgr = new XmlNamespaceManager(xmldoc.NameTable);
  
      string nameSpace = (@"urn:wpa-net:2000:Order:xdr:1.0");
      nsmgr.AddNamespace("ns", nameSpace);
  
      xmlnode = xmldoc.GetElementsByTagName("Order");
  
      for (i = 0; i &lt;= xmlnode.Count - 1; i++)
      {
          orderNo = "";
          buyerAddressLine1 = "";
          buyerAddressLine2 = "";
          buyerAddressLine3 = "";
          buyerAddressLine4 = "";
          buyerPostCode = "";
          lineNumber = "";
          customersOrderReference = "";
          suppliersProductCode = "";
          consumerUnitCode = "";
          description = "";
  
          // OrderNo
          XmlNodeList xmlOrderNodes = xmlnode[i].SelectSingleNode("descendant::ns:Order", nsmgr).ChildNodes;
  
          foreach (XmlNode xmlOrderNode in xmlOrderNodes)
          {
              switch (xmlOrderNode.Name)
              {
                  case "OrderNo":
                      orderNo = xmlOrderNode.InnerText;
                      break;
              }
          }
  
          // Address
          XmlNodeList xmlAddressNodes = xmlnode[i].SelectSingleNode("descendant::ns:Order/ns:Address", nsmgr).ChildNodes;
  
          foreach (XmlNode xmlAddressNode in xmlAddressNodes)
          {
              switch (xmlAddressNode.Name)
              {
                  case "AddressLine": 
                      if (buyerAddressLine1 == "" &amp;&amp; xmlAddressNode.InnerText.Length &gt;= 1)
                      {
                          buyerAddressLine1 = xmlAddressNode.InnerText;
                          break;
                      }
  
                      if (buyerAddressLine2 == "" &amp;&amp; xmlAddressNode.InnerText.Length &gt;= 1)
                      {
                          buyerAddressLine2 = xmlAddressNode.InnerText;
                          break;
                      }
  
                      if (buyerAddressLine3 == "" &amp;&amp; xmlAddressNode.InnerText.Length &gt;= 1)
                      {
                          buyerAddressLine3 = xmlAddressNode.InnerText;
                          break;
                      }
  
                      if (buyerAddressLine4 == "" &amp;&amp; xmlAddressNode.InnerText.Length &gt;= 1)
                      {
                          buyerAddressLine4 = xmlAddressNode.InnerText;
                          break;
                      }
  
                      break;
  
                  case "PostCode":
                      buyerPostCode = xmlAddressNode.InnerText;
                      break;
              }
          }
  
          // To Do loop through each DespatchLine element
          // DespatchLine
          XmlNodeList xmlDespatchLineNodes = xmlnode[i].SelectSingleNode("descendant::ns:DespatchLine", nsmgr).ChildNodes;
  
          foreach (XmlNode xmlDespatchLineNode in xmlDespatchLineNodes)
          {
              switch (xmlDespatchLineNode.Name)
              {
                  case "LineNumber":
                      lineNumber = xmlDespatchLineNode.InnerText;
                      break;
              }                    
          }
  
          XmlNodeList xmlDespatchLineReferenceNodes = xmlnode[i].SelectSingleNode("descendant::ns:DespatchLine/ns:DespatchLineReferences", nsmgr).ChildNodes;
  
          foreach (XmlNode xmlDespatchLineReferenceNode in xmlDespatchLineReferenceNodes)
          {
              switch (xmlDespatchLineReferenceNode.Name)
              {
                  case "CustomersOrderReference":
                      customersOrderReference = xmlDespatchLineReferenceNode.InnerText;
                      break;
              }
          }
  
          XmlNodeList xmlDespatchLineProductNodes = xmlnode[i].SelectSingleNode("descendant::ns:DespatchLine/ns:Product", nsmgr).ChildNodes;
  
          foreach (XmlNode xmlDespatchLineProductNode in xmlDespatchLineProductNodes)
          {
              switch (xmlDespatchLineProductNode.Name)
              {
                  case "SuppliersProductCode":
                     suppliersProductCode = xmlDespatchLineProductNode.InnerText;
                     break;
  
                  case "ConsumerUnitCode":
                     consumerUnitCode = xmlDespatchLineProductNode.InnerText;
                     break;
  
                  case "Description":
                     description = xmlDespatchLineProductNode.InnerText;
                     break;
              }
          }
  
          Debug.WriteLine("Write to the database");
          db.UpdateDatabase(orderNo,
                  buyerAddressLine1, buyerAddressLine2,
                  buyerAddressLine3, buyerAddressLine4,
                  buyerPostCode,
                  lineNumber,
                  customersOrderReference,
                  suppliersProductCode,
                  consumerUnitCode,
                  description);
      }
  
      fs.Close();  // Close the connection so no processes are left open.
  }
  
  public class WriteToDatabase
  {
      public void UpdateDatabase
      (
                  string orderNo,
                  string buyerAddressLine1,
                  string buyerAddressLine2,
                  string buyerAddressLine3,
                  string buyerAddressLine4,
                  string buyerPostCode,
                  string lineNumber,
                  string customersOrderReference,
                  string suppliersProductCode,
                  string consumerUnitCode,
                  string description
              )
          {
              string sqConString = "data source=TestBox;initial catalog=TestPlay;integrated security=True";
              string sqCommand = "spPopulateTest";
              using (SqlConnection sqCon = new SqlConnection(sqConString))
              {
                  sqCon.Open();
                  {
                      using (SqlCommand sqCom = new SqlCommand())
                      {
                          sqCom.CommandText = sqCommand;
                          sqCom.CommandType = CommandType.StoredProcedure;
  
                          sqCom.Parameters.Add(new SqlParameter("OrderNo", orderNo));
  
                          sqCom.Parameters.Add(new SqlParameter("BuyerAddressLine1", buyerAddressLine1));
                          sqCom.Parameters.Add(new SqlParameter("BuyerAddressLine2", buyerAddressLine2));
                          sqCom.Parameters.Add(new SqlParameter("BuyerAddressLine3", buyerAddressLine3));
                          sqCom.Parameters.Add(new SqlParameter("BuyerAddressLine4", buyerAddressLine4));
                          sqCom.Parameters.Add(new SqlParameter("BuyerPostCode", buyerPostCode));
  
                          sqCom.Parameters.Add(new SqlParameter("LineNumber", lineNumber));
                          sqCom.Parameters.Add(new SqlParameter("CustomersOrderReference", customersOrderReference));
  
                          sqCom.Parameters.Add(new SqlParameter("SuppliersProductCode", suppliersProductCode));
                          sqCom.Parameters.Add(new SqlParameter("ConsumerUnitCode", consumerUnitCode));
                          sqCom.Parameters.Add(new SqlParameter("Description", description));
  
                          sqCom.Connection = sqCon;
                          sqCom.ExecuteNonQuery();
                      }
                  }
                  sqCon.Close();      
              }
          }
  </code></pre>