title: How to insert multiple rows from xml document to sql server using XmlReader
body: <p>Of course, I googled my problem : "visual basic xml to sql how to insert many rows XmlReader". I even changed 'many' to 'multiple'.</p>
  
  <p>So far, I was able to add only the <strong>first</strong> entry into my database.
  I was happy to be able to use XmlReader, since I heard it's quite efficient.
  I was happy as well to be able to use parameters, since I can't concatenate properly anyway (and yes, I like to code elegantly - I do not talk about my clothes here).</p>
  
  <p>My VB.Net code:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Imports System.Data.SqlClient
  Imports System.Xml
  
  Public Class Form1
  Private Sub ButtonOK_Click(sender As Object, e As EventArgs) Handles ButtonOK.Click        
      Dim xmlFile As String           
      Dim connectionString As String  
      Dim connection As SqlConnection 
      Dim command As SqlCommand       
      Dim ds As New DataSet           
      Dim reader As XmlReader         
      Dim sqlStatement As String      
  
      'Tag names inside xml document, will have to be inserted into table
      Dim company As Integer
      Dim name As String
      Dim streetaddress As String
      Dim city As String        
      Dim status As Char
  
      connectionString = "this connection string works"
      xmlFile = "MyXMLFile.xml"
      connection = New SqlConnection(connectionString)
      reader = XmlReader.Create(xmlFile, New XmlReaderSettings())
      ds.ReadXml(reader)
  
      Dim doc As New XmlDocument()
      doc.Load(xmlFile)
      Dim nodelist As XmlNodeList = doc.SelectNodes(".//siteelement/site")
      For Each node As XmlElement In nodelist
  
          Dim i As Integer
          For i = 0 To ds.Tables(0).Rows.Count - 1
              company = Convert.ToInt32(ds.Tables(0).Rows(i).Item(0))                
              name = ds.Tables(0).Rows(i).Item(1)
              streetaddress = ds.Tables(0).Rows(i).Item(2)                
              city = ds.Tables(0).Rows(i).Item(3)                
              status = ds.Tables(0).Rows(i).Item(4)
  
              sqlStatement = "INSERT INTO [dbo].[SITE] ([COMPANY], [NAME], [STREETADDRESS], [CITY], [STATUS])" &amp;
              "VALUES (@COMPANY, @NAME, @STREETADDRESS, @CITY, @STATUS)"
              command = New SqlCommand(sqlStatement, connection)
              command.Parameters.AddWithValue("@COMPANY", company)                
              command.Parameters.AddWithValue("@NAME", name)
              command.Parameters.AddWithValue("@STREETADDRESS", streetaddress)
              command.Parameters.AddWithValue("@CITY", city)                
              command.Parameters.AddWithValue("@STATUS", status)
              Try
                  connection.Open()
                  Dim rowsAffected As Integer = command.ExecuteNonQuery()
                  MessageBox.Show(rowsAffected &amp; " entries were inserted into Site table")
                  'Release the resources
                  command.Dispose()
              Catch ex As Exception
                  MessageBox.Show(ex.Message)
              End Try
              'Execute the sql statement against the connection
              command.ExecuteNonQuery()
          Next
      Next
      connection.Close()
  End Sub
  End Class
  </code></pre>
  
  <p>What my xml file look like:</p>
  
  <pre class="lang-xml prettyprint-override"><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;siteelement&gt;
  &lt;site&gt;
    &lt;company&gt;1000&lt;/company&gt;   
    &lt;name&gt;STORE #2336&lt;/name&gt;
    &lt;streetAddress&gt;123 London Calling&lt;/streetAddress&gt; 
    &lt;city&gt;London&lt;/city&gt;   
    &lt;status&gt;I&lt;/status&gt;
  &lt;/site&gt;
  
  &lt;site&gt;
    &lt;company&gt;1001&lt;/company&gt;   
    &lt;name&gt;STORE #2332&lt;/name&gt;
    &lt;streetAddress&gt;123 City of New Orleans&lt;/streetAddress&gt;    
    &lt;city&gt;New Orleans&lt;/city&gt;  
    &lt;status&gt;A&lt;/status&gt;
  &lt;/site&gt;
  
  &lt;site&gt;
    &lt;company&gt;1002&lt;/company&gt;   
    &lt;name&gt;STORE #2333&lt;/name&gt;
    &lt;streetAddress&gt;123 Bla bla bla&lt;/streetAddress&gt;
    &lt;city&gt;Somewhere&lt;/city&gt;    
    &lt;status&gt;A&lt;/status&gt;
  &lt;/site&gt;
  &lt;/siteelement&gt;
  </code></pre>
  
  <p>Question : How can I make my reader reads through all my xml document? What did I miss? What is incorrect?</p>
  
  <p><strong>Any</strong> help would be appreciated. Please notice I am completely new to Visual Basic (less than a week!) and if you think I can betterize my code...</p>
  
  <p>Thanks in advance.</p>