XML Bulk Load Examples (SQLXML 4.0)

Article
03/03/2023

Applies to: SQL Server Azure SQL Database

The following examples illustrate the XML Bulk Load functionality in Microsoft SQL Server. Each example provides an XSD schema and its equivalent XDR schema.

Bulk Loader Script (ValidateAndBulkload.vbs)

The following script, written in the Microsoft Visual Basic Scripting Edition (VBScript), loads an XML document into the XML DOM; validates it against a schema; and, if the document is valid, executes an XML bulk load to load the XML into a SQL Server table. This script can be used with each of the individual examples that refer to it later in this topic.

Note

XML Bulk Load does not throw a warning or an error if no content is uploaded from the data file. Therefore, it is a good practice to validate your XML data file prior to executing a bulk load operation.

Dim FileValid  
  
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")  
objBL.ConnectionString = "provider=SQLOLEDB;data source=MyServer;database=tempdb;integrated security=SSPI"  
objBL.ErrorLogFile = "c:\error.log"  
  
'Validate the data file prior to bulkload  
Dim sOutput   
sOutput = ValidateFile("SampleXMLData.xml", "", "SampleSchema.xml")  
WScript.Echo sOutput  
  
If FileValid Then  
   ' Check constraints and initiate transaction (if needed)  
   ' objBL.CheckConstraints = True  
   ' objBL.Transaction=True  
  'Execute XML bulkload using file.  
  objBL.Execute "SampleSchema.xml", "SampleXMLData.xml"  
  set objBL=Nothing  
End If  
  
Function ValidateFile(strXmlFile,strUrn,strXsdFile)  
  
   ' Create a schema cache and add SampleSchema.xml to it.  
   Dim xs, fso, sAppPath  
   Set fso = CreateObject("Scripting.FileSystemObject")   
   Set xs = CreateObject("MSXML2.XMLSchemaCache.6.0")  
   sAppPath = fso.GetFolder(".")   
   xs.Add strUrn, sAppPath & "\" & strXsdFile  
  
   ' Create an XML DOMDocument object.  
   Dim xd   
   Set xd = CreateObject("MSXML2.DOMDocument.6.0")  
  
   ' Assign the schema cache to the DOM document.  
   ' schemas collection.  
   Set xd.schemas = xs  
  
   ' Load XML document as DOM document.  
   xd.async = False  
   xd.Load sAppPath & "\" & strXmlFile  
  
   ' Return validation results in message to the user.  
   If xd.parseError.errorCode <> 0 Then  
        ValidateFile = "Validation failed on " & _  
             strXmlFile & vbCrLf & _  
             "=======" & vbCrLf & _  
             "Reason: " & xd.parseError.reason & _  
             vbCrLf & "Source: " & _  
             xd.parseError.srcText & _  
             vbCrLf & "Line: " & _  
             xd.parseError.Line & vbCrLf  
             FileValid = False  
    Else  
        ValidateFile = "Validation succeeded for " & _  
             strXmlFile & vbCrLf & _  
             "========" & _  
             vbCrLf & "Contents to be bulkloaded" & vbCrLf  
             FileValid = True  
    End If  
End Function

A. Bulk loading XML in a table

This example establishes a connection to the instance of SQL Server that is specified in the ConnectionString property (MyServer). The example also specifies the ErrorLogFile property. Therefore, the error output is saved in the specified file ("C:\error.log"), which you might also decide to change to a different location. Notice also that the Execute method has as its parameters both the mapping schema file (SampleSchema.xml) and the XML data file (SampleXMLData.xml). When the bulk load executes, the Cust table you have created in tempdb database will contain new records based upon the contents of the XML data file.

To test a sample bulk load

Create this table:

CREATE TABLE Cust(CustomerID  int PRIMARY KEY,  
                  CompanyName varchar(20),  
                  City        varchar(20));  
GO

Create a file in your preferred text or XML editor, and save it as SampleSchema.xml. To this file, add the following XSD schema:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
   <xsd:element name="ROOT" sql:is-constant="1" >  
     <xsd:complexType>  
       <xsd:sequence>  
         <xsd:element name="Customers" sql:relation="Cust" maxOccurs="unbounded">  
           <xsd:complexType>  
             <xsd:sequence>  
               <xsd:element name="CustomerID"  type="xsd:integer" />  
               <xsd:element name="CompanyName" type="xsd:string" />  
               <xsd:element name="City"        type="xsd:string" />  
             </xsd:sequence>  
           </xsd:complexType>  
         </xsd:element>  
       </xsd:sequence>  
      </xsd:complexType>  
     </xsd:element>  
</xsd:schema>

Create a file in your preferred text or XML editor, and save it as SampleXMLData.xml. To this file, add the following XML document:

<ROOT>  
  <Customers>  
    <CustomerID>1111</CustomerID>  
    <CompanyName>Sean Chai</CompanyName>  
    <City>New York</City>  
  </Customers>  
  <Customers>  
    <CustomerID>1112</CustomerID>  
    <CompanyName>Tom Johnston</CompanyName>  
     <City>Los Angeles</City>  
  </Customers>  
  <Customers>  
    <CustomerID>1113</CustomerID>  
    <CompanyName>Institute of Art</CompanyName>  
    <City>Chicago</City>  
  </Customers>  
</ROOT>

Create a file in your preferred text or XML editor, and save it as ValidateAndBulkload.vbs. To this file, add the VBScript code that is provided above at the beginning of this topic. Modify the connection string to provide the appropriate server name. Specify the appropriate path for the files that are specified as parameters to the Execute method.
Execute the VBScript code. XML Bulk Load loads the XML into the Cust table.

This is the equivalent XDR schema:

  
<Schema xmlns="urn:schemas-microsoft-com:xml-data"   
        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"    
        xmlns:sql="urn:schemas-microsoft-com:xml-sql" >   
  
   <ElementType name="CustomerID" dt:type="int" />  
   <ElementType name="CompanyName" dt:type="string" />  
   <ElementType name="City" dt:type="string" />  
  
   <ElementType name="ROOT" sql:is-constant="1">  
      <element type="Customers" />  
   </ElementType>  
  
   <ElementType name="Customers"  sql:relation="Cust" >  
      <element type="CustomerID"  sql:field="CustomerID" />  
      <element type="CompanyName" sql:field="CompanyName" />  
      <element type="City"        sql:field="City" />  
  
   </ElementType>  
