Split messages

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

From: newbie (newbie_at_discussions.microsoft.com)
Date: 11/09/04 

Date: Tue, 9 Nov 2021 04:53:01 -0800

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

Relevant Pages

  • Re: Decouple SQL queries from class in OOP design
    ... If the data is owned by the application, embedding SQL might be ... - It couples the application to the database schema. ... - The "validate employee ID" functionality is likely to be used ...
    (comp.object)
  • Re: exporting into a SQL Server schema
    ... inside of the schema. ... The thing that's most confusing is that the dbo schema isn't ... take a look at the way the objects in the AdventureWorks database are ... It turns out that I'm moving some tables to a web hosted solution with sql ...
    (microsoft.public.access.externaldata)
  • RE: SQL Adapter
    ... Did you add the SQL schema with the 'Add Generated Items - Add Adapter' wizard. ... Target namespace and Request and Response root element name. ... > have to be inserted in an SQL Database. ...
    (microsoft.public.biztalk.general)
  • RE: where is a step by step example for setting up an envelope (splitt
    ... Create document schema: ... Select the SQL entry, make sure that we are pointing to the SQL ... port set to receive and for the root element enter EmpRoot. ... For the item select a receive pipeline and name it EmpSplitPipe.btp. ...
    (microsoft.public.biztalk.general)
  • RE: from XML to SqlCE
    ... you can load the XML into a data set and then into SQL CE. ... as there is no way for the dataset to create the database schema on the fly. ...
    (microsoft.public.sqlserver.ce)