Web Release 1

for RuBoard

Microsoft has announced its intentions to update the XML support in SQL Server via periodic updates called Web releases. The first of these is in beta test as I write this. It should be out later this year. It promises to add significant functionality to SQL Server's XML support. Among the new features Microsoft has announced are:

• Updategram supportUpdategrams are templatelike documents that you can use to insert, update, and delete SQL Server data. An option will be added to the Configure SQL XML Support In IIS tool to allow you to enable updategrams to be posted to a virtual directory.

• XML bulk loadThis will be a COM component that you can use to load XML data quickly into a database in a manner analogous to the T-SQL BULK INSERT command. This will be more efficient than doing so via OPENXML().

• Additional data type support in schemasCurrently, SQL Server's annotated schemas allow you to use the sql:datatype annotation for BLOB data types such as text and image. Web Release 1 will add support for all SQL Server data types (e.g., int, varchar).

• Enhanced templatesMicrosoft has announced that templates will be enhanced through several additions to the XML-SQL namespace. The details are a little sketchy at this point, but it appears that these features are geared toward making templates easier to use.

One of frustrating things about writing technical books is that the technology literally changes as you write. What follows is a brief discussion of the two biggest features in the beta version of Web Release 1 that I'm currently using: updategrams and the XML Bulk Load components . Keep in mind that this is beta software. Some details may have changedperhaps dramaticallyby the time you read this.

Updategrams

Updategrams provide an XML-based method of updating data in a SQL Server database. They are basically templates with special attributes and elements that allow you to specify the data you want to update and how you want to update it. An updategram contains a before-and-after image of the data you want to change. You submit updategrams to SQL Server in much the same way you submit templates. All the execution mechanisms available with templates work equally well with updategrams. You can post updategrams via HTTP, save updategrams to files and execute them via URLs, and you can execute updategrams directly via ADO and OLEDB.

Details

Updategrams are based on the xml-updategram namespace. You reference this namespace via the xmlns:updg qualifier. Each updategram contains at least one Sync element. This Sync element contains the data changes you wish to make in the form of Before and After elements. The Before element contains the before image of the data you wish to change. Normally, it will also contain a primary key or candidate key reference so that SQL Server will be able to locate the row you wish to change. Note that only one row can be selected for update by the Before element. If the elements and attributes included in the Before element identify more than one row, you'll receive an error message.

For row deletions, an updategram will have a before image, but no after image. For insertions, it will have an after image, but no before image. And, of course, for updates, an updategram will have both before and after images. Here's an example:

 <?xml version="1.0"?> <employeeupdate xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync>         <updg:before>             <Employees EmployeeID="4"/>         </updg:before>         <updg:after>             <Employees City="Scotts Valley" Region="CA"/>         </updg:after>     </updg:sync> </employeeupdate> 

In this example, we change the City and Region columns for Employee 4 in the Northwind Employees table. The EmployeeID attribute in the Before element identifies the row to change, and the City and Region attributes in the After element identify which columns to change and what values to assign to them.

Each batch of updates within a Sync element is considered a transaction. Either all the updates in the Sync element succeed or none of them do. You can include multiple Sync elements to break updates into multiple transactions.

Mapping Data

Of course, when sending data to the server for updates, deletions, and insertions via XML, we need a means of linking values in the XML document to columns in the target database table. SQL Server sports two facilities for doing this: default mapping and mapping schemas.

Default Mapping

Naturally, the easiest way to map data in an updategram to columns in the target table is to use default mapping (a.k.a., intrinsic mapping). With default mapping, a Before or After element's top-level tag is assumed to refer to the target database table, and each subelement or attribute it contains refers to a column of the same name in the table.

Here's an example that shows how to map the OrderID column in the Orders table:

 <Orders OrderID="10248"/> 

This example maps XML attributes to table columns. You could also map subelements to table columns, like this:

 <Orders>     <OrderID>10248</OrderID> </Orders> 

You need not select either attribute-centric or element-centric mapping. You can freely mix them within a given Before or After element. Here's an example:

 <Orders OrderID="10248">     <ShipCity>Reims</ShipCity> </Orders> 