</Schema>

B. Bulk loading XML data in multiple tables

In this example, the XML document consists of the <Customer> and <Order> elements.

<ROOT>  
  <Customers>  
    <CustomerID>1111</CustomerID>  
    <CompanyName>Sean Chai</CompanyName>  
    <City>NY</City>  
    <Order OrderID="1" />  
    <Order OrderID="2" />  
  </Customers>  
  <Customers>  
    <CustomerID>1112</CustomerID>  
    <CompanyName>Tom Johnston</CompanyName>  
     <City>LA</City>    
    <Order OrderID="3" />  
  </Customers>  
  <Customers>  
    <CustomerID>1113</CustomerID>  
    <CompanyName>Institute of Art</CompanyName>  
    <Order OrderID="4" />  
  </Customers>  
</ROOT>

This example bulk loads the XML data into two tables, Cust and CustOrder:

Cust(CustomerID, CompanyName, City)
CustOrder(OrderID, CustomerID)

The following XSD schema defines the XML view of these tables. The schema specifies the parent-child relationship between the <Customer> and <Order> elements.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
  <xsd:annotation>  
    <xsd:appinfo>  
      <sql:relationship name="CustCustOrder"  
          parent="Cust"  
          parent-key="CustomerID"  
          child="CustOrder"  
          child-key="CustomerID" />  
    </xsd:appinfo>  
  </xsd:annotation>  
  <xsd:element name="ROOT" sql:is-constant="1" >  
    <xsd:complexType>  
      <xsd:sequence>  
        <xsd:element name="Customers" sql:relation="Cust" >  
          <xsd:complexType>  
            <xsd:sequence>  
              <xsd:element name="CustomerID"  type="xsd:integer" />  
              <xsd:element name="CompanyName" type="xsd:string" />  
              <xsd:element name="City"        type="xsd:string" />  
              <xsd:element name="Order"   
                          sql:relation="CustOrder"  
                          sql:relationship="CustCustOrder" >  
                <xsd:complexType>  
                  <xsd:attribute name="OrderID" type="xsd:integer" />  
                </xsd:complexType>  
              </xsd:element>  
             </xsd:sequence>  
          </xsd:complexType>  
        </xsd:element>  
      </xsd:sequence>  
    </xsd:complexType>  
  </xsd:element>  
</xsd:schema>

XML Bulk Load uses the primary key/foreign key relationship specified above between the <Cust> and <CustOrder> elements to bulk load the data into both tables.

To test a sample bulk load

Create two tables in tempdb database:

USE tempdb;  
CREATE TABLE Cust(  
       CustomerID  int PRIMARY KEY,  
       CompanyName varchar(20),  
       City        varchar(20));  
CREATE TABLE CustOrder(        OrderID     int PRIMARY KEY,   
        CustomerID int FOREIGN KEY REFERENCES Cust(CustomerID));

Create a file in your preferred text or XML editor, and save it as SampleSchema.xml. Add the XSD schema that is provided in this example to the file.
Create a file in your preferred text or XML editor, and save it as SampleData.xml. Add the XML document that was provided earlier in this example to the file.
Create a file in your preferred text or XML editor, and save it as ValidateAndBulkload.vbs. To this file, add the VBScript code that is provided above at the beginning of this topic. Modify the connection string to provide the appropriate server and database name. Specify the appropriate path for the files that are specified as parameters to the Execute method.
Execute the VBScript code above. XML Bulk Load loads the XML document into the Cust and CustOrder tables.

This is the equivalent XDR schema:

  
<Schema xmlns="urn:schemas-microsoft-com:xml-data"   
        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"    
        xmlns:sql="urn:schemas-microsoft-com:xml-sql" >   
   <ElementType name="CustomerID" dt:type="int" />  
   <ElementType name="CompanyName" dt:type="string" />  
   <ElementType name="City" dt:type="string" />  
  
   <ElementType name="ROOT" sql:is-constant="1">  
      <element type="Customers" />  
   </ElementType>  
  
   <ElementType name="Customers" sql:relation="Cust" >  
      <element type="CustomerID" sql:field="CustomerID" />  
      <element type="CompanyName" sql:field="CompanyName" />  
      <element type="City" sql:field="City" />  
      <element type="Order" >  
<sql:relationship  
                key-relation="Cust"  
                key="CustomerID"  
                foreign-key="CustomerID"  
                foreign-relation="CustOrder" />  
      </element>  
   </ElementType>  
    <ElementType name="Order" sql:relation="CustOrder" >  
      <AttributeType name="OrderID" />  
      <AttributeType name="CustomerID" />  
      <attribute type="OrderID" />  
      <attribute type="CustomerID" />  
    </ElementType>  
</Schema>

C. Using chain relationships in the schema to bulk load XML

This example illustrates how the M:N relationship that is specified in the mapping schema is used by XML Bulk Load to load data in a table that represents an M:N relationship.

For example, consider this XSD schema:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
<xsd:annotation>  
  <xsd:appinfo>  
    <sql:relationship name="OrderOD"  
          parent="Ord"  
          parent-key="OrderID"  
          child="OrderDetail"  
          child-key="OrderID" />  
  
    <sql:relationship name="ODProduct"  
          parent="OrderDetail"  
          parent-key="ProductID"  
          child="Product"  
          child-key="ProductID"   
          inverse="true"/>  
  </xsd:appinfo>  
</xsd:annotation>  
  
  <xsd:element name="ROOT" sql:is-constant="1" >  
    <xsd:complexType>  
      <xsd:sequence>  
        <xsd:element name="Order"   
                     sql:relation="Ord"   
                     sql:key-fields="OrderID" >  
          <xsd:complexType>  
            <xsd:sequence>  
             <xsd:element name="Product"  
                          sql:relation="Product"   
                          sql:key-fields="ProductID"  
                          sql:relationship="OrderOD ODProduct">  
               <xsd:complexType>  
                 <xsd:attribute name="ProductID" type="xsd:int" />  
                 <xsd:attribute name="ProductName" type="xsd:string" />  
               </xsd:complexType>  
             </xsd:element>  
           </xsd:sequence>  
           <xsd:attribute name="OrderID"   type="xsd:integer" />   
           <xsd:attribute name="CustomerID"   type="xsd:string" />  
         </xsd:complexType>  
       </xsd:element>  
      </xsd:sequence>  
    </xsd:complexType>  
  </xsd:element>  
