title: Bulk insert in MySQL with stored procedure
body: <p>From my C#, I am creating datatable at run time. I want to pass this datatable into stored procedure so that data will insert together into the table.
  I am using MySQL as database. What is the MySQL equivalent of passing xml for multiple insert in SQL-Server?</p>
  
  <p>I generate XML from dataset and passed it to stored procedure:</p>
  
  <pre><code>CREATE DEFINER=`root`@`localhost` PROCEDURE `RouteLocationVehicleTimeInsert`(
  
      IN xml VARCHAR(1000000)
      )
  BEGIN
    SET @x=1;
    SET @val=xml;
      WHILE @x&lt;=4 DO
          SET @vehicleId=EXTRACTVALUE(@val, 'NewDataSet/Table1/vehicle_id[$@x]');
          SET @routeId = EXTRACTVALUE(@val, 'NewDataSet/Table1/route_id[$@x]');
          SET @locationId = EXTRACTVALUE(@val, 'NewDataSet/Table1/location_id[$@x]');
          SET @arrivalTime = EXTRACTVALUE(@val, 'NewDataSet/Table1/arrival_time[$@x]');   
      INSERT INTO `rm.routelocation_fm.vehicle_time`(`vehicle_id`,`route_id`,`location_id`,`arrival_time`)
      VALUES(@vehicleId,@routeId,@locationId,@arrivalTime);
      SET @x=@x+1;
     END WHILE;
  END@@
  </code></pre>
  
  <p>But it doesn't help. When i test the sql statement written in SP i gives multipl values in same column, like as below</p>
  
  <pre><code>SET @val='&lt;NewDataSet&gt;
  &lt;Table1&gt;
  &lt;vehicle_id&gt;1&lt;/vehicle_id&gt;
  &lt;route_id&gt;5&lt;/route_id&gt;
  &lt;location_id&gt;1&lt;/location_id&gt;
  &lt;arrival_time&gt;00:00&lt;/arrival_time&gt;
  &lt;/Table1&gt;
  &lt;Table1&gt;
      &lt;vehicle_id&gt;2&lt;/vehicle_id&gt;
      &lt;route_id&gt;5&lt;/route_id&gt;
      &lt;location_id&gt;2&lt;/location_id&gt;
      &lt;arrival_time&gt;00:00&lt;/arrival_time&gt;
    &lt;/Table1&gt;&lt;/NewDataSet&gt;';
  SET @x=1;
  
          SET @vehicle_id= EXTRACTVALUE(@val, 'NewDataSet/Table1/vehicle_id[1]');
          SELECT @vehicle_id;
  </code></pre>
  
  <p>it gives result as 1  1 in same column. where they are two records.</p>