Use the four-digit hexadecimal UCS-2 code for characters in table names that are illegal in XML elements (e.g., spaces). For example, to reference the Northwind Order Details table, do this:

 <Order_x0020_Details OrderID="10248"/> 

Mapping Schemas

You can also use mapping schemas to map data in an updategram to tables and columns in a database. We talked about mapping schemas earlier in the book, so I won't go back into them here except to say that you use a sync 's updg:mapping-schema attribute to specify the mapping schema for an updategram. Here's an example that specifies a mapping schema for the Orders table:

 <?xml version="1.0"?> <orderupdate xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync updg:mapping-schema="OrderSchema.xml">         <updg:before>             <Order OID="10248"/>         </updg:before>         <updg:after>             <Order City="Reims"/>         </updg:after>     </updg:sync> </orderupdate> 

And here's its mapping schema:

 <?xml version="1.0"?> <Schema xmlns="urn:schemas-microsoft-com:xml-data"         xmlns:sql="urn:schemas-microsoft-com:xml-sql">      <ElementType name="Order" sql:relation="Orders">        <AttributeType name="OID"/>        <AttributeType name="City"/>        <attribute type="OID" sql:field="OrderID"/>        <attribute type="City" sql:field="ShipCity"/>      </ElementType> </Schema> 

As you can see, the mapping schema helps translate the layout of the XML document into the layout of the Northwind Orders table.

NULLs

It's common to represent missing or inapplicable data as NULL in a database. To represent or retrieve NULL data in an updategram, you use the sync element's nullvalue attribute to specify a placeholder for NULL. This placeholder is then used everywhere in the updategram that you need to specify a NULL value. Here's an example:

 <?xml version="1.0"?> <employeeupdate xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync updg:nullvalue="NONE">         <updg:before>             <Orders OrderID="10248"/>         </updg:before>         <updg:after>       <Orders ShipCity-"Reims" ShipRegion="NONE"             ShipName="NONE"/>         </updg:after>     </updg:sync> </employeeupdate> 

As you can see, we define a placeholder for NULL named "NONE." We then use this placeholder to assign a NULL value to the ShipRegion and ShipName columns.

Parameters

Curiously, parameters work slightly different with updategrams than with templates. Rather than using at (@) symbols to denote updategram parameters, you use dollar ($) symbols, like this:

 <?xml version="1.0"?> <orderupdate xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:header>         <updg:param name="OrderID"/>         <updg:param name="ShipCity"/>     </updg:header>     <updg:sync>         <updg:before>             <Orders OrderID="$OrderID"/>         </updg:before>         <updg:after>             <Orders ShipCity="$ShipCity"/>         </updg:after>     </updg:sync> </orderupdate> 

This nuance has interesting implications for passing currency values as parameters. To pass a currency parameter value to a table column (e.g., the Freight column in the Orders table), you must map the data using a mapping schema.

To pass a parameter with a NULL value to an updategram, include the null value placeholder attribute in the updategram's Header element. You can then pass this placeholder value into the updategram to signify a NULL parameter value. This is similar to the way in which you specify a NULL value for a column in an updategram, the difference being that you specify nullvalue within the Sync element for column values, but within the Header element for parameters. Here's an example:

 <?xml version="1.0"?> <orderupdate xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:header nullvalue="NONE">         <updg:param name="OrderID"/>         <updg:param name="ShipCity"/>     </updg:header>     <updg:sync>         <updg:before>             <Orders OrderID="$OrderID"/>         </updg:before>         <updg:after>             <Orders ShipCity="$ShipCity"/>         </updg:after>     </updg:sync> </orderupdate> 

This updategram accepts two parameters. Passing a value of "NONE" will cause the ShipCity column to be set to NULL for the specified order.

Note that we don't include the xml-updategram (updg:) qualifier when specifying the nullvalue placeholder for parameters in the updategram's Header.

Multiple Rows