</xsd:schema>

The schema specifies an <Order> element with a <Product> child element. The <Order> element maps to Ord table and the <Product> element maps to the Product table in the database. The chain relationship specified on the <Product> element identifies a M:N relationship represented by the OrderDetail table. (An order can include many products, and a product can be included in many orders.)

When you are bulk loading an XML document with this schema, records are added to the Ord, Product, and OrderDetail tables.

To test a working sample

Create three tables:

CREATE TABLE Ord (  
         OrderID     int  PRIMARY KEY,  
         CustomerID  varchar(5));  
GO  
CREATE TABLE Product (  
         ProductID   int PRIMARY KEY,  
         ProductName varchar(20));  
GO  
CREATE TABLE OrderDetail (  
       OrderID     int FOREIGN KEY REFERENCES Ord(OrderID),  
       ProductID   int FOREIGN KEY REFERENCES Product(ProductID),  
                   CONSTRAINT OD_key PRIMARY KEY (OrderID, ProductID));  
GO

Save the schema that is provided above in this example as SampleSchema.xml.

Save the following sample XML data as SampleXMLData.xml:

<ROOT>    
  <Order OrderID="1" CustomerID="ALFKI">  
    <Product ProductID="1" ProductName="Chai" />  
    <Product ProductID="2" ProductName="Chang" />  
  </Order>  
  <Order OrderID="2" CustomerID="ANATR">  
    <Product ProductID="3" ProductName="Aniseed Syrup" />  
    <Product ProductID="4" ProductName="Gumbo Mix" />  
  </Order>  
</ROOT>

Create a file in your preferred text or XML editor, and save it as ValidateAndBulkload.vbs. To this file, add the VBScript code that is provided above at the beginning of this topic. Modify the connection string to provide the appropriate server and database name. Uncomment the following lines from the source code for this example.
```
objBL.CheckConstraints = True  
objBL.Transaction=True  
```
Execute the VBScript code. XML Bulk Load loads the XML document into the Ord and Product tables.

D. Bulk loading in identity type columns

This example illustrates how bulk load handles identity type columns. In the example, data is bulk loaded into three tables (Ord, Product, and OrderDetail).

In these tables:

OrderID in the Ord table is an identity type column
ProductID in the Product table is an identity type column.
OrderID and ProductID columns in the OrderDetail are foreign key columns referring to corresponding primary key columns in the Ord and Product tables.

The following are the table schemas for this example:

Ord (OrderID, CustomerID)  
Product (ProductID, ProductName)  
OrderDetail (OrderID, ProductID)

In this example of XML Bulk Load, the KeepIdentity property of the BulkLoad object model is set to false. Therefore, SQL Server generates identity values for the ProductID and OrderID columns in the Product and Ord tables, respectively (any values provided in the documents to be bulk loaded are ignored).

In this case, XML Bulk Load identifies the primary key/foreign key relationship among tables. Bulk Load first inserts records in the tables with the primary key, then propagates the identity value generated by SQL Server to the tables with foreign key columns. In the following example, XML Bulk Load inserts data in tables in this order:

Product
Ord
OrderDetail

Note

In order to propagate identity values generated in the Products and Orders tables, the processing logic requires XML Bulk Load to keep track of these values for later insertion into the OrderDetails table. To do that, XML Bulk Load creates intermediate tables, populates the data in these tables, and later removes them.

To test a working sample

Create these tables:

CREATE TABLE Ord (  
         OrderID     int identity(1,1)  PRIMARY KEY,  
         CustomerID  varchar(5));  
GO  
CREATE TABLE Product (  
         ProductID   int identity(1,1) PRIMARY KEY,  
         ProductName varchar(20));  
GO  
CREATE TABLE OrderDetail (  
       OrderID     int FOREIGN KEY REFERENCES Ord(OrderID),  
       ProductID   int FOREIGN KEY REFERENCES Product(ProductID),  
                   CONSTRAINT OD_key PRIMARY KEY (OrderID, ProductID));  
GO

Create a file in your preferred text or XML editor, and save it as SampleSchema.xml. Add this XSD schema to this file.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
 <xsd:annotation>  
   <xsd:appinfo>  
    <sql:relationship name="OrderOD"  
          parent="Ord"  
          parent-key="OrderID"  
          child="OrderDetail"  
          child-key="OrderID" />  
    <sql:relationship name="ODProduct"  
          parent="OrderDetail"  
          parent-key="ProductID"  
          child="Product"  
          child-key="ProductID"   
          inverse="true"/>  
   </xsd:appinfo>  
 </xsd:annotation>  

  <xsd:element name="Order" sql:relation="Ord"   
                            sql:key-fields="OrderID" >  
   <xsd:complexType>  
     <xsd:sequence>  
        <xsd:element name="Product" sql:relation="Product"   
                     sql:key-fields="ProductID"  
                     sql:relationship="OrderOD ODProduct">  
          <xsd:complexType>  
             <xsd:attribute name="ProductID" type="xsd:int" />  
             <xsd:attribute name="ProductName" type="xsd:string" />  
          </xsd:complexType>  
        </xsd:element>  
     </xsd:sequence>  
        <xsd:attribute name="OrderID"   type="xsd:integer" />   
        <xsd:attribute name="CustomerID"   type="xsd:string" />  
    </xsd:complexType>  
  </xsd:element>  
</xsd:schema>

Create a file in your preferred text or XML editor, and save it as SampleXMLData.xml. Add the following XML document.

<ROOT>    
  <Order OrderID="11" CustomerID="ALFKI">  
    <Product ProductID="11" ProductName="Chai" />  
    <Product ProductID="22" ProductName="Chang" />  
  </Order>  
  <Order OrderID="22" CustomerID="ANATR">  
     <Product ProductID="33" ProductName="Aniseed Syrup" />  
    <Product ProductID="44" ProductName="Gumbo Mix" />  
  </Order>  
</ROOT>

Create a file in your preferred text or XML editor, and save it as ValidateAndBulkload.vbs. To this file, add the following VBScript code. Modify the connection string to provide the appropriate server and database name. Specify the appropriate path for the files that serve as parameters to the Execute method.

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")  
objBL.ConnectionString = "provider=SQLOLEDB;data source=localhost;database=tempdb;integrated security=SSPI"  
objBL.ErrorLogFile = "C:\error.log"  
objBL.CheckConstraints = True  
objBL.Transaction = False  
objBL.KeepIdentity = False  
objBL.Execute "SampleSchema.xml", "SampleXMLData.xml"  
Set objBL = Nothing  
MsgBox "Done."

