title: Import data from XML to SQL Server tables
body: <p>I have problem with importing data from xml into SQL Server tables. </p>
  
  <p>This example of my XML file:</p>
  
  <pre><code>&lt;ORDER&gt;
    &lt;ORDER_HEADER&gt;
      &lt;NUMBER&gt;109&lt;/NUMBER&gt;
    &lt;/ORDER_HEADER&gt;
    &lt;CUSTOMER_HEADER&gt;
      &lt;CUSTOMER&gt;Michael&lt;/CUSTOMER&gt;    
    &lt;/CUSTOMER_HEADER&gt;
    &lt;ORDER_ITEMS&gt;
      &lt;ITEM&gt;
        &lt;CATALOG_NUMBER&gt;2&lt;/CATALOG_NUMBER&gt;
        &lt;VAT&gt;21&lt;/VAT&gt;
      &lt;/ITEM&gt;
      &lt;ITEM&gt;
        &lt;CATALOG_NUMBER&gt;5&lt;/CATALOG_NUMBER&gt;
        &lt;VAT&gt;21&lt;/VAT&gt;
      &lt;/ITEM&gt;
      &lt;ITEM&gt;
        &lt;CATALOG_NUMBER&gt;7&lt;/CATALOG_NUMBER&gt;
        &lt;VAT&gt;21&lt;/VAT&gt;
      &lt;/ITEM&gt;
      &lt;ITEM&gt;
        &lt;CATALOG_NUMBER&gt;9&lt;/CATALOG_NUMBER&gt;
        &lt;VAT&gt;21&lt;/VAT&gt;
      &lt;/ITEM&gt;
    &lt;/ORDER_ITEMS&gt;
  &lt;/ORDER&gt;
  </code></pre>
  
  <p>And this is my c# code:</p>
  
  <pre><code>XDocument doc = XDocument.Load("C:\\Users\\L\\order.xml");
  var NUMBER = doc.Descendants("NUMBER");                
  var CUSTOMER = doc.Descendants("CUSTOMER");
  var CATALOG_NUMBER = doc.Descendants("CATALOG_NUMBER");
  var VAT = doc.Descendants("VAT");
  
  SqlConnection conn = new SqlConnection("*****");
     conn.Open();
        foreach (var cislo in NUMBER)
          {
            using (SqlCommand cmd = conn.CreateCommand())
              {
                 cmd.CommandText="Insert INTO ORDER_HEADER(NUMBER) VALUES (@cislo);";
                 cmd.Parameters.AddWithValue("@cislo",cislo.Value);
                 cmd.ExecuteNonQuery();
                 cmd.Clone();
               }
          }
        foreach (var zakaznik in CUSTOMER)
          {
             using (SqlCommand cmd = conn.CreateCommand())
               {
                 cmd.CommandText="Insert INTO CUSTOMER_HEAD(CUSTOMER)VALUES(@zakaznik);";
                 cmd.Parameters.AddWithValue("@zakaznik", zakaznik.Value);
                 cmd.ExecuteNonQuery();
                 cmd.Clone();
                }
          }
        foreach (var katalo_cislo in CATALOG_NUMBER)
          foreach (var vat1 in VAT)
             {
                using (SqlCommand cmd = conn.CreateCommand())
                   {
                      cmd.CommandText = "Insert INTO ITEM (CATALOG_NUMBER,VAT) VALUES (@katalo_cislo,@vat1);";
                      cmd.Parameters.AddWithValue("@katalo_cislo", katalo_cislo.Value);
                      cmd.Parameters.AddWithValue("@vat1", vat1.Value);
                      cmd.ExecuteNonQuery();
                      cmd.Clone();
                    }
             }
      conn.Close();
  </code></pre>
  
  <p>But first 2 sql tables is OK, but in last sql table ITEM is more than 4 record...problem is probably in  two FOREACH... but how could I load records from <code>ORDER_ITEMS</code> right? </p>
  
  <p>Thanks</p>
  
  <p>This is my code now with all my values:
  ¨</p>
  
  <pre><code>var Items = doc.Descendants("ITEM").Select(x =&gt; new { NUMBER = (int?)x.Element("NUMBER"), CATALOG_NUMBER = (string)x.Element("CATALOG_NUMBER"), ITEM_NAME = (string)x.Element("ITEM_NAME"), UNIT = (string)x.Element("UNIT"), AMOUNT = (int?)x.Element("AMOUNT"), PRICE_WITHOUT_VAT = (string)x.Element("PRICE_WITHOUT_VAT"), VAT = (string)x.Element("VAT"), PRICE_VAT = (string)x.Element("PRICE_VAT"), EAN = (string)x.Element("EAN"), SUPPLIER_ITEM_NUMBER = (string)x.Element("SUPPLIER_ITEM_NUMBER"), SUPPLIER_ID = (string)x.Element("SUPPLIER_ID"), SUPPLIER_NAME = (string)x.Element("SUPPLIER_NAME"), ORDER_ITEMS_Id = (string)x.Element("ORDER_ITEMS_Id"), }).ToList();
                  foreach (var item in Items)
                                              {
                                                  using (SqlCommand cmd = conn.CreateCommand())
                                                  {
                                                      cmd.CommandText = "Insert INTO ITEM (NUMBER,CATALOG_NUMBER,ITEM_NAME,UNIT,AMOUNT,PRICE_WITHOUT_VAT,VAT,PRICE_VAT,EAN,SUPPLIER_ITEM_NUMBER,SUPPLIER_ID,SUPPLIER_NAME,ORDER_ITEMS_Id) VALUES (@cislo,@katalo_cislo,@nazev_zbozi,@jednotka,@mnozstvi,@cenabezvat,@vat1,@cenavat,@ean1,@dodav_cislo,@dodav_id,@dodav_jmeno,@objednavkaid);";
                                                      cmd.Parameters.AddWithValue("@cislo", item.NUMBER);
                                                      cmd.Parameters.AddWithValue("@katalo_cislo", item.CATALOG_NUMBER);
                                                      cmd.Parameters.AddWithValue("@nazev_zbozi", item.ITEM_NAME);
                                                      cmd.Parameters.AddWithValue("@jednotka", item.UNIT);
                                                      cmd.Parameters.AddWithValue("@mnozstvi", item.AMOUNT);
                                                      cmd.Parameters.AddWithValue("@cenabezvat", item.PRICE_WITHOUT_VAT);
                                                      cmd.Parameters.AddWithValue("@vat1", item.VAT);
                                                      cmd.Parameters.AddWithValue("@cenavat", item.PRICE_VAT);
                                                      cmd.Parameters.AddWithValue("@ean1", item.EAN);
                                                      cmd.Parameters.AddWithValue("@dodav_cislo", item.SUPPLIER_ITEM_NUMBER);
                                                      cmd.Parameters.AddWithValue("@dodav_id", item.SUPPLIER_ID);
                                                      cmd.Parameters.AddWithValue("@dodav_jmeno", item.SUPPLIER_NAME);
                                                      cmd.Parameters.AddWithValue("@objednavkaid", item.ORDER_ITEMS_Id);
                                                      cmd.ExecuteNonQuery();
                                                      //cmd.Clone();
                                                  }
                                              }
  </code></pre>