SQL Adapter and Debatching. Beware when you process a message that contains consecutive empty elements, debatching will fail.

For how to configure SQL Adapter and Debatching see the article writtin by Richard Seroter This problem occurs when you process a message that uses a document structure that resembles the following. <DDLService xmlns=”http://www.digitaldeposit.net/schemas/SQL”> <CustomerActivationMessages AccountNumber=”12345” DDLServiceType=”ORDERRESPONSE” DDLServiceName=”ORDERRESPONSE” CompanyName=”XYZ” CountryCode=”GB” /> <CustomerActivationMessages AccountNumber=”98765” DDLServiceType=”ORDERRESPONSE” DDLServiceName=”ORDERRESPONSE” CompanyName=”ABC” CountryCode=”GB” /> </DDLService > Envelope Schema: DDLService Document Schema: CustomerActivationMessages In our solution, SQL Adapter submits the above message via XmlReceive (used for debatching) pipeline into Biztalk. As soon as the SQL adapter polling starts we started to see the following exception message: There was a failure executing the receive pipeline: “Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35” Source: “Pipeline ” Receive Port: “Receive.Internal.SQL.1Way.AllPollingService.ActivationMessages” URI: “SQL://SERVER/SchOrderPolling/ORDERRESPONSE/ORDERRESPONSE/902971” Reason: Unexpected event (“document_start”) in state “processing_empty_document“. Little bit of search revealed its a known issue with Biztalk 2006 (See the KB Article http://support.microsoft.com/kb/927741) and it applies to any message which contains consecutive empty elements as shown in the beginning of the article. If you notice the message carefully, you can see there is only root element with few attributes and no content element(s). You got few options to resolve this problem, if you are flexible with the schemas you can add ELEMENTS along with FOR XML AUTO in your SQL Query as shown below FOR XML AUTO, ELEMENTS which will result in following xml <DDLService xmlns=”http://www.digitaldeposit.net/schemas/SQL”> <CustomerActivationMessages> <AccountNumber>12345</AccountNumber> <DDLServiceType>ORDERRESPONSE</DDLServiceType> <DDLServiceName>ORDERRESPONSE</DDLServiceName> <CompanyName>XYZ</CompanyName> <CountryCode>GB</CountryCode> </CustomerActivationMessages> <CustomerActivationMessages> <AccountNumber>98765</AccountNumber> <DDLServiceType>ORDERRESPONSE</DDLServiceType> <DDLServiceName>ORDERRESPONSE</DDLServiceName> <CompanyName>ABC</CompanyName> <CountryCode>GB</CountryCode> </CustomerActivationMessages> </DDLService > or, there is a hot fix available from Microsoft at http://support.microsoft.com/kb/927741. Nandri! Saravana