Execute the VBScript code. The XML Bulk Load will load the data into the appropriate tables.

E. Generating table schemas before bulk loading

XML Bulk Load can optionally generate the tables if they do not exist before bulk loading. Setting the SchemaGen property of the SQLXMLBulkLoad object to TRUE does this. You can also optionally request XML Bulk Load to drop any existing tables and re-create them by setting the SGDropTables property to TRUE. The following VBScript example illustrates the use of these properties.

Also, this example sets two additional properties to TRUE:

CheckConstraints. Setting this property to TRUE ensures that the data being inserted into the tables does not violate any constraints that have been specified on the tables (in this case the PRIMARY KEY/FOREIGN KEY constraints specified between the Cust and CustOrder tables). If there is a constraint violation, the bulk load fails.
XMLFragment. This property must be set to TRUE because the sample XML document (data source) contains no single, top-level element (and is thus a fragment).

This is the VBScript code:

Dim objBL   
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")  
objBL.ConnectionString = "provider=SQLOLEDB;data source=localhost;database=tempdb;integrated security=SSPI"  
objBL.ErrorLogFile = "c:\error.log"  
  
objBL.CheckConstraints=true  
objBL.XMLFragment = True  
objBL.SchemaGen = True  
objBL.SGDropTables = True  
  
objBL.Execute "SampleSchema.xml", "SampleXMLData.xml"  
Set objBL = Nothing

To test a working sample

Create a file in your preferred text or XML editor, and save it as SampleSchema.xml. Add the XSD schema that is provided in the earlier example, "Using chain relationships in the schema to bulk load XML", to the file.
Create a file in your preferred text or XML editor, and save it as SampleXMLData.xml. Add the XML document that is provided in the earlier example, "Using chain relationships in the schema to bulk load XML", to the file. Remove the <ROOT> element from the document (to make it a fragment).
Create a file in your preferred text or XML editor, and save it as ValidateAndBulkload.vbs. To this file, add the VBScript code in this example. Modify the connection string to provide the appropriate server and database name. Specify the appropriate path for the files that are specified as parameters to the Execute method.
Execute the VBScript code. The XML Bulk Load creates the necessary tables on the basis of the mapping schema that is provided and bulk loads the data in it.

F. Bulk loading from a stream

The Execute method of the XML Bulk Load object model takes two parameters. The first parameter is the mapping schema file. The second parameter provides the XML data that is to be loaded in the database. There are two ways to pass the XML data to the Execute method of XML Bulk Load:

Specify the file name as the parameter.
Pass a stream that contains the XML data.

This example illustrates how to bulk load from a stream.

VBScript first executes a SELECT statement to retrieve customer information from the Customers table in the Northwind database. Because the FOR XML clause is specified (with the ELEMENTS option) in the SELECT statement, the query returns an element-centric XML document of this form:

<Customer>  
  <CustomerID>..</CustomerID>  
  <CompanyName>..</CompanyName>  
  <City>..</City>  
</Customer>  
...

The script then passes the XML as a stream to the Execute method as its second parameter. The Execute method bulk loads the data into the Cust table.

Because this script sets the SchemaGen property to TRUE and SGDropTables property to TRUE, XML Bulk Load creates the Cust table in the specified database. (If the table already exists, it first drops the table and then re-creates it.)

This is the VBScript example:

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")  
Set objCmd = CreateObject("ADODB.Command")  
Set objConn = CreateObject("ADODB.Connection")  
Set objStrmOut = CreateObject ("ADODB.Stream")  
  
objBL.ConnectionString = "provider=SQLOLEDB;data source=localhost;database=tempdb;integrated security=SSPI"  
objBL.ErrorLogFile     = "c:\error.log"  
objBL.CheckConstraints = True  
objBL.SchemaGen        = True  
objBL.SGDropTables     = True  
objBL.XMLFragment      = True  
' Open a connection to the instance of SQL Server to get the source data.  
  
objConn.Open "provider=SQLOLEDB;server=(local);database=tempdb;integrated security=SSPI"  
Set objCmd.ActiveConnection = objConn  
objCmd.CommandText = "SELECT CustomerID, CompanyName, City FROM Customers FOR XML AUTO, ELEMENTS"  
  
' Open the return stream and execute the command.  
Const adCRLF = -1  
Const adExecuteStream = 1024  
objStrmOut.Open  
objStrmOut.LineSeparator = adCRLF  
objCmd.Properties("Output Stream").Value = objStrmOut  
objCmd.Execute , , adExecuteStream  
objStrmOut.Position = 0  
  
' Execute bulk load. Read source XML data from the stream.  
objBL.Execute "SampleSchema.xml", objStrmOut  
  
Set objBL = Nothing

The following XSD mapping schema provides the necessary information to create the table:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
<xsd:element name="ROOT" sql:is-constant="true" >  
  <xsd:complexType>  
    <xsd:sequence>  
      <xsd:element ref="Customers"/>  
    </xsd:sequence>  
  </xsd:complexType>  
</xsd:element>  
<xsd:element name="Customers" sql:relation="Cust" >  
  <xsd:complexType>  
    <xsd:sequence>  
      <xsd:element name="CustomerID"  
                   type="xsd:string"  
                   sql:datatype="nvarchar(5)"/>  
      <xsd:element name="CompanyName"  
                   type="xsd:string"  
                   sql:datatype="nvarchar(40)"/>  
      <xsd:element name="City"  
                   type="xsd:string"  
                   sql:datatype="nvarchar(40)"/>  
    </xsd:sequence>  
  </xsd:complexType>  
</xsd:element>  
</xsd:schema>

This is equivalent XDR schema:

<?xml version="1.0" ?>  
<Schema xmlns="urn:schemas-microsoft-com:xml-data"   
        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"    
        xmlns:sql="urn:schemas-microsoft-com:xml-sql" >  
   <ElementType name="CustomerID" dt:type="int" />  
   <ElementType name="CompanyName" dt:type="string" />  
   <ElementType name="City" dt:type="string" />  
  
   <ElementType name="root" sql:is-constant="1">  
      <element type="Customers" />  
   </ElementType>  
  
   <ElementType name="Customers" sql:relation="Cust"  >  
      <element type="CustomerID" sql:field="CustomerID" />  
      <element type="CompanyName" sql:field="CompanyName" />  
      <element type="City" sql:field="City" />  
    </ElementType>  
