ADO.NET 3.5 Cookbook, 2nd Edition

10.9. XML Bulk Loading with SQL Server

Problem

Given many records in an XML file that you need to add to a SQL Server database, you need to perform a bulk insert with optimal performance.

Solution

Perform a bulk insert and update using the XML bulk load functionality in Microsoft SQL Server.

The solution uses a table named Customers in the AdoDotNet35Cookbook database. The T-SQL statement to create the table follows:

	USE AdoDotNet35Cookbook
	GO

	CREATE TABLE Customers(
	    CustomerID nvarchar(5) NOT NULL PRIMARY KEY,
	    CompanyName nvarchar(40) NULL,
	    ContactName nvarchar(30) NULL,
	    ContactTitle nvarchar(30) NULL,
	    Address nvarchar(60) NULL,
	    City nvarchar(15) NULL,
	    Region nvarchar(15) NULL,
	    PostalCode nvarchar(10) NULL,
	    Country nvarchar(15) NULL,
	    Phone nvarchar(24) NULL,
	    Fax nvarchar(24) NULL
	)

The solution uses an XML schema file that describes the XML data file. The schema file is named Customers.xsd and is shown in Example 10-14. The solution expects this file to be in the same directory as the solution file BulkLoad.sln.

Example 10-14. File: 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 ...