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

{tocify} $title={Table of Contents}

Introduction

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

If you have questions or suggestions, feel free to do in comments section below !!!

Do share if you find this helpful .......

 

                          Knowledge Sharing is Caring !!!!!!

Learn more about BizTalk 

• BizTalk Server: Multiple XML files to Single FlatFile Using File Adapter
• BizTalk 2013: Inserting RawXML (Whole Incoming XML Message) in SQL database
• BizTalk 2013: Inserting RawXML (Whole Incoming XML Message) in SQL database - Part 2
• Is it possible to have Map Chaining on the Port Level
• Promoting custom context property using Pipeline Component
• Custom ZipReceivePipeline to Unzip Multi-Type Messages
• Grouping and debatching Inbound Messages from WCF SQL Adapter - BizTalk 2010
• Polling data from SQL using WCF-SQL Adapter in BizTalk 2010 - TypedPolling(From Multiple table)
• Grouping XML Messages using custom XSLT- BizTalk 2010
• Insert Records in SQL Server using WCF-SQL Adapter in BizTalk 2010 - Composite operation(Multiple Records insertion)
• Insert Records in SQL Server using WCF-SQL Adapter in BizTalk 2010- Composite Operation (Message Transformation Pattern)
• Debatching(Splitting) XML Message - BizTalk 2010
• Debatching(Splitting) XML Message in Orchestration using DefaultPipeline - BizTalk 2010