</Schema>

Opening a Stream on an Existing File

You can also open a stream on an existing XML data file and pass the stream as a parameter to the Execute method (instead of passing the file name as the parameter).

This is a Visual Basic example of passing a stream as the parameter:

Private Sub Form_Load()  
Dim objBL As New SQLXMLBulkLoad  
Dim objStrm As New ADODB.Stream  
Dim objFileSystem As New Scripting.FileSystemObject  
Dim objFile As Scripting.TextStream  
  
MsgBox "Begin BulkLoad..."  
objBL.ConnectionString = "provider=SQLOLEDB;data source=localhost;database=tempdb;integrated security=SSPI"  
objBL.ErrorLogFile = "c:\error.log"  
objBL.CheckConstraints = True  
objBL.SchemaGen = True  
objBL.SGDropTables = True  
' Here again a stream is specified that contains the source data   
' (instead of the file name). But this is just an illustration.  
' Usually this is useful if you have an XML data   
' stream that is created by some other means that you want to bulk   
' load. This example starts with an XML text file, so it may not be the   
' best to use a stream (you can specify the file name directly).  
' Here you could have specified the file name itself.   
Set objFile = objFileSystem.OpenTextFile("c:\SampleData.xml")  
objStrm.Open  
objStrm.WriteText objFile.ReadAll  
objStrm.Position = 0  
objBL.Execute "c:\SampleSchema.xml", objStrm  
  
Set objBL = Nothing  
MsgBox "Done."  
End Sub

To test the application, use the following XML document in a file (SampleData.xml) and the XSD schema that is provided in this example:

This is the XML source data (SampleData.xml):

<ROOT>  
  <Customers>  
    <CustomerID>1111</CustomerID>  
    <CompanyName>Hanari Carnes</CompanyName>  
    <City>NY</City>  
    <Order OrderID="1" />  
    <Order OrderID="2" />  
  </Customers>  
  
  <Customers>  
    <CustomerID>1112</CustomerID>  
    <CompanyName>Toms Spezialitten</CompanyName>  
     <City>LA</City>  
    <Order OrderID="3" />  
  </Customers>  
  <Customers>  
    <CustomerID>1113</CustomerID>  
    <CompanyName>Victuailles en stock</CompanyName>  
    <Order CustomerID= "4444" OrderID="4" />  
</Customers>  
</ROOT>

This is the equivalent XDR schema:

<?xml version="1.0" ?>  
<Schema xmlns="urn:schemas-microsoft-com:xml-data"   
        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"    
        xmlns:sql="urn:schemas-microsoft-com:xml-sql" >  
  
    <ElementType name="Order" sql:relation="CustOrder" >  
      <AttributeType name="OrderID" />  
      <AttributeType name="CustomerID" />  
      <attribute type="OrderID" />  
      <attribute type="CustomerID" />  
    </ElementType>  
  
   <ElementType name="CustomerID" dt:type="int" />  
   <ElementType name="CompanyName" dt:type="string" />  
   <ElementType name="City" dt:type="string" />  
  
   <ElementType name="root" sql:is-constant="1">  
      <element type="Customers" />  
   </ElementType>  
  
   <ElementType name="Customers" sql:relation="Cust"  >  
      <element type="CustomerID" sql:field="CustomerID" />  
      <element type="CompanyName" sql:field="CompanyName" />  
      <element type="City" sql:field="City" />  
      <element type="Order" >  
             <sql:relationship  
                key-relation="Cust"  
                key="CustomerID"  
                foreign-key="CustomerID"  
                foreign-relation="CustOrder" />  
      </element>  
   </ElementType>  
</Schema>

G. Bulk loading in overflow columns

If the mapping schema specifies an overflow column by using the sql:overflow-field annotation, XML Bulk Load copies all unconsumed data from the source document into this column.

Consider this XSD schema:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
<xsd:annotation>  
  <xsd:appinfo>  
    <sql:relationship name="CustCustOrder"  
          parent="Cust"  
          parent-key="CustomerID"  
          child="CustOrder"  
          child-key="CustomerID" />  
  </xsd:appinfo>  
</xsd:annotation>  
  <xsd:element name="Customers" sql:relation="Cust"  
                                sql:overflow-field="OverflowColumn" >  
   <xsd:complexType>  
     <xsd:sequence>  
       <xsd:element name="CustomerID"  type="xsd:integer" />  
       <xsd:element name="CompanyName" type="xsd:string" />  
       <xsd:element name="City"        type="xsd:string" />  
       <xsd:element name="Order"   
                          sql:relation="CustOrder"  
                          sql:relationship="CustCustOrder" >  
         <xsd:complexType>  
          <xsd:attribute name="OrderID" type="xsd:integer" />  
          <xsd:attribute name="CustomerID" type="xsd:integer" />  
         </xsd:complexType>  
       </xsd:element>  
     </xsd:sequence>  
    </xsd:complexType>  
  </xsd:element>  
</xsd:schema>

The schema identifies an overflow column (OverflowColumn) for the Cust table. As a result, all unconsumed XML data for each <Customer> element is added to this column.

Note

All abstract elements (elements for which abstract="true" is specified) and all prohibited attributes (attributes for which prohibited="true" is specified) are considered overflow by XML Bulk Load and are added to the overflow column, if specified. (Otherwise, they are ignored.)

To test a working sample

Create two tables in tempdb database:

USE tempdb;  
CREATE TABLE Cust (  
              CustomerID     int         PRIMARY KEY,  
              CompanyName    varchar(20) NOT NULL,  
              City           varchar(20) DEFAULT 'Seattle',  
              OverflowColumn nvarchar(200));  
GO  
CREATE TABLE CustOrder (  
              OrderID    int PRIMARY KEY,  
              CustomerID int FOREIGN KEY   
                             REFERENCES Cust(CustomerID));  
GO

Create a file in your preferred text or XML editor, and save it as SampleSchema.xml. Add the XSD schema that is provided in this example to the file.

Create a file in your preferred text or XML editor, and save it as SampleXMLData.xml. Add the following XML document to the file:

