SQLXMLBulkLoad

可以实现主键重复数据更新嘛？主键重复忽略不是我想要的结果阿期待高手ing

楼主，清华大学出版社出版的《SQL Server存储过程、XML和HTML高级指南》这本书里有的。
内容说的比较详细了，你可以去网上查一下有没有电子版的。
但没有提到你要的时间处理的问题。

主键重复忽略不是我想要的结果阿
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
什么意思呢？
能举个具体的例子？SQLXMLBulkLoad还有两个相关的属性：
CheckConstraints:设为True表示强制执行检查和有关的约束。
KeepIdentity:设为True相当于Set Identity_Insert On。其他的属性我也就不清楚了，不知道这两上能不能满足你的需求。

<%
'存储XML文件的数据
'首先创建XML文件批量装载的对象
set objBLSer = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBLSer.ConnectionString= conn.ConnectionString '发生错误时错误日志的路径及文件名称
objBLSer.ErrorLogFile = Server.MapPath(".") & "\Error.xml"
'忽略主健重复错误信息
objBLSer.IgnoreDuplicateKeys = True
'强制执行检查和有关的约束
objBLSer.CheckConstraints = True
'执行（映射文件、数据文件）
SchemaFile = "d:\test\Schema\userinfo.xml"
DataFile = "d:\test\userinfo.xml"
objBLSer.Execute SchemaFile, DataFile set objBLSer=Nothing
%>其中：
'忽略主健重复错误信息
objBLSer.IgnoreDuplicateKeys = True
如果不设置'忽略主健重复错误信息的话，程序执行要报错，如果设置了的话，主键重复的记录不更新而是直接跳过了，显然我要的是更新记录而不是跳过。
有什么办法可以设置成，主键重复的记录更新呢？

