Debatching(Splitting) XML Message in Orchestration using DefaultPipeline - BizTalk 2010

In this post we will walk through the process of debatching an xml message in Orchestration using pipeline in Biztalk.

I have used the Default XML Receive pipeline to achieve it, but it can also be done by creating a custom pipeline which uses XML disassembler (where you can set the Envelope and Document Schema).

Pipeline is not available in Orchestration like other shapes, thus to use it we need to add reference to following assemblies(which will allow us to use methods in those assemblies) :

• Microsoft.XLANGs.Pipeline.dll
• Microsoft.BizTalk.Pipeline.dll

You can browse to the location BizTalk Installation Directory to find above dll's.

Most of the part of this post is borrowed from my earlier post which talks about debatching xml at receive port:
http://tech-findings.blogspot.in/2013/07/debatchingsplitting-xml-message-biztalk.html

Scenario:

We receive many item information but its wrapped (Enveloped) , so to process each item we need to unwrap it (remove the envelope and split individual Item message).

Below is what we receive (Input) :
<ns0:Items xmlns:ns0="http://TestingSchemas.ItemEnvelope">
  <ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_0</ID> 
  <Name>Name_0</Name> 
  <Quantity>100</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
  <ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_0</ID> 
  <Name>Name_1</Name> 
  <Quantity>200</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
  <ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_2</ID> 
  <Name>Name_2</Name> 
  <Quantity>300</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
<ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_3</ID> 
  <Name>Name_2</Name> 
  <Quantity>300</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
<ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_4</ID> 
  <Name>Name_2</Name> 
  <Quantity>300</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
<ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_5</ID> 
  <Name>Name_2</Name> 
  <Quantity>300</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
<ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_6</ID> 
  <Name>Name_2</Name> 
  <Quantity>300</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
<ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_7</ID> 
  <Name>Name_2</Name> 
  <Quantity>300</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
<ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_8</ID> 
  <Name>Name_2</Name> 
  <Quantity>300</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
<ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_9</ID> 
  <Name>Name_2</Name> 
  <Quantity>300</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
  </ns0:Items>

But we want (Output) :

      <ns0:Product xmlns:ns0="http://TestingSchemas.Item">

           <ID>ID_0</ID>

          <Name>Name_0</Name>

          <Quantity>100</Quantity>

          <UnitPrice>100</UnitPrice>

      </ns0:Product>

             .

             .

             .

             .

             .

     <ns0:Product xmlns:ns0="http://TestingSchemas.Item">

        <ID>ID_9</ID>

        <Name>Name_9</Name>

        <Quantity>100</Quantity>

        <UnitPrice>100</UnitPrice>

     </ns0:Product>

All right, let's see how we do it:

1. Create schema - document schema 

2. Create the wrapper - Envelope Schema

• Name the root node, I have named it Items.

• Click on "Schema" and go to Properties Window
• Set the property "Envelope" as Yes, as this schema will be used as envelope and this is the property which helps disassembler to recognize it.
• So far good, envelope schema is ready but what about the document which will be wrapped.

• In the Property window select "Imports" and click on the ellipsis 
• You will get Imports wizard pop-ed  out  .
• Click on add and select the document schema which we created in step 1.(Here it is Item schema)