<ROOT>  
  <Customers>  
    <CustomerID>1111</CustomerID>  
    <CompanyName>Hanari Carnes</CompanyName>  
    <City><![CDATA[NY]]> </City>  
    <Junk>garbage in overflow</Junk>  
    <Order OrderID="1" />  
    <Order OrderID="2" />  
  </Customers>  

  <Customers>  
    <CustomerID>1112</CustomerID>  
    <CompanyName>Toms Spezialitten</CompanyName>  
     <![CDATA[LA]]>   
    <!-- <xyz><address>111 Maple, Seattle</address></xyz>   -->  
    <Order OrderID="3" />  
  </Customers>  
  <Customers>  
    <CustomerID>1113</CustomerID>  
    <CompanyName>Victuailles en stock</CompanyName>  
    <Order OrderID="4" />  
</Customers>  
</ROOT>

Create a file in your preferred text or XML editor, and save it as ValidateAndBulkload.vbs. To this file, add the following Microsoft Visual Basic Scripting Edition (VBScript) code. Modify the connection string to provide the appropriate server and database name. Specify the appropriate path for the files that are specified as parameters to the Execute method.
```
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")  
objBL.ConnectionString = "provider=SQLOLEDB;data source=localhost;database=tempdb;integrated security=SSPI"  
objBL.ErrorLogFile = "c:\error.log"  
objBL.CheckConstraints = True  
objBL.Execute "SampleSchema.xml", "SampleXMLData.xml"  
set objBL=Nothing  
```
Execute the VBScript code.

This is the equivalent XDR schema:

<?xml version="1.0" ?>  
<Schema xmlns="urn:schemas-microsoft-com:xml-data"   
        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"    
        xmlns:sql="urn:schemas-microsoft-com:xml-sql" >  
  
    <ElementType name="Order" sql:relation="CustOrder" >  
      <AttributeType name="OrderID" />  
      <AttributeType name="CustomerID" />  
      <attribute type="OrderID" />  
      <attribute type="CustomerID" />  
    </ElementType>  
  
   <ElementType name="CustomerID" dt:type="int" />  
   <ElementType name="CompanyName" dt:type="string" />  
   <ElementType name="City" dt:type="string" />  
  
   <ElementType name="root" sql:is-constant="1">  
      <element type="Customers" />  
   </ElementType>  
  
   <ElementType name="Customers" sql:relation="Cust"   
                       sql:overflow-field="OverflowColumn"  >  
      <element type="CustomerID" sql:field="CustomerID" />  
      <element type="CompanyName" sql:field="CompanyName" />  
      <element type="City" sql:field="City" />  
      <element type="Order" >  
             <sql:relationship  
                key-relation="Cust"  
                key="CustomerID"  
                foreign-key="CustomerID"  
                foreign-relation="CustOrder" />  
      </element>  
   </ElementType>  
</Schema>

H. Specifying the file path for temp files in transaction mode

When you are bulk loading in transaction mode (that is, when the Transaction property is set to TRUE), you also must set the TempFilePath property when either of the following conditions is true:

You are bulk loading to a remote server.
You want to use an alternate local drive or folder (one other than the path that is specified by the TEMP environment variable) to store the temporary files that are created in the transaction mode.

For example, the following VBScript code bulk loads data from the SampleXMLData.xml file into the database tables in transaction mode. The TempFilePath property is specified to set the path for the temporary files that are generated in transaction mode.

set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")  
objBL.ConnectionString = "provider=SQLOLEDB;data source=localhost;database=tempdb;integrated security=SSPI"  
objBL.ErrorLogFile = "c:\error.log"  
objBL.CheckConstraints = True  
objBL.Transaction=True  
objBL.TempFilePath="\\Server\MyDir"  
objBL.Execute "SampleSchema.xml", "SampleXMLData.xml"  
set objBL=Nothing

Note

The temporary file path must be a shared location that is accessible to the service account of the target instance of SQL Server and to the account that is running the bulk load application. Unless you are bulk loading on a local server, the temporary file path must be a UNC path (such as \\servername\sharename).

To test a working sample

Create this table in tempdb database:

USE tempdb;  
CREATE TABLE Cust (     CustomerID uniqueidentifier,   
      LastName  varchar(20));  
GO

Create a file in your preferred text or XML editor, and save it as SampleSchema.xml. Add the following XSD schema to the file:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
  <xsd:element name="ROOT" sql:is-constant="true" >  
    <xsd:complexType>  
      <xsd:sequence>  
        <xsd:element ref="Customers" />  
      </xsd:sequence>  
    </xsd:complexType>  
  </xsd:element>  

  <xsd:element name="Customers" sql:relation="Cust" >  
   <xsd:complexType>  
     <xsd:attribute name="CustomerID"  type="xsd:string" />  
     <xsd:attribute name="LastName" type="xsd:string" />  
   </xsd:complexType>  
  </xsd:element>  
</xsd:schema>

Create a file in your preferred text or XML editor, and save it as SampleXMLData.xml. Add the following XML document to the file:
```
<ROOT>  
<Customers CustomerID="6F9619FF-8B86-D011-B42D-00C04FC964FF"   
           LastName="Smith" />  
</ROOT>  
```

set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")  
objBL.ConnectionString = "provider=SQLOLEDB;data source=localhost;database=tempdb;integrated security=SSPI"  
objBL.ErrorLogFile = "c:\error.log"  
objBL.CheckConstraints = True  
objBL.Transaction=True  
objBL.TempFilePath="\\server\folder"  
objBL.Execute "SampleSchema.xml", "SampleXMLData.xml"  
set objBL=Nothing

Execute the VBScript code.

The schema must specify the corresponding sql:datatype for the CustomerID attribute when the value for CustomerID is specified as a GUID that includes braces ({ and }), such as:

<ROOT>  
<Customers CustomerID="{6F9619FF-8B86-D011-B42D-00C04FC964FF}"   
           LastName="Smith" />  
</ROOT>

This is the updated schema:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
  <xsd:element name="ROOT" sql:is-constant="true" >  
    <xsd:complexType>  
      <xsd:sequence>  
        <xsd:element ref="Customers" />  
      </xsd:sequence>  
    </xsd:complexType>  
  </xsd:element>  

  <xsd:element name="Customers" sql:relation="Cust" >  
   <xsd:complexType>  
     <xsd:attribute name="CustomerID"  type="xsd:string"   
                    sql:datatype="uniqueidentifier" />  
     <xsd:attribute name="LastName" type="xsd:string" />  
   </xsd:complexType>  
  </xsd:element>  
</xsd:schema>

When sql:datatype is specified identifying the column type as uniqueidentifier, the bulk load operation removes the braces ({ and }) from the CustomerID value before inserting it in the column.

This is the equivalent XDR schema:

<?xml version="1.0" ?>  
<Schema xmlns="urn:schemas-microsoft-com:xml-data"   
        xmlns:dt="urn:schemas-microsoft-com:datatypes"    
        xmlns:sql="urn:schemas-microsoft-com:xml-sql" >  
<ElementType name="ROOT" sql:is-constant="1">  
      <element type="Customers" />  
</ElementType>  
<ElementType name="Customers" sql:relation="Cust" >  
  <AttributeType name="CustomerID"  sql:datatype="uniqueidentifier" />  
  <AttributeType name="LastName"   />  
  
  <attribute type="CustomerID" />  
  <attribute type="LastName"   />  
</ElementType>  
</Schema>

I. Using an existing database connection with the ConnectionCommand property

You can use an existing ADO connection to bulk load XML. This is useful if XML Bulk Load is just one of many operations that will be performed on a data source.

The ConnectionCommand property enables you to use an existing ADO connection by using an ADO command object. This is illustrated in the following Visual Basic example:

Private Sub Form_Load()  
Dim objBL As New SQLXMLBulkLoad4  
Dim objCmd As New ADODB.Command  
Dim objConn As New ADODB.Connection  
  
'Open a connection to an instance of SQL Server.  
objConn.Open "provider=SQLOLEDB;data source=(local);database=tempdb;integrated security=SSPI"  
'Ask the Command object to use the connection just established.  
Set objCmd.ActiveConnection = objConn  
  
'Tell Bulk Load to use the active command object that is using the Connection obj.  
objBL.ConnectionCommand = objCmd  
objBL.ErrorLogFile = "c:\error.log"  
objBL.CheckConstraints = True  
'The Transaction property must be set to True if you use ConnectionCommand.  
objBL.Transaction = True  
objBL.Execute "SampleSchema.xml", "SampleXMLData.xml"  
Set objBL = Nothing  
End Sub

To test a working sample

Create two tables in tempdb database:

USE tempdb;  
CREATE TABLE Cust(  
               CustomerID   varchar(5) PRIMARY KEY,  
               CompanyName  varchar(30),  
               City         varchar(20));  
GO  
CREATE TABLE CustOrder(  
               CustomerID  varchar(5) references Cust (CustomerID),  
               OrderID     varchar(5) PRIMARY KEY);  
GO

Create a file in your preferred text or XML editor, and save it as SampleSchema.xml. Add the following XSD schema to the file:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
<xsd:annotation>  
  <xsd:appinfo>  
    <sql:relationship name="CustCustOrder"  
          parent="Cust"  
          parent-key="CustomerID"  
          child="CustOrder"  
          child-key="CustomerID" />  
  </xsd:appinfo>  
</xsd:annotation>  
  <xsd:element name="ROOT" sql:is-constant="true" >  
    <xsd:complexType>  
      <xsd:sequence>  
        <xsd:element ref="Customers" />  
      </xsd:sequence>  
    </xsd:complexType>  
  </xsd:element>  
  <xsd:element name="Customers" sql:relation="Cust" >  
   <xsd:complexType>  
     <xsd:sequence>  
       <xsd:element name="CustomerID"  type="xsd:integer" />  
       <xsd:element name="CompanyName" type="xsd:string" />  
       <xsd:element name="City"        type="xsd:string" />  
       <xsd:element name="Order"   
                          sql:relation="CustOrder"  
                          sql:relationship="CustCustOrder" >  
         <xsd:complexType>  
          <xsd:attribute name="OrderID" type="xsd:integer" />  
          <xsd:attribute name="CustomerID" type="xsd:integer" />  
         </xsd:complexType>  
       </xsd:element>  
     </xsd:sequence>  
    </xsd:complexType>  
  </xsd:element>  
</xsd:schema>

Create a file in your preferred text or XML editor, and save it as SampleXMLData.xml. Add the following XML document to the file:

<ROOT>  
  <Customers>  
    <CustomerID>1111</CustomerID>  
    <CompanyName>Hanari Carnes</CompanyName>  
    <City>NY</City>  
    <Order OrderID="1" />  
    <Order OrderID="2" />  
  </Customers>  

  <Customers>  
    <CustomerID>1112</CustomerID>  
    <CompanyName>Toms Spezialitten</CompanyName>  
     <City>LA</City>  
    <Order OrderID="3" />  
  </Customers>  
  <Customers>  
    <CustomerID>1113</CustomerID>  
    <CompanyName>Victuailles en stock</CompanyName>  
    <Order OrderID="4" />  
</Customers>  
</ROOT>

Create a Visual Basic (Standard EXE) application and the preceding code. Add these references to the project:
```
Microsoft XML BulkLoad for SQL Server 4.0 Type Library  
Microsoft ActiveX Data objects 2.6 Library  
```
Execute the application.

This is the equivalent XDR schema:

<?xml version="1.0" ?>  
<Schema xmlns="urn:schemas-microsoft-com:xml-data"   
        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"    
        xmlns:sql="urn:schemas-microsoft-com:xml-sql" >  
  
   <ElementType name="CustomerID" dt:type="int" />  
   <ElementType name="CompanyName" dt:type="string" />  
   <ElementType name="City" dt:type="string" />  
  
   <ElementType name="root" sql:is-constant="1">  
      <element type="Customers" />  
   </ElementType>  
  
   <ElementType name="Customers" sql:relation="Cust"  >  
      <element type="CustomerID" sql:field="CustomerID" />  
      <element type="CompanyName" sql:field="CompanyName" />  
      <element type="City" sql:field="City" />  
      <element type="Order" >  
         <sql:relationship  
                key-relation="Cust"  
                key="CustomerID"  
                foreign-key="CustomerID"  
                foreign-relation="CustOrder" />  
      </element>  
   </ElementType>  
    <ElementType name="Order" sql:relation="CustOrder" >  
      <AttributeType name="OrderID" />  
      <AttributeType name="CustomerID" />  
      <attribute type="OrderID" />  
      <attribute type="CustomerID" />  
    </ElementType>  