微软给的全部资料：
SQL Server XML Bulk Load Object Model
The Microsoft® SQL Server™ XML Bulk Load object model consists of the SQLXMLBulkLoad object. This object supports the following methods and properties.Methods
ExecuteBulk loads the data by using the schema file and data file (or stream) that are provided as parameters.Properties
BulkLoadSpecifies whether a bulk load should be performed. This property is useful if you want to generate only the schemas (see the SchemaGen, SGDropTables, and SGUseID properties that follow) and not perform a bulk load. This is a Boolean property. When the property is set to TRUE, XML Bulk Load executes. When it is set to FALSE, XML Bulk Load does not execute.The default value is TRUE.CheckConstraintsSpecifies whether the constraints (such as constraints due to the primary key/foreign key relationship among columns) that are specified on the column should be checked when XML Bulk Load inserts data into the columns. This is a Boolean property. When the property is set to TRUE, XML Bulk Load checks the constraints for each value inserted (which means that a constraint violation results in an error). When it is set to FALSE, XML Bulk Load ignores the constraints during an insert operation. In the current implementation, you must define the tables in the order of primary key and foreign key relationships in the mapping schema. That is, a table with a primary key must be defined before the corresponding table with the foreign key; otherwise, XML Bulk Load fails.The default value is FALSE.ConnectionCommandIdentifies an existing connection object (for example, the ADO or ICommand command object) that XML Bulk Load should use. You can use the Connection Command property instead of specifying a connection string with the ConnectionString property. The Transaction property must be set to TRUE if you use ConnectionCommand.If you use both the ConnectionString and ConnectionCommand properties, XML Bulk Load uses the last specified property.The default value is NULL.ConnectionStringIdentifies the OLEDB connection string that provides the necessary information to establish a connection to an instance of the database. If you use both the ConnectionString and ConnectionCommand properties, XML Bulk Load uses the last specified property.The default value is NULL.ErrorLogFileSpecifies the file name into which the XML Bulk Load logs errors and messages. The default is an empty string, in which case no logging takes place.ForceTableLockSpecifies whether the tables into which XML Bulk Load copies data should be locked for the duration of the bulk load. This is a Boolean property. When the property is set to TRUE, XML Bulk Load acquires table locks for the duration of the bulk load. When it is set to FALSE, XML Bulk Load acquires a table lock each time it inserts a record into a table.The default value is FALSE.IgnoreDuplicateKeysSpecifies what to do if an attempt is made to insert duplicate values in a key column. If this property is set to TRUE and an attempt is made to insert a record with a duplicate value in a key column, SQL Server does not insert that record. But it does insert the subsequent record; thus, the bulk load operation does not fail. If this property is set to FALSE, Bulk Load fails when an attempt is made to insert a duplicate value in a key column.When the IgnoreDuplicateKeys property is set to TRUE, a COMMIT statement is issued for every record inserted in the table. This slows down the performance. The property can be set to TRUE only when the Transaction property is set to FALSE, because the transactional behavior is implemented using files.The default value is FALSE.KeepIdentitySpecifies how to deal with the values for an IDENTITY-type column in the source file. This is a Boolean property. When the property is set to TRUE, XML Bulk Load assigns the values that are specified in the source file to the identity column. When the property is set to FALSE, the bulk-load operation ignores the identity column values that are specified in the source. In this case, SQL Server assigns a value to the identity column. The value of this property applies to all columns involved in the bulk load.The default value is TRUE.KeepNullsSpecifies what value to use for a column that is missing a corresponding attribute or subelement in the XML document. This is a Boolean property. When the property is set to TRUE, XML Bulk Load assigns a null value to the column. It does not assign the column's default value, if any, as set on the server. The value of this property applies to all columns involved in the bulk load.The default value is FALSE.SchemaGenSpecifies whether to create the required tables before performing a bulk load operation. This is a Boolean property. If this property is set to TRUE, the tables identified in the mapping schema are created (the database must exist). If one or more of the tables already exist in the database, the SGDropTables property determines whether these preexisting tables are to be dropped and re-created.The default value for the SchemaGen property is FALSE. SchemaGen does not create any constraints (such as PRIMARY KEY/FOREIGN KEY constraint) on the newly created tables.Note If you set the SchemaGen property to TRUE, XML Bulk Load creates the necessary tables from the element and attribute names. Therefore, it is important that you do not use SQL Server reserved words for element and attribute names in the schema.
SGDropTablesSpecifies whether existing tables should be dropped and re-created. You use this property when the SchemaGen property is set to TRUE. If SGDropTables is FALSE, the existing tables are retained. When this property is TRUE, the existing tables are deleted and re-created.The default value is FALSE.SGUseIDSpecifies whether the attribute in the mapping schema that is identified as id type can be used in creating a PRIMARY KEY constraint when the table is created. Use this property when the SchemaGen property is set to TRUE. If SGUseID is TRUE, the SchemaGen utility uses an attribute for which dt:type="id" is specified as the primary key column and adds the appropriate PRIMARY KEY constraint when creating the table.The default value is FALSE.TempFilePathSpecifies the file path where XML Bulk Load creates the temporary files for a transacted bulk load. (This property is useful only when the Transaction property is set to TRUE.) You must ensure that the SQL Server account that is used for XML Bulk Load has access to this path. If this property is not set, XML Bulk Load stores the temporary files in the location that is specified in the TEMP environment variable.TransactionSpecifies whether the bulk load should be done as a transaction, in which case the rollback is guaranteed if the bulk load fails. This is a Boolean property. If the property is set to TRUE, the bulk load occurs in a transactional context. The TempFilePath property is useful only when Transaction is set to TRUE.Note If you are loading binary data (such as the bin.hex, bin.base64 XML data types to the binary, image SQL Server data types), the Transaction property must be set to FALSE.
The default value is FALSE.XMLFragmentSpecifies whether the source data is an XML fragment. An XML fragment refers to an XML document with no single, top-level (root) element. This is a Boolean property. This property must be set to TRUE if the source file consists of an XML fragment.The default value is FALSE.©2002 Microsoft Corporation. All Rights Reserved.

解决方案 »