XML and SQL 2000 (Part 8)

So far, this series has concentrated on configuration options available via
the graphical interface of IIS Virtual Directory Management for SQL Server.
However, newer versions of SQLXML, in addition to the features we have already
covered, provide another type of functionality, accessible through scripting,
that allows bulk loading of XML data into SQL databases. Such functionality
will be the topic of this article. For this, you should install the most recent
release of SQLXML (version 3.0 currently at Service Pack 1 level, available for
download from the
Microsoft Web Site
).

Mechanisms for modifying SQL databases using XML formatted data, such as the
previously described updategrams,
are not suitable for large amounts of data, because they require the entire XML
document to be loaded into memory prior to initiating the insert operation.
Bulk loading of XML data (equivalent to bulk loading using standard SQL server
methods as Bulk Copy Program or BULK INSERT) provides a more efficient method
of dealing with such situations.

XML-based bulk load operation is implemented as the XML Bulk Load COM
component. You can take advantage of this component through use of programming
(including .NET applications) or scripting (e.g. via VBScript, which will be
the method presented here). The XML Bulk Load component uses data in XML format
stored in an XML document (or an XML fragment) and an annotated mapped schema
in XDR (XML Data Reduced) or XSD (XML Schema Definition) format (which provides
XML data description and verification).

As you might recall from our earlier discussions, XDR schema has been
supported (as the de-facto standard) since the release of SQL Server 2000 (and
accompanying SQLXML 1.0). Once the XML Schema Definition standard had been
ratified (and recommended) by the World Wide Web Consortium, Microsoft included
it in subsequent releases of SQLXML (2.0 and 3.0). If you intend to work with
XSD schemas, you can convert XDR schema examples presented in this article to
XSD format using CVTSCHEMA.EXE included with SQLXML 3.0 (you can find this file
in Program FilesSQLXML 3.0bin folder).

To better understand the bulk load mechanism, let’s take a look at a fairly
simple sample of code that inserts XML-formatted data into the Shippers table
of the Northwind database.

Set oXMLBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
oXMLBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB;SERVER=YourSQLServer;" & _
				"DATABASE=Northwind;INTEGRATED SECURITY=sspi;"
oXMLBulkLoad.Execute "C:XMLDataShippers.xdr", "C:XMLDataShippers.xml"
Set oXMLBulkLoad = Nothing

As you can see, first we instantiate an object (in our example, called oXMLBulkLoad)
of type SQLXMLBulkLoad – this class is represented in the HKEY_CLASSES_ROOT
portion of the registry by SQLXMLBulkLoad.SQLXMLBulkLoad entry (referred to as ProgID),
hence this is the parameter used by the CreateObject method. Next, we specify
the connection string property of the object we just created. The connection
string contains the provider name (SQLOLEDB), server name (make sure you
replace the value "YourSQLServer" with the name of your SQL server),
database name (Northwind in this case) and type of authentication used for the
connection (Windows integrated). The actual bulk insert takes place when the
Execute method of the oXMLBulkLoad object is invoked. The method takes two
parameters – full paths of the files containing a schema – in XDR or XSD format
– and corresponding XML document, respectively. If you save the script as BulkLoadShippers.vbs,
you could execute it by running

cscript BulkLoadShippers.vbs

at the Command Prompt or simply double-clicking the file
from Windows Explorer.

However, before you do this, you need to ensure that you have properly
formatted schema and XML-document files, available in the locations specified
in the script. Our XML document will have the following format:

<?xml version="1.0" encoding="utf-8" ?> 
  <Shippers>
    <Shipper ShipperID="4" CompanyName="Speedy Gonzalez" Phone="(503) 555-9934" /> 
    <Shipper ShipperID="5" CompanyName="The RoadRunner" Phone="(503) 555-3456" /> 
  </Shippers>

which corresponds to the following XDR schema:


<Schema 
	xmlns_dt="urn:schemas-microsoft-com:datatypes"
	xmlns_sql="urn:schemas-microsoft-com:xml-sql">

	<ElementType name="Shipper" sql_relation="Shippers">
		<AttributeType name="ShipperID" required="yes" dt_type="int"/>
		<AttributeType name="CompanyName" required="yes" dt_type="string"/>
		<AttributeType name="Phone" required="no" dt_type="string"/>

		<attribute type="ShipperID"/>
		<attribute type="CompanyName"/>
		<attribute type="Phone"/>
	</ElementType>
</Schema>
Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles