All the examples we saw in the previous posts loaded information into a single table. There are times when you might need to load data into multiple tables. Some times these tables may be related and some times they may not be. In this post, we will see an example that shows how to load data from an XML document to two unrelated tables. The XML document contains two sections: "Customers" and "Employees". Information from the "Customers" node should be loaded into the Customer table and the information from the "Employees" section should be loaded to the Employee table.

Here is the sample XML document that we will process in this session.

<AppData>
  <Customers>
    <Customer>
      <Number>1001</Number>
      <Name>Jacob Sebastian</Name>
    </Customer>
    <Customer>
      <Number>1002</Number>
      <Name>Smith Jones</Name>
    </Customer>
  </Customers>
  <Employees>
    <Employee>
      <Number>505</Number>
      <Name>Jacob</Name>
    </Employee>
    <Employee>
      <Number>606</Number>
      <Name>Steve</Name>
    </Employee>
  </Employees>
</AppData>

Note that the root element "AppData" contains two child elements, "Customers" and "Employees". We will write a schema that will pull data from each section into two different tables.

Let us create the tables for storing the data.

IF OBJECT_ID('Customers') IS NOT NULL
    DROP TABLE Customers
GO

CREATE TABLE Customers (
    CustomerNumber VARCHAR(15), 
    CustomerName VARCHAR(40)
)

IF OBJECT_ID('Employees') IS NOT NULL
    DROP TABLE Employees
GO

CREATE TABLE Employees (
    EmployeeNumber VARCHAR(15), 
    EmployeeName VARCHAR(40)
)

Now, let us write the schema needed for pulling the data out. We will take the schema we developed in the previous session and modify it to handle the employee information. Here is the schema that we need.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="AppData" sql:is-constant="1" >
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Customers" sql:is-constant="1">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="Customer" sql:relation="Customers"
                           maxOccurs="unbounded">
                <xsd:complexType>
                  <xsd:sequence>
                    <xsd:element name="Number" type="xsd:string"
                                   sql:field="CustomerNumber"/>
                    <xsd:element name="Name" type="xsd:string"
                                   sql:field="CustomerName"/>
                  </xsd:sequence>
                </xsd:complexType>
              </xsd:element>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
        <xsd:element name="Employees" sql:is-constant="1">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="Employee" sql:relation="Employees"
                           maxOccurs="unbounded">
                <xsd:complexType>
                  <xsd:sequence>
                    <xsd:element name="Number" type="xsd:string"
                                   sql:field="EmployeeNumber"/>
                    <xsd:element name="Name" type="xsd:string"
                                   sql:field="EmployeeName"/>
                  </xsd:sequence>
                </xsd:complexType>
              </xsd:element>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>      
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Note that we have two complex type elements under the AppData element. The first child element processes customer information and the second element processes employee information.

Let us save the XML data file as "data3.xml" and the schema file as "schema3.xml". Let us then run the VBS code to perform the data processing.

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = "provider=SQLOLEDB;data source=JACOBXPS\SQL2005;database=XmlWorkshop;integrated security=SSPI"
objBL.ErrorLogFile = "C:\temp\error.log"
objBL.Execute "schema3.xml", "data3.xml"
Set objBL = Nothing
MsgBox "Done."

Save the above VBS code to a .vbs file and change the connection string and file/folder names to match with what you have in your computer. Run the script by double clicking on the file or from command prompt.

If the process completes successfully, you will see a message box that says "Done". If it fails, you will see a message box with the error message. Details of the error will be written to the error log too.

Let us now look at the target tables and see if the data is correctly inserted or not.

select * from customers
/*
CustomerNumber  CustomerName
--------------- ----------------------------------------
1001            Jacob Sebastian
1002            Smith Jones
*/
select * from employees
/*
EmployeeNumber  EmployeeName
--------------- ----------------------------------------
505             Jacob
606             Steve
*/

Refer the previous example for sample code that shows how to run XMLBULKLOAD from VB.NET and C#.NET. You could take the code from the previous example and just change the filename and connection string.

See Also

SQLXML Labs - A collection of SQLXML Sample Scripts