I mentioned earlier that each Before element can identify at most one row. This means that to update multiple rows, you must include an element for each row you wish to change.

The Id Attribute

When you specify multiple subelements within your Before and After elements, SQL Server requires that you provide a means of matching each Before element with its corresponding After element. One way to do this is through the Id attribute. The Id attribute allows you to specify a unique string value that you can use to match a Before element with an After element. Here's an example:

 <?xml version="1.0"?> <orderupdate xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync>         <updg:before>             <Orders updg:id="ID1" OrderID="10248"/>             <Orders updg:id="ID2" OrderID="10249"/>         </updg:before>         <updg:after>             <Orders updg:id="ID2" ShipCity="Munster"/>             <Orders updg:id="ID1" ShipCity="Reims"/>         </updg:after>     </updg:sync> </orderupdate> 

Here, we use the Updg:id attribute to match up subelements in the Before and After elements. Even though these subelements are specified out of sequence, SQL Server is able to apply the updates to the correct rows.

Multiple Before and After Elements

Another way to do this is to specify multiple Before and After elements, rather than multiple subelements. For each row you want to change, specify a separate Before/After element pair. Here's an example:

 <?xml version="1.0"?> <orderupdate xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync>         <updg:before>             <Orders OrderID="10248"/>         </updg:before>         <updg:after>             <Orders ShipCity="Reims"/>         </updg:after>         <updg:before>             <Orders OrderID="10249"/>         </updg:before>         <updg:after>             <Orders ShipCity="Munster"/>         </updg:after>     </updg:sync> </orderupdate> 

As you can see, this updategram updates two rows. It includes a separate before / after element pair for each update.

Results

The result returned to a client application that executes an updategram is normally an XML document containing the empty root element specified in the updategram. For example, we would expect to see this result returned by the orderupdate updategram:

 <?xml version="1.0"?> <orderupdate xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> </orderupdate> 

Any errors that occur during updategram exection are returned as <?MSSQLError> elements within the updategram's root element.

Identity Column Values

In real applications, you're often going to need to be able to retrieve an identity value that's generated by SQL Server for one table and insert it into another. This is especially true when you need to insert data into a table with a primary key that is an identity column and a table that references this primary key via a foreign key constraint. Take the example of inserting orders in the Northwind Orders and Order Details tables. As its name suggests, Order Details stores detailed information for the orders in the Orders table. Part of Order Details' primary key is the Orders table's OrderID column. When we insert a new row into the Orders table, we need to be able to retrieve that value and insert it into the Order Details table.

From Transact -SQL, we'd usually handle this situation with an INSTEAD OF insert trigger or a stored procedure. To handle it with an updategram, you use the at-identity attribute. Similarly to the id attribute, at-identity serves as a placeholder: Everywhere you use its value in the updategram, SQL Server supplies the identity value for the corresponding table (each table can have just one identity column). Here's an example:

 <?xml version="1.0"?> <orderinsert xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync>         <updg:before>         </updg:before>         <updg:after>             <Orders updg:at-identity="ID" ShipCity="Reims"/>             <Order_x0020_Details OrderID="ID" ProductID="11"                 UnitPrice=".00" Quantity="12"/>             <Order_x0020_Details OrderID="ID" ProductID="42"                 UnitPrice=".80" Quantity="10"/>         </updg:after>     </updg:sync> </orderinsert> 

Here we use the string "ID" to signify the identity column in the Orders table. Once ID is assigned, we can use it in the insertions for the Order Details table.

In addition to being able to use an identity column value elsewhere in an updategram, it's quite likely that you'll want to be able to return it to the client. To do this, use the after element's returnid attribute and specify the at-identity placeholder as its value, like this:

 <?xml version="1.0"?> <orderinsert xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync>         <updg:before>         </updg:before>         <updg:after updg:returnid="ID">             <Orders updg:at-identity="ID" ShipCity="Reims"/>             <Order_x0020_Details OrderID="ID" ProductID="11"                 UnitPrice=".00" Quantity="12"/>             <Order_x0020_Details OrderID="ID" ProductID="42"                 UnitPrice=".80" Quantity="10"/>         </updg:after>     </updg:sync> </orderinsert> 

