How to use updategram to update multiple rows in BizTalk orchestration

Using the updategram to insert and delete are very straightforward. It's also easy to update one row of data using the updategram in BizTalk. BizTalk SQL adapter seems to be problematic when dealing with updating multiple rows of data using updategram. SQL XML states that you need to use the attribute "updg:id" to associate the records enclosed in the <updg:before> and those enclosed in <updg:after>(article here). I am pretty sure if you are reading this blog, you have tried the default schema generated by BizTalk and realized it didn't work. I have tried the following two approaches and they didn't work:

1. Use multiple <sync> nodes to describe the updategram. It didn't work.
   

   
   <?xml version="1.0" encoding="utf-8" ?> 
   <ns0:updateStatsPendingRequest xmlns:ns0="http://www.bankofamerica.com/">
    <ns0:sync>
     <ns0:before>
      <ns0:StatsPending APPLICATIONID="100010.032970" /> 
     </ns0:before>
     <ns0:after>
      <ns0:StatsPending APPLICATIONID="100010.032970" 
        SITEID="24" ASSIGNPROCESSED="true" /> 
     </ns0:after>
    </ns0:sync>
    <ns0:sync>
     <ns0:before>
      <ns0:StatsPending APPLICATIONID="100010.032971" /> 
     </ns0:before>
     <ns0:after>
      <ns0:StatsPending APPLICATIONID="100010.032971" 
        SITEID="25" ASSIGNPROCESSED="true" /> 
     </ns0:after>
    </ns0:sync>
   </ns0:updateStatsPendingRequest>
   

   
   Conclusion: SQL Adapter has difficulty handling multiple <sync> elements.
   


2. Use the <id> attributed as suggested by SQL XML. I added the extra id attribute into the schema like this:
   

   
   <xs:attribute name="id" type="xs:string" updategram:Prefix="updg" />
   

   
   This solution fails since the generated xml document instance doesn't have the correct xml schema prefix. As described by SQL XML, the prefix for the "id", "sync", "before" and "after" should really be "updg". However, the xml instance generated by BizTalk will look like:
   

   
   <?xml version="1.0" encoding="utf-8" ?> 
   <ns0:updateStatsPendingReq xmlns:ns0="http://www.bankofamerica.com/">
    <ns0:sync>
     <ns0:before>
      <ns0:StatsPending ns0:id="100010.040649" /> 
      <ns0:StatsPending ns0:id="100010.042313" /> 
     </ns0:before>
     <ns0:after>
      <ns0:StatsPending ns0:id="100010.040649" 
                        ns0:APPLICATIONID="100010.040649" 
                        ns0:SITEID="24" 
                        ns0:ASSIGNPROCESSED="1" /> 
      <ns0:StatsPending ns0:id="100010.042313" 
                        ns0:APPLICATIONID="100010.042313" 
                        ns0:SITEID="24" 
                        ns0:ASSIGNPROCESSED="1" /> 
     </ns0:after>
    </ns0:sync>
   </ns0:updateStatsPendingReq>
   

   
   

As you can see from the above xml instance, all of the elements and attributes are qualified with "ns0" prefix which is not what we wanted. After some research, I figured out that SQL XML need to use the "updg" prefix in order to correctly associate the records in <before> and <after> blocks. Therefore, we need to make the BizTalk to generate the document instance looks like this:

<?xml version="1.0" encoding="utf-8" ?> 
<ns0:updateStatsPendingReq 
  xmlns:updg="urn:schemas-microsoft-com:xml-updategram" 
  xmlns:ns0="http://www.bankofamerica.com/">
 <ns0:sync>
  <ns0:before>
   <ns0:StatsPending updg:id="100010.040649" /> 
   <ns0:StatsPending updg:id="100010.042313" /> 
  </ns0:before>
  <ns0:after>
   <ns0:StatsPending updg:id="100010.040649" 
                     ns0:APPLICATIONID="100010.040649" 
                     ns0:SITEID="24" 
                     ns0:ASSIGNPROCESSED="1" /> 
   <ns0:StatsPending updg:id="100010.042313" 
                     ns0:APPLICATIONID="100010.042313" 
                     ns0:SITEID="24" 
                     ns0:ASSIGNPROCESSED="1" /> 
  </ns0:after>
 </ns0:sync>
</ns0:updateStatsPendingReq>

After I tweak the schemas to generate the xml instance like above, it worked!!
If you run the SQL profiler, you will see the following TSQL statements are generated:

SET XACT_ABORT ON
BEGIN TRAN
DECLARE @eip INT, @r__ int, @e__ int
SET @eip = 0

