Export (0) Print
Expand All

SQL Server XML Bulk Load Object Model

SQL Server 2000

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.

If the bulk load involves a column that is a foreign key referring to an identity column in which SQL-Server-generated values are stored, bulk load appropriately propagates these identity values to the foreign key 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.

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.

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.

Show:
© 2014 Microsoft