Executing this updategram will return an XML document that looks like this:

 <?xml version="1.0"?> <orderinsert xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <returnid>         <ID>10248</ID>     </returnid> </orderinsert> 

Globally Unique Identifiers (GUIDs)

It's not unusual to see GUIDs used as key values across a partitioned view or other distributed system. Normally, you use the Transact-SQL NEWID() function to generate new GUIDs. The updategram equivalent of NEWID() is the Guid attribute. You can specify the Guid attribute to generate a GUID for use elsewhere in a Sync element. As with Id, Nullvalue, and the other attributes presented in this section, the Guid attribute establishes a placeholder that you can then supply to other elements and attributes in the updategram to use the generated GUID. Here's an example:

 <orderinsert>     xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync>         <updg:before>         </updg:before>         <updg:after>             <Orders updg:guid="GUID">                 <OrderID>GUID</OrderID>                 <ShipCity>Reims</ShipCity>             </Orders>             <Order_x0020_Details OrderID="GUID" ProductID="11"                 UnitPrice=".00" Quantity="12"/>             <Order_x0020_Details OrderID="GUID" ProductID="42"                 UnitPrice="9.80" Quantity="10"/>         </updg:after>     </updg:sync> </orderinsert> 

XML Bulk Load

As we saw in the earlier discussions of updategrams and OPENXML(), inserting XML data into a SQL Server database is relatively easy. However, both of these methods of loading data have one serious drawback: They're not suitable for loading large amounts of data. In the same way that using the Transact-SQL INSERT statement is suboptimal for loading large numbers of rows, using updategrams and OPENXML() to load large volumes of XML data into SQL Server is slow and resource intensive .

Web release 1 will introduce a new facility intended specifically to address this problem. It's called the XML Bulk Load component, and it is a COM component that you can call from OLE Automation-capable languages/tools such as Visual Basic, Delphi, and even Transact-SQL. It presents an object-oriented interface to loading XML data in bulk in a manner similar to the Transact-SQL BULK INSERT command.

Using the Component

The first step in using the XML Bulk Load component is to define a mapping schema that maps the XML data you're importing to tables and columns in your database. When the component loads your XML data, it will read it as a stream and use the mapping schema to decide where the data goes in the database.

The mapping schema determines the scope of each row added by the Bulk Load component. As the closing tag for each row is read, its corresponding data is written to the database.

You access the bulk load component itself via the SQLXMLBulkLoad interface on the SQLXMLBulkLoad COM object. The first step in using it is to connect to the database using an OLE-DB connection string or by setting its ConnectionCommand property to an existing ADO Command object. The second step is to call its Execute method. Here's some VBScript code that illustrates:

 Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBulkLoad.ConnectionString = _     "provider=SQLOLEDB;data source=KUFNATHE;database=Northwind;" & _     "Integrated Security=SSPI;" objBulkLoad.Execute d:\xml\OrdersSchema.xml, d:\xml\OrdersData.xml Set objBulkLoad = Nothing 

You can also specify an XML stream (rather than a file) to load, making cross-DBMS data transfers (from platforms that feature XML support) fairly easy.

XML Fragments