• Click on the "Items" and go to property window and select the property Body XPath and set it to /*[local-name()='Items' and namespace-uri()='http://TestingSchemas.ItemEnvelope']. Doing so allows an Items node to contain any number of Item Document.
• Cool... Now we are ready with the resources, next is to validate schema, build it, sign it  and deploy.

3. Now lets create Orchestration which will receive the enveloped item and debatch/split it using the pipeline.

•         Receive shape is configured to receive untyped message i.e. of type System.Xml.XmlDocument (which will receive enveloped message -ItemEnvelope Type) .
•         Then we have a "DebatchScope"  which is of type "Atomic" and Orchestration  is of type Long running. Why Atomic scope? Because we will be calling/executing Pipeline within it and the pipeline is of non- serializable type.
•         Next we have Expression shape named as "Execute Pipeline". It is here where we make call to pipeline  and execute it which results in splitted messages.
•         It contains following line:  GetPipelineOutput=Microsoft.XLANGs.Pipeline.XLANGPipelineManager.ExecuteReceivePipeline(typeof(Microsoft.BizTalk.DefaultPipelines.XMLReceive),ItemsIn);
•         GetPipelineOutput is a DebatchScope variable of type: Microsoft.XLANGS.Pipeline.ReceivePipelineOutputMessages
•       So we are executing the method ExecuteReceivePipeline()  method of XLANGPipelineManager class  which belongs to Microsoft.XLANGs.Pipeline and its output assigned to  GetPipelineOutput.

•      Then we have a loop shape "UntilLastMessage" and its same as  while loop, below is the condition(till spillited messages are available) : GetPipelineOutput.MoveNext()
•         Next is Construct shape with Message Assignment within it, which has following code:

 ItemOut = null;

          GetPipelineOutput.GetCurrent(ItemOut);

It is here where the splitted single  message is assigned to ItemOut , where ItemOut is a Message variable which is of type Item.xsd

•        At last we have Send shape which accepts message of type ItemOut and sends it .
•        Now what, build the project , sign it and deploy .

4.Now create a Receive and Send port :

•        Receive Pipeline is PassThruReceive  as we don't want its message type to be detected until the message reaches to ExecutePipeline shape in Orchestration.

•        Now after both the ports are ready, it's time to bind it to the logical ports of our orchestration:

5. Start the application and test it, I will drop a envelope message at receive location which we saw at start  . So I should be getting 10 individual xml message at the destination location:

        Will keep on posting as an when I find something to share!!!!!!!!!!!!

Debatching(Splitting) XML Message - BizTalk 2010

In this post we will walk through the process of debatching xml message using Biztalk.

Scenario:

We receive many item information but its wrapped(Enveloped) , so to process each item we need to unwrap it ( remove envelope and split individual Item message.

Below is what we receive(Input) :
<ns0:Items xmlns:ns0="http://TestingSchemas.ItemEnvelope">
  <ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_0</ID> 
  <Name>Name_0</Name> 
  <Quantity>100</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
  <ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_0</ID> 
  <Name>Name_1</Name> 
  <Quantity>200</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
  <ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_2</ID> 
  <Name>Name_2</Name> 
  <Quantity>300</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
<ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_3</ID> 
  <Name>Name_2</Name> 
  <Quantity>300</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
<ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_4</ID> 
  <Name>Name_2</Name> 
  <Quantity>300</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
<ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_5</ID> 
  <Name>Name_2</Name> 
  <Quantity>300</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
<ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_6</ID> 
  <Name>Name_2</Name> 
  <Quantity>300</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
<ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_7</ID> 
  <Name>Name_2</Name> 
  <Quantity>300</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
<ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_8</ID> 
  <Name>Name_2</Name> 
  <Quantity>300</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
<ns1:Product xmlns:ns1="http://TestingSchemas.Item">
  <ID>ID_9</ID> 
  <Name>Name_2</Name> 
  <Quantity>300</Quantity> 
  <UnitPrice>100</UnitPrice> 
  </ns1:Product>
  </ns0:Items>

But we want(Output) :

      <ns0:Product xmlns:ns0="http://TestingSchemas.Item">

           <ID>ID_0</ID>

          <Name>Name_0</Name>

          <Quantity>100</Quantity>

          <UnitPrice>100</UnitPrice>

      </ns0:Product>

             .

             .

             .

             .

             .

     <ns0:Product xmlns:ns0="http://TestingSchemas.Item">

        <ID>ID_9</ID>

        <Name>Name_9</Name>

        <Quantity>100</Quantity>

        <UnitPrice>100</UnitPrice>

     </ns0:Product>

Allright , lets see how we do it:

1. Create schema - document schema 

2. Create the wrapper - Envelope Schema

• Name the root node, I have named it Items.

• Click on "Schema" and go to Properties Window
• Set the property "Envelope" as Yes , as this schema will be used as envelope and this is the property which helps disassembler to recognize it.
• So far good , envelope schema is ready but what about the document which will be wrapped.

• In the Property window select "Imports" and click on the ellipsis 
• You will get Imports wizard pop-ed  out  .
• Click on add and select the document schema which we created in step 1.(Here it is Item schema)

• Click on the "Items" and go to property window and select the property Body XPath and set it to /*[local-name()='Items' and namespace-uri()='http://TestingSchemas.ItemEnvelope']. Doing so allows Items node to contain any number of Item Document.
• Cool... now we are ready with the resources, next is to validate schema ,build it, sign it  and deploy.

3. Now create a Receive port which will receive enveloped message(ItemEnvelope Type) .

•            Select XMLReceive pipeline

4.Now create a Send port which will send individual xml message (Item Type)

•          Select XMLTransmit pipeline
•          Add Filter (this creates subcription ) : BTS.ReceivePortName == ReceiveItem
•         After splitting the message , the pipeline would submit individual Item Messages and as we are creating subscription , all the messages submitted by ReceiveItem port will be picked by SendPort

5. Now lets test, I will drop a envelope message at receive location which we saw at start  . So I should be getting 10 individual xml message at the destination location:

6.Did we really got the messages split-ted:

Will keep on posting as an when I find something to share!!!!!!!!!!!!

Insert Records in SQL Server using WCF-SQL Adapter in BizTalk 2010- Composite Operation (Message Transformation Pattern)

In this post we will see how to Insert records in SQL tables without using Orchestration . The transformation of the message is done at Receive port.

The idea to implement this struck me , when I was composing my other post(so most of the part will be common ) : http://tech-findings.com/2013/07/insert-records-in-sql-server-using-wcf.html


Scenario is I receive Purchase Order and need to insert in SQL tables viz. 1. Header table(Parent) 2. Details table(Child)

First thing is to create Tables and Stored Procedures which we will call to insert values in the tables.

Header Table Create Script:

CREATE TABLE [dbo].[HDR4201](
[RequestDate] [date] NOT NULL,
[OrderID] [nchar](50) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[BillToID] [nchar](50) NOT NULL,
[ShipToID] [nchar](50) NOT NULL,
[ACKFlag] [char](1) NULL,
[SNFlag] [char](1) NULL,
[BillToName] [nchar](50) NOT NULL,
[BillToCity] [nchar](50) NOT NULL,
[BillToState] [nchar](50) NOT NULL,
[BillToPostal] [nchar](50) NOT NULL,
[BillToCountry] [nchar](50) NOT NULL,
[ShipToName] [nchar](50) NOT NULL,
[ShipToCity] [nchar](10) NOT NULL,
[ShipToState] [nchar](50) NOT NULL,
[ShipToPostal] [nchar](50) NOT NULL,
[ShipToCountry] [nchar](50) NOT NULL,
[Comments] [nchar](999) NULL,
[TotalAmount] [decimal](18, 2) NOT NULL,
[TotalTax] [decimal](18, 2) NOT NULL,
[Currency] [nchar](10) NOT NULL,
[DOCO] [bigint] IDENTITY(1000,1) NOT NULL,
[INFlag] [char](1) NULL,
 CONSTRAINT [PK_HDR4201] PRIMARY KEY CLUSTERED 
(
[OrderID] ASC,
[OrderDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Detail Table Create Script:

CREATE TABLE [dbo].[DTL4211](
[Doco] [bigint] NOT NULL,
[LineNumber] [float] NOT NULL,
[OrderID] [nchar](50) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[RequestDate] [date] NOT NULL,
[Quantity] [int] NOT NULL,
[UnitPrice] [decimal](18, 2) NOT NULL,
[Tax] [decimal](18, 2) NOT NULL,
[TotalLineAmount] [decimal](18, 2) NOT NULL,
[UnitOfMeasure] [nchar](4) NOT NULL,
[ACKFlag] [nchar](1) NULL,
[SNFlag] [nchar](1) NULL,
[CurrentDate] [datetime] NOT NULL,
[ACKSentDate] [datetime] NOT NULL,
[ShipmentDate] [datetime] NOT NULL,
[ItemDescription] [nchar](50) NULL,
[LineComment] [nchar](50) NULL,
[ItemID] [nchar](20) NOT NULL,
 CONSTRAINT [PK_DTL4211] PRIMARY KEY CLUSTERED 
(
[Doco] ASC,
[LineNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Stored Procedure to insert in Header Table:
CREATE PROCEDURE  [dbo].[uspInsertOrderHeader]
@RequestDate date,
@OrderID nchar(50),
@OrderDate nchar(50),
@BillToID nchar(50),
@ShipToID nchar(50),
@BillToName nchar(50),
@BillToCity nchar(50),
@BillToState nchar(50),
@BillToPostal nchar(50),
@BillToCountry nchar(50),
@ShipToName nchar(50),
@ShipToCity nchar(50),
@ShipToState nchar(50),
@ShipToPostal nchar(50),
@ShipToCountry nchar(50),
@Comments nchar(999),
@TotalAmount decimal(18,2),
@TotalTax decimal(18,2),
@Currency nchar(10)
AS
BEGIN

SET NOCOUNT ON;

INSERT INTO HDR4201(RequestDate,OrderID,OrderDate,BillToID,ShipToID,BillToName,BillToCity,BillToState,BillToPostal,BillToCountry,ShipToName,ShipToCity,ShipToState,ShipToPostal,ShipToCountry,Comments,TotalAmount,TotalTax,Currency)
Values(@RequestDate,@OrderID,@OrderDate,@BillToID,@ShipToID,@BillToName,@BillToCity,@BillToState,@BillToPostal,@BillToCountry,@ShipToName,@ShipToCity,@ShipToState,@ShipToPostal,@ShipToCountry,@Comments,@TotalAmount,@TotalTax,@Currency);

 END

Stored Procedure to insert in Detail Table:
CREATE PROCEDURE [dbo].[uspInsertOrderDetail] 
@LineNumber float,
@OrderID nchar(50),
@OrderDate nchar(50),
@RequestDate date,
@Quantity int,
@UnitPrice decimal(18,2),
@Tax decimal(18,2),
@TotalLineAmount decimal(18,2),
@UnitOfMeasure nchar(4),
@CurrentDate datetime,
@ACKSentDate datetime,
@ShipmentDate datetime,
@ItemDescription nchar(50),
@LineComment nchar(50),
@ItemID nchar(20)
AS
BEGIN

SET NOCOUNT ON;
DECLARE @doco bigint;
set @doco = (SELECT DOCO FROM HDR4201 WHERE OrderID = @OrderID AND OrderDate =@OrderDate);

INSERT INTO DTL4211(Doco,LineNumber,OrderID,OrderDate,RequestDate,Quantity,UnitPrice,Tax,TotalLineAmount,UnitOfMeasure,CurrentDate,ACKSentDate,ShipmentDate,ItemDescription,LineComment,ItemID)
Values(@doco,@LineNumber,@OrderID,@OrderDate,@RequestDate,@Quantity,@UnitPrice,@Tax,@TotalLineAmount,@UnitOfMeasure,@CurrentDate,@ACKSentDate,@ShipmentDate,@ItemDescription,@LineComment,@ItemID);

  END

Cool ... Now to make it happen actually ... we need to consume the service which is leveraged by WCF-SQL Adapter

1.Create BizTalk Project (Name it what you desire)
2.Create schema for the incoming Purchase Order

3.In schema shown above , ShipTo and BillTo have same structure
4.Now we need the destination schema and let's add it:

•  Right click on  the project and select Add->Add Generated Items
• A wizard will pop out and will give you options to select from it.
• Select Consume Adapter Service ( you can also opt for Add Adapter)
• Difference is very small and should be opted as per requirement:
• Consume Adapter Service : Creates schema(XSD) as per adapter metadata
• Add Adapter Metadata: Creates orchestration and schema as per WSDL
• And both will make you go through same Wizard (Shown Below)
• In Security tab select Windows from drop down list 
• In URI Properties tab in front of Server
• 1.Enter "dot(.)" (If your SQL server is on  same machine) 2. Else the name of the server machine
• I front of InitialCatalog mention your database name

• After you say OK on the configure adapter page , click connect . And as shown above all the details are filled .
• It tells you the type of binding ( sqlBinding) , URI(which you configured), contract type (Client(Outbound operations )) and all the categories and operations available on URI that we configured.
• As it can be seen above , I have added the operations which I need for inserting into tables from Procedures category. 
• I have given a prefix (Its not mandatory) which will be prefixed to the schema's we are about to create.
• After I click OK , two xsd get added to my project(shown below)

    5. Now we have  the destination  schemas as shown below :

6. As you can see we get four root elements here.Now I want to insert OrderHeader and OrderDetails in both the table at once.To do so, I would need a dedicated schema record for Request and for Response. 

So lets add it in the two records namely OrderRequest and OrderRequestResponse.In OrderRequest record add uspOrderInsertHeader and uspOrderInsertDetail.Make sure that you change the max occurrence property of uspOrderInsertDerail to unbounded.

7.The reason for doing this is as we will have multiple line item in a single order.Also we are inserting both header and detail records together .

8.Under OrderRequestResponse add uspOrderInsertHeaderResponse and uspOrderInsertDerailResponse

also make max occurrence property of uspOrderInsertDerailResponse to unbounded.

Note: You can name any thing you want but remember the second record name should be suffixed with Response. Because SQL sends response with  request name suffixed with "Response" 

 example: Suppose you submit request with name "A" then  SQL will send response as "AResponse"

9.Remember to set MaxOccur as unbounded for "uspInsertOrderDetail" , "uspInsertOrderDetailResponse" as this will be multiple times.

10.Now we are ready with the resources required for mapping, lets do it:

11.Do test your map , it is best practice and does help in reducing problems which might arise later.

12. Now Deploy the project.

13. The flow is :

•  Purchase Order is received at receive port.
• A map which we saw above will be applied where we transform incoming message to traget(SQL) form.
• The message is published in the MessageBox, thereafter look up for subscription is done and copy of this message is sent to subscriber ( here it will be Send Port which we will create in some time)

14.Now its time to configure the application

• Create one way Receive port with a file location where you want the input message to be picked
• And add the map which will transform IncomingOrder to SQL expected form.

• Create one Send port of type Static One Way Type 
• Select WCF-SQL Adapter as a Transport type
• Select XMLTransmit 

Click configure

Perform same operation as we did at step 4

Do not forget to Add Action and here it should be "CompositeOperation".

Click ok 

• Add filter on the send port:  BTS.ReceivePortName==ReceiveOrderFile
• Adding filter means creating subscription, so here our send port is subscribing  to the messages which are published by our receive port "ReceiveOrderFile" in MessageBox.

16. Start the application and test it .


Will keep on posting as an when I find something to share!!!!!!!!!!!!

Related Post:

• BizTalk 2013: Inserting RawXML (Whole Incoming XML Message) in SQL database
• Insert Records in SQL Server using WCF-SQL Adapter in BizTalk 2010 - Composite operation(Multiple Records insertion)