BizTalk Development Series (18) using envelopes to split database messages

The BizTalk Development Series (17) Envelope architecture (Envelop), which was written earlier, is about the envelope architecture and its disassembly principles, all of which are theoretical content. Envelopes are most commonly used during the BizTalk development process when reading SQL Server databases. Read multiple messages at once, improving SQL adapter read performance to SQL Server. Try this example today to enhance your understanding of envelope messages.

The database is based on the SQL Server 2005 sample database: AdventureWorks selects all cluster information through SELECT * from Production.culture. As shown in the following:

Creating table schemas and envelope schemas

After you create the BizTalk project, build the table schema in the database according to the Build Schema Wizard. Enter the appropriate values in the target namespace and the document root node in the schema information, respectively.

Select the SQL script in the type, and in the Script editing window enter the following script (which indicates that the table column names are mapped to elements in the schema):

SELECT * from Production.culture FOR XML auto,elements

The Schema Generation Wizard generates a schema file with the following content. A orchestration that declares this type is also generated and can be removed by this orchestration.

The generated schema needs to be modified. All elements under the Production.culture node are promoted to the root node. The root node is modified to: Production.culture. Finally, the production.culture of the child node is deleted. The revised content is as follows:

Modified the table schema, you now need to create an additional envelope schema. There are several points to note in envelope architecture design: 1. The command space is consistent with the table schema. 2. The root node must be: Enveloproot. Of course, these two points do not necessarily have to obey. If you already know how the envelope works, you should understand why and how to modify it as needed.

The creation of envelope schemas is relatively straightforward. Create a schema in which the target namespace is consistent with the table schema and the Envelope property is set to Yes. Modify the root node name to: Enveloproot. Modify the data construction type of the root node in the properties of the root node to: Xs:anytype (note: AnyType is the base class for all types in the schema, similar to System.Object in C #). In the Set root node Bodyxpath dialog box, select Enveloproot.

Deploy projects, create receive ports and subscribe to send ports

　Two schemas are created so that you can deploy the project. You need to set the assembly key, the application name, before you deploy the project. After you deploy the project, you can create a new receive port: Receivesqlrecords reference to configure the SQL receive location. Note the value of document root elemnt name is consistent with the value of the root node of the envelope schema. The Document target Namesapce value also matches the target namespace of the envelope, table schema. Receive pipeline selection system built-in: xmlrecive

To create a new send port, define the filtering rule in the filter option for the send port: bts.receiveportname = = Receivesqlrecords.

Test

When you start a BizTalk project, the folder in the send location generates a split message. Because BizTalk does not delete records from the database after reading the data, the SQL script can fetch messages every time BizTalk SQL adapter polls. So that messages are constantly generated to the destination folder. You need to close the port or application.

Summary

Splitting database records through schemas is a classic case of envelope use. We will change the receive pipeline to: passthrureceive. You can get the original message generated from the database, such as. Of course, if you're interested, you can combine: The BizTalk Development Series (17) Envelope Architecture (Envelop) analyzes the disassembly process.

BizTalk Development Series (18) using envelopes to split database messages