34,590
社区成员
发帖
与我相关
我的任务
分享SQLXMLBulkLoad
哪位大哥有相关资料给点阿！！！谢谢了
查了 几天资料，都是最简单的用法，关于时间类型字段的处理都没有提
set objBLSer = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBLSer.ConnectionString= conn.ConnectionString
'忽略主健重复错误信息
objBLSer.IgnoreDuplicateKeys = True
查了 几天资料，都是最简单的用法，关于时间类型字段的处理都没有提
set objBLSer = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBLSer.ConnectionString= conn.ConnectionString
'忽略主健重复错误信息
objBLSer.IgnoreDuplicateKeys = True
...全文
请发表友善的回复…
发表回复
Comer 2006-07-26
- 打赏
- 举报
up
Comer 2006-07-19
- 打赏
- 举报
ding shang qu
Comer 2006-07-14
- 打赏
- 举报
up
lzhs 2006-07-11
- 打赏
- 举报
我帮顶 :)
Comer 2006-07-11
- 打赏
- 举报
自己顶
Comer 2006-07-10
- 打赏
- 举报
强烈BS版主
lzhs 2006-07-07
- 打赏
- 举报
呵呵。
我来帮顶`~~
我来帮顶`~~
Comer 2006-07-07
- 打赏
- 举报
我给N个版主发了短消息，没一个来看的
难道这个问题不值得一提？
难道这个问题不值得一提？
lzhs 2006-07-07
- 打赏
- 举报
呵呵，不客气啦。
没办法啊，谁叫咱没掌握核心技术呢？
有时候只有“曲线救国”啦 ：）
没办法啊，谁叫咱没掌握核心技术呢？
有时候只有“曲线救国”啦 ：）
Comer 2006-07-07
- 打赏
- 举报
“曲线救国”的路我也想过，说实话，“曲线救国”偶是经常用的，为了应付项目完成时间，不得不用。但是每次都会觉得很没技术水准，所以开贴求个有点水准的方案，真的不想再走“曲线救国”的路了，总不能老也没有提高吧。
谢谢楼上的兄弟的大力支持。
谢谢楼上的兄弟的大力支持。
lzhs 2006-07-07
- 打赏
- 举报
呵呵。
这个帖应该转到疑难问题区去～～～
这个帖应该转到疑难问题区去～～～
Comer 2006-07-07
- 打赏
- 举报
那至少该给我置个吧？
广告都能置顶，难道还有什么不可以置顶的嘛？
广告都能置顶，难道还有什么不可以置顶的嘛？
lzhs 2006-07-07
- 打赏
- 举报
不能这样极端嘛。
版主也不一定就懂这方面的知识呀，他们说不定来看过了，但是刚好不懂。。。
我觉得你现在就“曲线救国”吧。
等以后有机会了再研究喽。
版主也不一定就懂这方面的知识呀，他们说不定来看过了，但是刚好不懂。。。
我觉得你现在就“曲线救国”吧。
等以后有机会了再研究喽。
Comer 2006-07-07
- 打赏
- 举报
不知道现在的版主都怎么当的。。。
居然还有脸搞什么优秀版主评选。。。
居然还有脸搞什么优秀版主评选。。。
lzhs 2006-07-06
- 打赏
- 举报
主键重复忽略不是我想要的结果阿
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
什么意思呢？
能举个具体的例子？
SQLXMLBulkLoad还有两个相关的属性：
CheckConstraints:设为True表示强制执行检查和有关的约束。
KeepIdentity:设为True相当于Set Identity_Insert On。
其他的属性我也就不清楚了，不知道这两上能不能满足你的需求。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
什么意思呢？
能举个具体的例子？
SQLXMLBulkLoad还有两个相关的属性：
CheckConstraints:设为True表示强制执行检查和有关的约束。
KeepIdentity:设为True相当于Set Identity_Insert On。
其他的属性我也就不清楚了，不知道这两上能不能满足你的需求。
Comer 2006-07-06
- 打赏
- 举报
但没有提到你要的时间处理的问题。
/////////////////
我现在要解决的就是 俩个问题 ，一个是 主键重复，这个是最主要要解决的，另一个是日期时间类型字段，这个我可以用比较笨的方法解决。
/////////////////
我现在要解决的就是 俩个问题 ，一个是 主键重复，这个是最主要要解决的，另一个是日期时间类型字段，这个我可以用比较笨的方法解决。
lzhs 2006-07-06
- 打赏
- 举报
收下了。
＝＝＝＝＝
这个实际情况我没有实施过，不知道批量加载后是放到表里呢还是怎么着的。
如果是加载到一个表里的话，可不可以考虑先加载到一个没有主键的表里，然后再更新这个表，然后再复制到另一个表里呢？
＝＝＝＝＝
这个实际情况我没有实施过，不知道批量加载后是放到表里呢还是怎么着的。
如果是加载到一个表里的话，可不可以考虑先加载到一个没有主键的表里，然后再更新这个表，然后再复制到另一个表里呢？
Comer 2006-07-06
- 打赏
- 举报
微软给的全部资料：
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
Execute
Bulk loads the data by using the schema file and data file (or stream) that are provided as parameters.
Properties
BulkLoad
Specifies 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.
CheckConstraints
Specifies 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.
ConnectionCommand
Identifies 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.
ConnectionString
Identifies 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.
ErrorLogFile
Specifies 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.
ForceTableLock
Specifies 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.
IgnoreDuplicateKeys
Specifies 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.
KeepIdentity
Specifies 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.
KeepNulls
Specifies 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.
SchemaGen
Specifies 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.
SGDropTables
Specifies 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.
SGUseID
Specifies 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.
TempFilePath
Specifies 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.
Transaction
Specifies 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.
XMLFragment
Specifies 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.
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
Execute
Bulk loads the data by using the schema file and data file (or stream) that are provided as parameters.
Properties
BulkLoad
Specifies 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.
CheckConstraints
Specifies 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.
ConnectionCommand
Identifies 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.
ConnectionString
Identifies 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.
ErrorLogFile
Specifies 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.
ForceTableLock
Specifies 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.
IgnoreDuplicateKeys
Specifies 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.
KeepIdentity
Specifies 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.
KeepNulls
Specifies 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.
SchemaGen
Specifies 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.
SGDropTables
Specifies 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.
SGUseID
Specifies 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.
TempFilePath
Specifies 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.
Transaction
Specifies 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.
XMLFragment
Specifies 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.
Comer 2006-07-06
- 打赏
- 举报
<%
'存储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
如果不设置'忽略主健重复错误信息的话，程序执行要报错，如果设置了的话，主键重复的记录不更新而是直接跳过了，显然我要的是更新记录而不是跳过。
有什么办法可以设置成，主键重复的记录更新呢？
'存储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
如果不设置'忽略主健重复错误信息的话，程序执行要报错，如果设置了的话，主键重复的记录不更新而是直接跳过了，显然我要的是更新记录而不是跳过。
有什么办法可以设置成，主键重复的记录更新呢？
lzhs 2006-07-04
- 打赏
- 举报
楼主，清华大学出版社出版的《SQL Server存储过程、XML和HTML高级指南》这本书里有的。
内容说的比较详细了，你可以去网上查一下有没有电子版的。
但没有提到你要的时间处理的问题。
内容说的比较详细了，你可以去网上查一下有没有电子版的。
但没有提到你要的时间处理的问题。
加载更多回复（5）
