Wednesday, September 24, 2014

BizTalk 2013: Inserting RawXML (Whole Incoming XML Message) in SQL database

In some scenarios, it is required to store the message which is received from sender along with transforming it to the destination format.  One such scenario is to store the RawXml (Whole Incoming XML Message) in database as it is.

To demonstrate this, used the Scenario : Order Message is received and we are to store the OrderId and the whole Order in database. For that I have created a TestDb, a table Order with two fields Id and RawXML.




Input Schema:


For the purpose of demo, I have kept only three fields in the input schema, and ID field is marked as distinguished as it will be used while constructing Outbound Message.



Destination Schema:


I have a table created in TestDb database in to which the Order Id of the incoming message and the Order as it is received  i.e. whole incoming Message is inserted.

To do so we need to create a destination schema for BizTalk but based on already defined table thus we would use Add Generated Items -> Consume Adapter Service option for it.


Binding : sqlBinding as the database we are dealing is SQL Server
IntialCatalog : TestDb as this is database which is to be connected and it holds the table we are dealing with
Server : “.” As database resides on same machine, if it was on other machine then it would have been that machine’s address.
Select contract type: Client (Outbound Operations) as message is going out of BizTalk
Operations: Insert as data will be inserted in the table


Click ok and you will see that three schemas and one binding file is added to the solution.


Out of the three schemas we will be using TableOperation.dbo.Order.xsd, so question might arise what about the rest two schemas? Well those are also used but implicitly by the schema TableOperation.dbo.Order.xsd.



Orchestrating the requirement:


Order Message(as per input schema) is received and passed on to Construct message, where the destination message is created and then send to InsertOrder_SQL port to do the actual insertion .



Constructing the Outbound Message:


The message construction is done inside the Message Assignment shape, with the help of two XmlDocument variable XMLDocIn and XMLDocOut.

Why XmlDocument variable? The class "XmlDocument" is a super class for all XML messages, and hence it can hold any type of XML message and subsequently any type of orchestration message.

And with XmlDocument , we can use a property called “OuterXml “ which points to the current node and its children.

So the first step is to assign the incoming message to XMLDocIn.
XMLDocIn = InMsg;


Second step is to create the outbound message. Apart from Map, Message Assignment, using .Net, the fourth way to construct message is with the help of LoadXML method – which provides a way to load the instance of schema of the message which is to be created (with or without actual values). For this demo we need to create the message which expects ID and the Whole Order as per the schema generated from SQL Table.

As we have made the ID field as distinguished it is available and we have the incoming message in XmlDocIn, with the help of OuterXml property we can get the whole Xml message.  Thus using the LoadXml message the values are loaded in the XmlDocOut variable and later assigned to OutMsg, typed message which is expected.

XMLDocOut.LoadXml("<ns0:Insert xmlns:ns0='http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Order'>"+
  "<ns0:Rows>"+
    "<ns1:Order xmlns:ns1='http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo'>"+
      "<ns1:Id>"+InMsg.ID+"</ns1:Id>"+
      "<ns1:RawXML>"+"<![CDATA[" + XMLDocIn.OuterXml + "]]>"+"</ns1:RawXML>"+
    "</ns1:Order>"+
   "</ns0:Rows>"+
"</ns0:Insert>");

OutMsg = XMLDocOut;


Question might arise why <![CDATA [ ]]> is used?  It is because while inserting into Database the parser will detect the opening tag (<) and the closing tag (>)  as a markup, so to avoid this <![CDATA[]> is used, which tells parser to treat the data enclosed within it as characters.



Sign the project, build and deploy.

Next is to configure and Test, check the part2 of this post : Configuring the Application and Testing


Download Sample - BizTalk 2013: Inserting RawXML (Whole Incoming XML Message) in SQL database Sample


Related Post:

1 comment:

  1. Maheshkumar,
    How can I do the same if records in the schema are repeating?
    Could you please contact me at remzikh@gmail.com

    Thank you
    Remzi

    ReplyDelete