Setting the XMLFragment property to TRUE allows the Bulk Load component to load data from an XML fragment (an XML document with no root element, similar to the type returned by Transact-SQL's FOR XML extension). Here's an example:

 Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBulkLoad.ConnectionString = _     "provider=SQLOLEDB;data source=KUFNATHE;database=Northwind;" & _     "Integrated Security=SSPI;" objBulkLoad.XMLFragment = True objBulkLoad.Execute d:\xml\OrdersSchema.xml, d:\xml\OrdersFrag.xml Set objBulkLoad = Nothing 

Enforcing Constraints

Be default, the XML Bulk Load component does not enforce check and referential integrity constraints. Enforcing constraints as data is loaded slows down the process significantly, so the component doesn't enforce them unless you tell it to do so. One situation in which you might want to do that is when you're loading data directly into production tables and you want to ensure that the integrity of your data is not compromised. To cause the component to enforce your constraints as it loads data, set the CheckConstraints property to TRUE, like this:

 Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBulkLoad.ConnectionString = _     "provider=SQLOLEDB;data source=KUFNATHE;database=Northwind;" & _     "Integrated Security=SSPI;" objBulkLoad.CheckConstraints = True objBulkLoad.Execute d:\xml\OrdersSchema.xml, d:\xml\OrdersData.xml Set objBulkLoad = Nothing 

Duplicate Keys

Normally, you'd want to stop a bulk load process when you encounter a duplicate key. Usually, this means you've got unexpected data values or data corruption of some type and you need to have a look at the source data before proceeding. There are, however, exceptions. Say, for example, that you get a daily data feed from an external source that contains the entirety of a table. Each day, a few new rows show up, but for the most part, the data in the XML document already exists in your table. Your interest is in loading the new rows, but the external source who provides you the data may not know which rows you have and which ones you don't. They may provide data to lots of companies, and what your particular database contains may be unknown to them.

In this situation, you can set the IgnoreDuplicateKeys property before the load, and the component will ignore the duplicate key values it encounters. The bulk load won't halt when it encounters a duplicate key. It will simply ignore the row containing the duplicate key, and the rows with nonduplicate keys will be loaded as you'd expect. Here's an example:

 Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBulkLoad.ConnectionString = _     "provider=SQLOLEDB;data source=KUFNATHE;database=Northwind;" & _     "Integrated Security=SSPI;" objBulkLoad.IgnoreDuplicateKeys = True objBulkLoad.Execute d:\xml\OrdersSchema.xml, d:\xml\OrdersData.xml Set objBulkLoad = Nothing 

When IgnoreDuplicateKeys is set to TRUE, inserts that would cause a duplicate key will still fail, but the bulk load process will not halt. The remainder of the rows will be processed as though no error occurred.

Identity Columns

SQLXMLBulkLoad's KeepIdentity property is TRUE by default. This means that values for identity columns in your XML data will be loaded into the database rather than being generated on-the-fly by SQL Server. Normally, this is what you'd want, but you can set KeepIdentity to FALSE if you'd rather have SQL Server generate these values.

There are a couple of caveats regarding the KeepIdentity property. First, when KeepIdentity is set to TRUE, SQL Server uses SET IDENTITY_INSERT to enable identity value insertion into the target table. SET IDENTITY_INSERT has specific permissions requirements: execute permission defaults to the sysadmin role, the db_owner and db_ddladmin fixed database roles, and the table owner. This means that a user who does not own the target table and who also is not a sysadmin, dbo, or DDL administrator will likely have trouble loading data with the XML Bulk Load component. Merely having bulk admin rights is not enough.

Another caveat is that you would normally want to preserve identity values when bulk loading data into a table with dependent tables. Allowing these values to be regenerated by the server could be disastrous. You could break parent-child relationships between tables with no hope of reconstructing them. If a parent table's primary key is its identity column and KeepIdentity is set to FALSE when you load it, you may not be able to resynchronize it with the data you load for its child table. Fortunately, KeepIdentity is enabled by default, so normally this isn't a concern, but be sure you know what you're doing if you choose to set KeepIdentity to FALSE.

Here's some code that illustrates setting the KeepIdentity property:

 Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBulkLoad.ConnectionString = _     "provider=SQLOLEDB;data source=KUFNATHE;database=Northwind;" & _     "Integrated Security=SSPI;" objBulkLoad.KeepIdentity = False objBulkLoad.Execute d:\xml\OrdersSchema.xml, d:\xml\OrdersData.xml Set objBulkLoad = Nothing 

Another thing to keep in mind is that KeepIdentity is a very binary optioneither it's on or it's not. Whether you set it to TRUE or FALSE, this setting affects every object into which it inserts rows within a given bulk load. You can't retain identity values for some tables and allow SQL Server to generate them for others.

NULL Values

For a column not mapped in the schema, SQLXMLBulkLoad inserts the column's default value. If the column doesn't have a default, NULL is inserted. If the column doesn't allow NULLs, the bulk load halts with an error message.

The KeepNulls property allows you to tell the bulk load facility to insert a NULL value rather than a column's default when the column is not mapped in the schema. Here's some code that demonstrates :

 Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBulkLoad.ConnectionString = _     "provider=SQLOLEDB;data source=KUFNATHE;database-Northwind;" & _     "Integrated Security=SSPI;" objBulkLoad.KeepNulls = True objBulkLoad.Execute d:\xml\OrdersSchema.xml, d:\xml\OrdersData.xml Set objBulkLoad = Nothing 

Table Locks

As with SQL Server's other bulk load facilities, you can configure SQLXMLBulkLoad to lock the target table before it begins loading data into it. This is more efficient and faster than using more granular locks, but it has the disadvantage of preventing other users from accessing the table while the bulk load runs. To force a table lock during an XML bulk load, set the ForceTableLock property to TRUE, like this:

 Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBulkLoad.ConnectionString = _     "provider=SQLOLEDB;data source=KUFNATHE;database=Northwind;" & _     "Integrated Security=SSPI;" objBulkLoad.ForceTableLock = True objBulkLoad.Execute d:\xml\OrdersSchema.xml, d:\xml\OrdersData.xml Set objBulkLoad = Nothing 

Transactions

By default, XML bulk load operations are not transactional. That is, if an error occurs during the load process, the rows loaded up to that point will remain in the database. This is the fastest way of doing things, but it has the disadvantage of possibly leaving a table in a partially loaded state. To force a bulk load operation to be handled as a single transaction, set SQLXMLBulkLoad's Transaction property to TRUE before calling Execute.

When Transaction is TRUE, all inserts are cached in a temporary file before being loaded onto SQL Server. You can control where this file is written by setting the TempFilePath property. TempFilePath has no meaning unless Transaction is TRUE. If TempFilePath is not otherwise set, it defaults to the folder specified by the temp environmental variable on the server.

I should point out that bulk loading data within a transaction is much slower than loading it outside of one. That's why the component doesn't load data within a transaction by default. Also, you can't bulk load binary XML data from within a transaction, so keep that in mind.

Here's some code that illustrates a transactional bulk load:

 Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBulkLoad.ConnectionString = _     "provider=SQLOLEDB;data source=KUFNATHE;database=Northwind;" & _     "Integrated Security=SSPI;" objBulkLoad.Transaction = True objBulkLoad.TempFilePath = "c:\temp\xmlswap" objBulkLoad.Execute d:\xml\OrdersSchema.xml, d:\xml\OrdersData.xml Set objBulkLoad = Nothing 

In this example, SQLXMLBulkLoad establishes its own connection to the server over OLE-DB, so it operates within its own transaction context. If an error occurs during the bulk load, the component rolls back its own transaction.

When SQLXMLBulkLoad uses an existing OLE-DB connection via its ConnectionCommand property, the transaction context belongs to that connection and is controlled by the client application. When the bulk load completes, the client application must explicitly commit or roll back the transaction. Here's an example:

 On Error Resume Next Err.Clear Set objCmd = CreateObject("ADODB.Command") objCmd.ActiveConnection= _     "provider=SQLOLEDB;data source=KUFNATHE;database=Northwind;" &     "Integrated Security=SSPI;" Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBulkLoad.Transaction = True objBulkLoad.ConnectionCommand = objCmd objBulkLoad.Execute d:\xml\OrdersSchema.xml, d:\xml\OrdersData.xml If Err.Number = 0 Then     objCmd.ActiveConnection.CommitTrans Else     objCmd.ActiveConnection.RollbackTrans End If Set objBulkLoad = Nothing Set objCmd = Nothing 

Note that when using the ConnectionCommand property, Transaction is required. It must be set to TRUE.

Errors

The XML Bulk Copy component supports logging error messages to a file via its ErrorLogFile property. This file is an XML document itself that lists any errors that occurred during the bulk load. Here's some code that demonstrates using it:

 Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBulkLoad.ConnectionString = _     "provider=SQLOLEDB;data source=KUFNATHE;database=Northwind;" & _     "Integrated Security=SSPI;" objBulkLoad.ErrorLogFile = "c:\temp\xmlswap\errors.xml" objBulkLoad.Execute d:\xml\OrdersSchema.xml, d:\xml\OrdersData.xml Set objBulkLoad = Nothing 

The file you specify will contain a Record element for each error that occurred during the last bulk load. The most error message will be listed first.

Generating Database Schemas

In addition to loading data into existing tables, the XML Bulk Copy component can also create target tables for you if they do not already exist, or drop and recreate them if they do exist. To create nonexistent tables, set the component's SchemaGen property to TRUE, like this:

 Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBulkLoad.ConnectionString = _     "provider=SQLOLEDB;data source=KUFNATHE;database=Northwind;" & _     "Integrated Security=SSPI;" objBulkLoad.SchemaGen = True objBulkLoad.Execute d:\xml\OrdersSchema.xml, d:\xml\OrdersData.xml Set objBulkLoad = Nothing 

Because SchemaGen is set to TRUE, any tables in the schema that don't already exist will be created when the bulk load starts. For tables that already exist, data is simply loaded into them as it normally would be.

If you set the BulkLoad property of the component to FALSE, no data is loaded. So, if SchemaGen is set to TRUE, but BulkLoad is FALSE, you'll get empty tables for those in the mapping schema that did not already exist in the database, but you'll get no data. Here's an example:

 Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBulkLoad.ConnectionString = _     "provider=SQLOLEDB;data source=KUFNATHE;database=Northwind;" & _     "Integrated Security=SSPI;" objBulkLoad.SchemaGen = True objBulkLoad.BulkLoad = False objBulkLoad.Execute d:\xml\OrdersSchema.xml, d:\xml\OrdersData.xml Set objBulkLoad = Nothing 

When XML Bulk Load creates tables, it uses the information in the mapping schema to define the columns in each table. The sql:datatype annotation defines column data types, and the Dt:type attribute further defines column type information. To define a primary key within the mapping schema, set a column's Dt:type attribute to Id and set the SGUseID property of the XML Bulk Load component to True. Here's a mapping schema that illustrates:

 <ElementType name="Orders" sql:relation="Orders">     <AttributeType name="OrderID" sql:datatype="int" dt:type="id"/>     <AttributeType name="ShipCity" sql:datatype="nvarchar(30)"/>     <attribute type="OrderID" sql:field="OrderID"/>     <attribute type="ShipCity" sql:field="ShipCity"/> </ElementType> 

And here's some VBScript code that sets the SGUseID property so that a primary key will automatically be defined for the table that's created on the server:

 Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBulkLoad.ConnectionString = _     "provider=SQLOLEDB;data source=KUFNATHE;database=Northwind;" & _     "Integrated Security=SSPI;" objBulkLoad.SchemaGen = True objBulkLoad.SGUseID = True objBulkLoad.Execute d:\xml\OrdersSchema.xml, d:\xml\OrdersData.xml Set objBulkLoad = Nothing 

Here's the Transact-SQL that results when the bulk load executes:

 CREATE TABLE Orders (     OrderID int NOT NULL,     ShipCity nvarchar(30) NULL,     PRIMARY KEY CLUSTERED (OrderID) ) 

In addition to being able to create new tables from those in the mapping schema, SQLXML BulkLoad can also drop and recreate tables. Set the SGDropTables property to TRUE to cause the component to drop and recreate the tables mapped in the schema. Here's an example:

 Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBulkLoad.ConnectionString = _     "provider=SQLOLEDB;data source=KUFNATHE;database=Northwind;" & _     "Integrated Security=SSPI;" objBulkLoad.SchemaGen = True objBulkLoad.SGDropTables = True objBulkLoad.Execute d:\xml\OrdersSchema.xml, d:\xml\OrdersData.xml Set objBulkLoad = Nothing 

for RuBoard