</Schema>

J. Bulk loading in xml Data Type columns

If the mapping schema specifies a xml data type column by using the sql:datatype="xml" annotation, XML Bulk Load can copy XML child elements for the mapped field from the source document into this column.

Consider the following XSD schema, which maps a view of the Production.ProductModel table in the AdventureWorks sample database. In this table, the CatalogDescription field of xml data type is mapped to a <Desc> element using the sql:field and sql:datatype="xml" annotations.

<?xml version="1.0" encoding="utf-8" ?>  
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"   
           xmlns:sql="urn:schemas-microsoft-com:mapping-schema"  
           xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription">   
  <xsd:element name="ProductModel"  sql:relation="Production.ProductModel" >  
    <xsd:complexType>  
      <xsd:sequence>  
        <xsd:element name="Name" type="xs:string"></xsd:element>  
        <xsd:element name="Desc" sql:field="CatalogDescription" sql:datatype="xml">  
        <xsd:complexType>  
          <xsd:sequence>  
            <xsd:element name="ProductDescription">  
              <xsd:complexType>  
                <xsd:sequence>  
                  <xsd:element name="Summary" type="xs:anyType"/>  
                </xsd:sequence>  
              </xsd:complexType>  
            </xsd:element>  
          </xsd:sequence>  
        </xsd:complexType>  
        </xsd:element>   
     </xsd:sequence>  
     <xsd:attribute name="ProductModelID" sql:field="ProductModelID" />  
    </xsd:complexType>  
  </xsd:element>  
</xsd:schema>

To test a working sample

Verify that the AdventureWorks sample database is installed.
Create a file in your preferred text or XML editor, and save it as SampleSchema.xml. Copy the XSD schema above and paste it into the file and save it.

Create a file in your preferred text or XML editor, and save it as SampleXMLData.xml. Copy the following XML document below and paste it into the file and save it in the same folder as was used for the previous step.

<ProductModel ProductModelID="2005">  
    <Name>Mountain-100 (2005 model)</Name>  
    <Desc><?xml-stylesheet href="ProductDescription.xsl" type="text/xsl"?>  
        <p1:ProductDescription xmlns:p1="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"   
              xmlns:wm="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"   
              xmlns:wf="https://www.adventure-works.com/schemas/OtherFeatures"   
              xmlns:html="http://www.w3.org/1999/xhtml"   
              xmlns="">  
            <p1:Summary>  
                <html:p>Our top-of-the-line competition mountain bike.   
      Performance-enhancing options include the innovative HL Frame,   
      super-smooth front suspension, and traction for all terrain.  
                        </html:p>  
            </p1:Summary>  
            <p1:Manufacturer>  
                <p1:Name>AdventureWorks</p1:Name>  
                <p1:Copyright>2002-2005</p1:Copyright>  
                <p1:ProductURL>HTTP://www.Adventure-works.com</p1:ProductURL>  
            </p1:Manufacturer>  
            <p1:Features>These are the product highlights.   
                 <wm:Warranty>  
                    <wm:WarrantyPeriod>3 years</wm:WarrantyPeriod>  
                    <wm:Description>parts and labor</wm:Description>  
                </wm:Warranty><wm:Maintenance>  
                    <wm:NoOfYears>10 years</wm:NoOfYears>  
                    <wm:Description>maintenance contract available through your dealer or any AdventureWorks retail store.</wm:Description>  
                </wm:Maintenance><wf:wheel>High performance wheels.</wf:wheel><wf:saddle>  
                    <html:i>Anatomic design</html:i> and made from durable leather for a full-day of riding in comfort.</wf:saddle><wf:pedal>  
                    <html:b>Top-of-the-line</html:b> clipless pedals with adjustable tension.</wf:pedal><wf:BikeFrame>Each frame is hand-crafted in our Bothell facility to the optimum diameter   
      and wall-thickness required of a premium mountain frame.   
      The heat-treated welded aluminum frame has a larger diameter tube that absorbs the bumps.</wf:BikeFrame><wf:crankset> Triple crankset; aluminum crank arm; flawless shifting. </wf:crankset></p1:Features>  
            <!-- add one or more of these elements... one for each specific product in this product model -->  
            <p1:Picture>  
                <p1:Angle>front</p1:Angle>  
                <p1:Size>small</p1:Size>  
                <p1:ProductPhotoID>118</p1:ProductPhotoID>  
            </p1:Picture>  
            <!-- add any tags in <specifications> -->  
            <p1:Specifications> These are the product specifications.  
                   <Material>Aluminum Alloy</Material><Color>Available in most colors</Color><ProductLine>Mountain bike</ProductLine><Style>Unisex</Style><RiderExperience>Advanced to Professional riders</RiderExperience></p1:Specifications>  
        </p1:ProductDescription>  
    </Desc>  
</ProductModel>

Create a file in your preferred text or XML editor, and save it as BulkloadXml.vbs. Copy the following VBScript code and paste it into the file. Save it in the same folder as was used for the previous XML data and schema files.

set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")  
objBL.ConnectionString = "provider=SQLOLEDB;data source=MyServer;database=AdventureWorks;integrated security=SSPI"  

Dim fso, sAppPath  
Set fso = CreateObject("Scripting.FileSystemObject")   
sAppPath = fso.GetFolder(".")   

objBL.ErrorLogFile = sAppPath & "\error.log"  

'Execute XML bulkload using file.  
objBL.Execute "SampleSchema.xml", "SampleXMLData.xml"  
set objBL=Nothing

Execute the BulkloadXml.vbs script.

Share via

XML Bulk Load Examples (SQLXML 4.0)

Bulk Loader Script (ValidateAndBulkload.vbs)

A. Bulk loading XML in a table

To test a sample bulk load

B. Bulk loading XML data in multiple tables

To test a sample bulk load

C. Using chain relationships in the schema to bulk load XML

To test a working sample

D. Bulk loading in identity type columns

To test a working sample

E. Generating table schemas before bulk loading

To test a working sample

F. Bulk loading from a stream

Opening a Stream on an Existing File

G. Bulk loading in overflow columns

To test a working sample

H. Specifying the file path for temp files in transaction mode

To test a working sample

I. Using an existing database connection with the ConnectionCommand property

To test a working sample

J. Bulk loading in xml Data Type columns

To test a working sample

Feedback

Additional resources