RE: Split messages

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Matt Meleski (
Date: 11/09/04

Date: Tue, 9 Nov 2021 05:50:06 -0800

I have tried the below example before and it worked.
But: There is an example of this in the BizTalk SDK samples: Try this one


"newbie" wrote:

> I've followed the guide below how to pull data from a table in a database and
> split the data into one XML document per record returned. Unfortunately the
> messages returned are not splitted, i.e. all records returned are received in
> one single XML document. Any ideas what might be wrong or why the data isn't
> splitted?
> ---
> Followup, here's the steps I've created for a kb article on this.
> This is based off the northwind database.
> ==================================
> The following is a step by step guide on creating a receive port that
> will pull data from the employees table in the NorthWind database and split
> the inbound data into one document per record returned.
> Create document schema:
> 1: Open Visual Studio and create a new BizTalk project named
> NorthWindSplit
> 2: Right click on the project in Solution Explorer and select Add,
> then Add Generated Items. In the Generated Item wizard highilght Add Adapter
> and click Open.
> 3: Select the SQL entry, make sure that we are pointing to the SQL
> server that contains the BizTalk message box database and that the message
> box database is selected. Click next.
> 4: Click the Set button and enter the information to connect to the
> SQL server that houses the NorthWind database. Once you have entered the
> information click next.
> 5: For the target namespace enter http://NorthwindEmpSplit, leave the
> port set to receive and for the root element enter EmpRoot. Click next.
> 6: On this page select to use a select statement and click next. For
> the select statement enter:
> select * from employees for xml auto click next. Then click finish.
> You should have a SQLService.xsd file added to the project at this
> point.
> 7: Open the sqlservice.xsd file and select the employees element. In
> properties for this set the max occurs and min occurs to 1.
> 8: Move the employees record to the root level (direct child of <schema>)
> and delete the EmpRoot element. Save this schema.
> Create the Envelope schema:
> 1: Add a new schema to the project, name it EmpEnvelope.xsd.
> 2: Open the new schema and highlight the <schema> element. In the
> properties window set the Envelope property to yes. Set the target
> namespace to NorthwindEmpSplit. This must match the target namespace
> for the SQLService.xsd file.
> 3: Rename the root element to EmpRoot. In properties for EmpRoot
> select the Body XPath property and click the ... button. Select the EmpRoot
> element. This should set the Body XPath to /*[local-name()='EmpRoot'
> and namespace-uri()='http://NorthwindEmpSplit'
> 4: Right click the EmpRoot element and insert a schema node, Any
> element. This should create a child element named <Any>
> 5: Select the <Any> element and in properties set the Namespace to
> ##any. Set the Process Contents property to Lax.
> Save this schema.
> Adding a custom pipeline:
> 1: Right click on the BizTalk project in Solution Explorer, add a new
> item. For the item select a receive pipeline and name it EmpSplitPipe.btp.
> 2: Open the pipeline file, from the toolbox drag an xml disassembler
> to the disassembler stage in the pipeline.
> 3: Select the disassembler you added, in the properties dialog set the
> Document Schemas to the SQLService.xsd and set the Envelope Schemas to
> EmpEnvelope.xsd. Save this file.
> Building and deploying:
> 1: Create a keyfile to sign the assembly and set this in properties
> for the project.
> 2: Build the project, then deploy.
> 3: Copy the dll for this assembly to the \program files\microsoft
> biztalk server 2004\pipeline components folder
> Configuring BizTalk:
> 1: In Biztalk explorer create a new receive port, under this create a
> new receive location and set the transport type to SQL.
> 2: Select the URI entry and click the ... button. Here set the
> connection string to point to the SQL server that contains the Northwind
> database.
> 3: Select the SQL Command entry and click the ... button. In the
> Project field select the assembly we deployed for our project and in the
> Schema field select the SQLService schema.
> 4: Change the Document Root Element Name to EmpRoot. Ok out of the
> SQL Transport Properties.
> 5: Set the receive pipeline to use the pipeline we created in our
> assembly.
> 6: create a new send port. This should send to a file location. For
> filter settings filter on BTS.ReceivePortName==<name of receive port>
> 7: Start the send and receive locations. The receive location should
> start polling the SQL adapter, submit the data to the pipeline which should
> split it into individual documents that are written out through the file
> adapter.
> ==================================
> Larry Franks