Bulk XML Load Tutorial

by | Feb 10, 2010 | BizTalk Community Blogs via Syndication | 0 comments

Since I have had to explain how to use the BulkXML adapter a few times, I decided to create a quick tutorial on how to use it. Also, this is a tutorial for me to follow, since every time I do this, I re-learn the same things over and over.

The first thing is we are going to create the tables in the database with the relationships. You can get a jump start by downloading this script:

You will have a table structure like this:

and an xml file that looks like this

<ns0:File xmlns:ns0="http://BulkXMLSample.Input">
  <FamilyRecord>
    <Name>Stott</Name>
    <Address>100 N 100 W</Address>
    <City>Logan</City>
    <State>UT</State>
    <Zip>84321</Zip>
    <Child><Name>Bob</Name><Sex>M</Sex></Child>
    <Child><Name>Susan</Name><Sex>F</Sex></Child>
    <Child><Name>Mary</Name><Sex>F</Sex></Child>
    <Child><Name>Jane</Name><Sex>F</Sex></Child>
    <Child><Name>Jeb</Name><Sex>M</Sex></Child>
  </FamilyRecord>
  <FamilyRecord>
    <Name>Dahl</Name>
    <Address>45 Polk Ave</Address>
    <City>Blaine</City>
    <State>MN</State>
    <Zip>54321</Zip>
    <Child><Name>Jason</Name><Sex>M</Sex></Child>
  </FamilyRecord>
  <FamilyRecord>
    <Name>Matthew</Name>
    <Address>232 Acadia Ave</Address>
    <City>St. Louis</City>
    <State>MO</State>
    <Zip>78223</Zip>
    <Child><Name>William</Name><Sex>M</Sex></Child>
    <Child><Name>Jennifer</Name><Sex>F</Sex>
    </Child>
  </FamilyRecord>
</ns0:File>

The next thing is to create the input and output schema

and the map

Now we need to create the ’mapping’ schema that maps the xml data that BizTalk creates and maps it into the database table.

To speed up the creation of the mapping download the following file and place it in the schema directory of Visual Studio (%InstallRoot%\Xml\Schemas):

Let’s open up the output schema using the XML Editor:

Make the following changes to the output schema:

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns="http://BulkXMLSample.Output" targetNamespace="http://BulkXMLSample.Output" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <!--Add xmlns:sql="urn:schemas-microsoft-com:mapping-schema" to xs:schema element-->
  <xs:annotation>
    <xs:appinfo>
      <b:schemaInfo root_reference="File" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" />
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="File" sql:relation="Header" sql:key-fields="fileid">
    <!--We want to 'map' the element File to the table Header, since they are different we have to use the sql:relation-->
    <!--We also need to tell which is the primary key column-->
    <xs:complexType>
      <xs:sequence>
        <xs:element name="FileName" type="xs:string" />
        <xs:element name="Date" type="xs:string" />
        <xs:element maxOccurs="unbounded" name="FamilyRecord" sql:relation="Family" sql:key-fields="FamilyId">
          <!--Now we need to start defining how this and its parent are related-->
          <xs:annotation>
            <xs:appinfo>
              <sql:relationship parent="Header" parent-key="fileid" child-key="HeaderId" child="Family" />
              <!--The parent and parent-key are defined in the parent table and child and child-key are the columns in the current table-->
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Name" type="xs:string" />
              <xs:element name="Address" type="xs:string" />
              <xs:element name="City" type="xs:string" />
              <xs:element name="State" type="xs:string" />
              <xs:element name="Zip" type="xs:string" />
              <xs:element maxOccurs="unbounded" name="Child" sql:relation="Child" sql:key-fields="ChildId">
                <!--Added the sql:relation and sql:key-fields annotations-->
                <xs:annotation>
                  <xs:appinfo>
                    <sql:relationship parent="Family" parent-key="FamilyId" child="Child" child-key="FamilyId" />
                  </xs:appinfo>
                </xs:annotation>
                <!--I added the entire xs:annotation section-->
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="Name" type="xs:string" sql:field="ChildName" />
                    <!--Added the sql:field since it was different-->
                    <xs:element name="Sex" type="xs:string" sql:field="Gender"/>
                    <!--Added the sql:field since it was different-->
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Now lets save a copy of this output schema in the send handler folder

The way the adapter knows which schema to use is it starts 7 characters into the target namespace and replaces # with _ and adds .xsd, so our http://targetnamespace#rootnode is http://BulkXMLSample.Output#File so it becomes BulkXMLSample.Output_File.xsd

In the orchestration, in the construct shape I have a message assignment shape that has the following code:

xpath(OutputMsg.MessagePart,"/*[local-name()='File' and namespace-uri()='http://BulkXMLSample.Output']/*[local-name()='FileName' and namespace-uri()='']")=System.IO.Path.GetFileName(InputMsg(FILE.ReceivedFileName));

I deploy it and set the send port to point to the database:

Bind it and run a file through:

I look in the event log and see this:

Event Type: Error

Event Source: SQLBulkXML

Event Category: None

Event ID: 0

Date: 2/9/2010

Time: 9:52:27 PM

User: N/A

Computer:
Description:

The column ‘Date’ was defined in the schema, but does not exist in the database.

at SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class.Execute(String bstrSchemaFile, Object vDataFile)

at StottIS.BulkLoad.BulkLoadXML.ThreadProc()

SQLXMLBulkLoad4Class error: The column ‘Date’ was defined in the schema, but does not exist in the database.

The Visual Basic Script to test this message is here:C:\Users\Administrator\AppData\Local\Temp\1e0e7b04-92e1-4f39-a591-2bdedef93a8f.vbs

So without having to run data through again, I can go and look at the data that was attempting to be inserted into the database and run it manually.

I have one of the elements mapped incorrectly to the column, so I modify the schema:

        <xs:element name="Date" type="xs:string" sql:field="ImportDate" />
        <!--I need to map the Date element into the ImportDate column, I use the sql:field attribute to do it-->
        <xs:element maxOccurs="unbounded" name="FamilyRecord" sql:relation="Family" sql:key-fields="FamilyId">

If you run the vbs script and you experience this error 

<?xml version="1.0"?>
<Result State="FAILED">
 <Error>
  <HResult>0x80004005</HResult>
  <Description>
   <![CDATA[No data was provided for column '{Primary Key Column}' on table '{Table}', and this column cannot contain NULL values.]]>
  </Description>
  <Source>General operational error</Source>
  <Type>FATAL</Type>
 </Error>
</Result>

Add this line to the vbs file

objBL.KeepIdentity=FALSE

After that I ran the vbs file, I am successful.

I can open up the tables:

Now that I have perfected the annotations to the mapping schema, I can run it through BizTalk without error.

To look at the final schema that is used by the bulkload adapter here:

Here is the sample data:

If you want to change the vbs script, here are the things you can change in the Bulk Load Object Model

Here are more definitions for the Bulk Load Annotations

Submit a Comment