UPDATE StatsPending SET SITEID=N'24', ASSIGNPROCESSED=N'1' 
WHERE (APPLICATIONID=N'100010.042313');  
SELECT @e__ = @@ERROR, @r__ = @@ROWCOUNT
 IF (@e__ != 0 OR @r__ != 1) SET @eip = 1
 IF (@r__ > 1) RAISERROR ( N'Ambiguous update, unique identifier required', 16, 1)
 ELSE IF (@r__ < 1) RAISERROR ( N'Empty update, no updatable rows found', 16, 1)

UPDATE StatsPending SET SITEID=N'24', ASSIGNPROCESSED=N'1' 
WHERE (APPLICATIONID=N'100010.040649');  
SELECT @e__ = @@ERROR, @r__ = @@ROWCOUNT
 IF (@e__ != 0 OR @r__ != 1) SET @eip = 1
 IF (@r__ > 1) RAISERROR ( N'Ambiguous update, unique identifier required', 16, 1)
 ELSE IF (@r__ < 1) RAISERROR ( N'Empty update, no updatable rows found', 16, 1)

IF (@eip != 0) ROLLBACK ELSE COMMIT
SET XACT_ABORT OFF

The following are list of things you need to do in order to ask BizTalk to generate the "updg" prefix.

1. Define Updategram.xsd like this:
   
   


2. In your updategram schema's <xs:schema> element, add this line:
   

   
   xmlns:updg="urn:schemas-microsoft-com:xml-updategram"
   

   
   


3. Add an <xs:import> element in your updategram schema:
   

   
   <xs:import
     schemaLocation="Updategram.xsd"
     namespace="urn:schemas-microsoft-com:xml-updategram" />
   

   
   


4. Add <updg:id> attribute in <before> and <after> elements by using the following line:
   

   
   <xs:attribute ref="updg:id" />
   

   
   Your schema will look like this:
   
   

   
   <?xml version="1.0"?>
   <xs:schema
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns="http://www.bankofamerica.com/commercialprocesstechnology/sbui"
    xmlns:updg="urn:schemas-microsoft-com:xml-updategram"
    targetNamespace="http://www.bankofamerica.com/commercialprocesstechnology/sbui"
    attributeFormDefault="unqualified"
    elementFormDefault="qualified"
   >
    <xs:import
     schemaLocation="Updategram.xsd"
     namespace="urn:schemas-microsoft-com:xml-updategram" />
    <xs:element name="updateStatsPendingReq">
     <xs:complexType>
      <xs:sequence>
        <xs:element
         xmlns:updategram="urn:schemas-microsoft-com:xml-updategram"
         updategram:Prefix="updg" minOccurs="1" maxOccurs="unbounded" name="sync">
         <xs:complexType>
          <xs:sequence>
           <xs:element updategram:Prefix="updg" minOccurs="0" maxOccurs="unbounded" name="before">
            <xs:complexType>
             <xs:sequence>
              <xs:element minOccurs="0" maxOccurs="unbounded" name="StatsPending">
               <xs:complexType>
                <xs:attribute ref="updg:id" />
                <xs:attribute name="APPLICATIONID" type="xs:decimal" />
               </xs:complexType>
              </xs:element>
             </xs:sequence>
            </xs:complexType>
           </xs:element>
           <xs:element updategram:Prefix="updg" minOccurs="0" maxOccurs="unbounded" name="after">
            <xs:complexType>
             <xs:sequence>
              <xs:element minOccurs="0" maxOccurs="unbounded" name="StatsPending">
               <xs:complexType>
                <xs:attribute ref="updg:id" />
                <xs:attribute name="APPLICATIONID" type="xs:decimal" />
                <xs:attribute name="SITEID" type="xs:string" />
                <xs:attribute name="ASSIGNPROCESSED" type="xs:boolean" />
               </xs:complexType>
              </xs:element>
             </xs:sequence>
            </xs:complexType>
           </xs:element>
          </xs:sequence>
         </xs:complexType>
        </xs:element>
       </xs:sequence>
     </xs:complexType>
    </xs:element>
     <xs:element name="updateStatsPendingRep">
       <xs:complexType>
         <xs:sequence>
           <xs:element name="Success" />
         </xs:sequence>
       </xs:complexType>
     </xs:element>
   </xs:schema>
   

   
   


5. In the message transformation shape when you construct the updategram, use a map similar to this:
   
   

Then just fire off the orchestration and watch the updategram does its work. The particular orchestration I ran was able to update approximately 1800 records in a few seconds without any problem. Email me if you need a sample project.