Hi Thomas,
Use an XML schema file that describes the XML data file. The schema file is named Customers.xsd.The solution expects this file to be in the same directory as the solution file BulkLoad.sln. Here's an example of Customers.xsd :
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ROOT" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="Customers" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="Customers" sql:relation="Customers">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:string"
sql:datatype="nvarchar(5)" />
<xsd:element name="CompanyName" type="xsd:string"
sql:datatype="nvarchar(40)" />
<xsd:element name="ContactName" type="xsd:string"
sql:datatype="nvarchar(30)" />
<xsd:element name="ContactTitle"
type="xsd:string"
sql:datatype="nvarchar(30)" />
<xsd:element name="Address" type="xsd:string"
sql:datatype="nvarchar(60)" />
<xsd:element name="City" type="xsd:string"
sql:datatype="nvarchar(15)" />
<xsd:element name="Region" type="xsd:string"
sql:datatype="nvarchar(15)" />
<xsd:element name="PostalCode" type="xsd:string"
sql:datatype="nvarchar(10)" />
<xsd:element name="Country" type="xsd:string"
sql:datatype="nvarchar(15)" />
<xsd:element name="Phone" type="xsd:string"
sql:datatype="nvarchar(24)" />
<xsd:element name="Fax" type="xsd:string"
sql:datatype="nvarchar(24)" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Use an XML file that contains the data to be loaded. The file is named Customers.xml. Then creates a bulk load object SQLXMLBulkLoad and sets the connection string and error log file for the object. The Execute() method of the SQLXMLBulkLoad object wil be used to bulk load the Customers data from the XML file into the Customers table in the Northwind database. The Customers table must be empty prior to running this sample or a primary key constraint error will be raised and written to the error log.You need a reference to the SQLXML Bulk Load 4.0 COM object. Add a reference to the Microsoft SQLXML BulkLoad 4.0 Type Library from the COM tab in Visual Studio .NET's Add Reference Dialog. If it is not listed, browse for and add its DLL named xblkld4.dll—it should be in the directory c:\Program Files\Common Files\System\Ole DB. The SQLXML Bulk Load 4.0 library ships with SQL Server 2005. If unavailable, you can use version 3.0 of the library, which you can download from the Microsoft Download Center web site.
Code in the program.cs will be something like this :
using System;
using SQLXMLBULKLOADLib;
namespace BulkLoad
{
class Program
{
[STAThread]
static void Main(string[ args)
{
string oledbConnectString = "Provider=SQLOLEDB;Data Source=(local);" +
"Initial Catalog=Northwind;Integrated security=SSPI;";
string dataFileName = @"..\..\..\Customers.xml";
string schemaFileName = @"..\..\..\Customers.xsd";
string errorLogFileName = @"..\..\..\BulkLoadError.log";
SQLXMLBulkLoad4Class bl = new SQLXMLBulkLoad4Class();
bl.ConnectionString = oledbConnectString;
bl.ErrorLogFile = errorLogFileName;
bl.KeepIdentity = false;
Console.WriteLine("[{0}] Starting bulk load.", DateTime.Now);
try
{
bl.Execute(schemaFileName, dataFileName);
Console.WriteLine("[{0}] Bulk load completed.", DateTime.Now);
}
catch (Exception ex)
{
Console.WriteLine("[{0}] Error: {1}", DateTime.Now, ex.Message);
}
Console.WriteLine("
Press any key to continue.");
Console.ReadKey();
}
}
}
The SQL Server XML Bulk Load component is used through COM Interop to bulk insert data contained in a XML document into a SQL Server database. This component controls the execution of a XML bulk load operation. The example defines an optional error logfile, where the default is an empty string meaning that no error log is created.You can bulk load data into multiple parent-child tables at the same time, a feature that is not available in the OpenXML Transact-SQL extension.