Bulk Insert XML as IDataReader with SqlBulkCopy

[shriop]

Here's a code example of how to bulk insert xml data as an IDataReader
into Sql Server using DataStreams, http://www.csvreader.com/ , and
SqlBulkCopy.

format of products.xml:

<products>
<product>
<productID>1</productID>
<productName>Chai</productName>
<supplierID>1</supplierID>
<categoryID>1</categoryID>
<quantityPerUnit>10 boxes x 20 bags</quantityPerUnit>
<unitPrice>18</unitPrice>
<unitsInStock>39</unitsInStock>
<unitsOnOrder>0</unitsOnOrder>
<reorderLevel>10</reorderLevel>
<discontinued>False</discontinued>
</product>
<product>
<productID>2</productID>
<productName>Chang</productName>
<supplierID>1</supplierID>
<categoryID>1</categoryID>
<quantityPerUnit>24 - 12 oz bottles</quantityPerUnit>
<unitPrice>19</unitPrice>
<unitsInStock>17</unitsInStock>
<unitsOnOrder>40</unitsOnOrder>
<reorderLevel>25</reorderLevel>
<discontinued>False</discontinued>
</product>
</products>

destination table sql definition:

CREATE TABLE dbo.Products
(
ProductID int NOT NULL,
ProductName nvarchar(40) NOT NULL,
SupplierID int NULL,
CategoryID int NULL,
QuantityPerUnit nvarchar(20) NULL,
UnitPrice money NULL,
UnitsInStock smallint NULL,
UnitsOnOrder smallint NULL,
ReorderLevel smallint NULL,
Discontinued bit NOT NULL
)

c# code example:

using System;
using System.Data.SqlClient;

using DataStreams.Xml;

namespace XmlIDataReader
{
class Program
{
static void Main(string[] args)
{
using (XmlDataReader reader = new
XmlDataReader("products.xml", "/product"))
using (SqlBulkCopy bcp = new
SqlBulkCopy(Properties.Settings.Default.ConnectionString))
{
reader.Columns.Add("varchar", "productID",
"ProductID");
reader.Columns.Add("varchar", "productName",
"ProductName");
reader.Columns.Add("varchar", "supplierID",
"SupplierID");
reader.Columns.Add("varchar", "categoryID",
"CategoryID");
reader.Columns.Add("varchar", "quantityPerUnit",
"QuantityPerUnit");
reader.Columns.Add("varchar", "unitPrice",
"UnitPrice");
reader.Columns.Add("varchar", "unitsInStock",
"UnitsInStock");
reader.Columns.Add("varchar", "unitsOnOrder",
"UnitsOnOrder");
reader.Columns.Add("varchar", "reorderLevel",
"ReorderLevel");
reader.Columns.Add("varchar", "discontinued",
"Discontinued");

bcp.DestinationTableName = "Products";

bcp.WriteToServer(reader);
}
}
}
}