http://msdn.microsoft.com/en-us/library/ms171878.aspx
1.首先创建一张表
CREATE TABLE Ord (
OrderID int identity(1,1) PRIMARY KEY,
CustomerID varchar(5))
GO
CREATE TABLE Product (
ProductID int identity(1,1) PRIMARY KEY,
ProductName varchar(20))
GO
CREATE TABLE OrderDetail (
OrderID int FOREIGN KEY REFERENCES Ord(OrderID),
ProductID int FOREIGN KEY REFERENCES Product(ProductID),
CONSTRAINT OD_key PRIMARY KEY (OrderID, ProductID))
GO
2.保存一个xml文件schema.xml
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="OrderOD"
parent="Ord"
parent-key="OrderID"
child="OrderDetail"
child-key="OrderID" /> <sql:relationship name="ODProduct"
parent="OrderDetail"
parent-key="ProductID"
child="Product"
child-key="ProductID"
inverse="true"/>
</xsd:appinfo>
</xsd:annotation> <xsd:element name="Order" sql:relation="Ord"
sql:key-fields="OrderID" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Product" sql:relation="Product"
sql:key-fields="ProductID"
sql:relationship="OrderOD ODProduct">
<xsd:complexType>
<xsd:attribute name="ProductID" type="xsd:int" />
<xsd:attribute name="ProductName" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="OrderID" type="xsd:integer" />
<xsd:attribute name="CustomerID" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
3.再保存另外一个xml文件data.xml
<ROOT>
<Order OrderID="11" CustomerID="ALFKI">
<Product ProductID="11" ProductName="Chai" />
<Product ProductID="22" ProductName="Chang" />
</Order>
<Order OrderID="22" CustomerID="ANATR">
<Product ProductID="33" ProductName="Aniseed Syrup" />
<Product ProductID="44" ProductName="Gumbo Mix" />
</Order>
</ROOT>
4.打开VS，创建一个控制台应用程序
5.添加dll引用 In the COM tab, select Microsoft SQLXML Bulkload 4.0 Type Library (xblkld4.dll) and click OK. You will see the Interop.SQLXMLBULKLOADLib assembly created in the project.
坑爹的是，我电脑上根本找不到xblkld4.dll C:\Program Files\Common Files\System\Ole DB\xblkld4.dll
在我的电脑的 Program Files和Program Files (x86)文件夹下对应的目录都找不到这个dll
6.Main函数如下
static void Main(string[] args)
{
try
{
SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class objBL = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();
objBL.ConnectionString = "Provider=sqloledb;server=server;database=databaseName;integrated security=SSPI";
objBL.ErrorLogFile = "error.xml";
objBL.KeepIdentity = false;
objBL.Execute ("schema.xml","data.xml");
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
}
}