Our XDR schema is rather straightforward and can be created without major
effort, but constructing more elaborate XDR schemas tends to be a mundane and
time consuming task (except for very simple data structures). Fortunately, you
can simplify this process by using the XML View Mapper utility, freely downloadable
from the
Microsoft Web site.
After the installation, the utility is available from All Programs ->
Microsoft SQL Server XML Tools menu. The first time you launch it, you will be
prompted to create a new project. Once you specify its name and location, you
will be presented with the XML View Mapper interface. The interface is divided
into two main windowpanes - Map Editor with three subpanes on the left and
Project Editor on the right. From here, you can generate XDR schemas based on
SQL database tables and add annotations to them. Start by clicking on the
leftmost subpane of Map Editor (labeled "Click to import an SQL
schema") and fill out entries in the Data Link Properties dialog box that
define a connection to the target server and database. Clicking on the OK
button in this initial dialog box, will bring up the next one, titled New
Database Tables, from which you can select tables and views for which you
intend to create mapping schema. This will bring back the Map Editor window,
this time with the tables you selected showing in the leftmost subpane. Next,
select the "Generate XDR module..." option from the Tools ->
Utilities menu. This will automatically generate an item under XDR node in the
Project Editor, corresponding to the tables displayed in the Map Editor
leftmost subpane. To generate a mapping schema for this XDR module, drag it to
the rightmost subpane of the Map Editor. The mapping will be displayed as a new
item under Map Modules node in the Project Editor. You can export XDR schema
using the "Export XDR Schema" option from the Tools menu.
The steps described above introduced you to the most basic features of XML
Map Viewer. Note, though, that the tool is much more powerful. For example, you
can generate XDR mapping schemas representing multi-table relationships (by
dragging and dropping respective columns between SQL Module and XDR Module subpanes
of the Map Editor) or run tests by submitting XPath queries against the
mappings. You can also generate XDR schema from an XML document or convert
schema from DTD (Document Type Definition - schema definition mechanism used in
early versions of XML) to XDR format.
Now that we are familiar with simpler ways of generating XDR schema, let's
go back to our original script and review the different options available when
bulk importing XML data into SQL Server databases. The SQLXMLBulkLoad object we
created has a number of properties that can be used to modify the default bulk
load behavior. Among the most relevant are:
CheckConstraints - when set to False, prevents check for
constraints during the data load. With large amounts of data, this might
significantly speed up the load process.
ErrorLogFile - when set to a valid file system path, it causes
creation of an XML-based error log that can be analyzed to determine reason for
bulk load failures.
ForceTableLock - when set to True, locks the entire table for the
duration of the bulk insert, which typically speeds up the load process.
KeepIdentity - when set to False, SQL Server will create identity
value during data import. Otherwise (also by default), values specified in the XML
document, will be used for this purpose.
KeepNulls - when set to True, forces SQL server to insert a NULL
value for unmapped columns (ones for which values are not included in XML
document), even if default, non-NULL is defined for them.
SchemaGen - when set to True, causes creation of tables (if they
do not already exist) during the import process. You can also drop existing
tables by setting SGDropTables property to True.
To take advantage of any of these properties, you need to set its value
(typically to either True or False) prior to invoking the Execute method). For
example, if you want to ignore check for constraints during bulk loading, you
would use the following code:
Set oXMLBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
oXMLBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB;SERVER=YourSQLServer;" & _
"DATABASE=Northwind;INTEGRATED SECURITY=sspi;"
oXMLBulkLoad.CheckConstraints = False
oXMLBulkLoad.Execute "C:\XMLData\Shippers.xdr", "C:\XMLData\Shippers.xml"
Set oXMLBulkLoad = Nothing
In this article, I presented an overview of bulk load
capabilities of COM object included with the SQLXML version 3.0. In the next
article of this series, we will look at another method of modifying SQL Server databases,
called diffgrams, which has been introduced after the release of SQL Server
2000.
»
See All Articles by Columnist Marcin Policht
» 