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 07ansforming it to the destination format.  One such scenario is to store the RawXml (Whole Incoming XML Message) in database as it is.

 To demons07ate 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 cons07ucting 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 con07act 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.



Orches07ating the requirement:


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


Cons07ucting the Outbound Message:


 The message cons07uction 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 orches07ation 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 cons07uct 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 07eat 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 

  • 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 Orches07ation using DefaultPipeline - BizTalk 2010


    • ServerLess360



    Comments

    Post a Comment

    Popular posts from this blog

    The request has both SAS authentication scheme and 'Bearer' authorization scheme. Only one scheme should be used

    Image
    While doing testing after doing a POC on Securing Logic App with Azure Active Directory authentication , where I have put logic app behind APIM and before passing the request to logic app, apim does validation of the token.  I was encountered with an error "The request has both SAS authentication scheme and 'Bearer' authorization  scheme.  Only one scheme should be used." Why it happened After validating the token which is part of the header i.e. Authorization, APIM forwards the request as it is to backend. As  Logic app is configured as  back end , it's url already consist of SAS signature plus the request also has Authorization section and this is the problem. By default every request endpoint on a logic app has a Shared Access Signature (SAS) in the endpoint's URL, which follows this format: https://<request-endpoint-URI>sp=<permissions>sv...
    Read more

    Getting Started with Logic Apps - AS2

    Image
    What is AS2? Every enterprise requires some kind of product, service or counselling from another enterprises thus there happens a B2B(business to business) communications, some of the communication is unique whereas most of the communications are common for most of the enterprises e.g., Purchase Order. But every enterprise has there own way(format) of sending the messages, leading to difficult management when number of partners increased.  Considering this the Enterprise leaders decided to have some standards/rules defined across the communication leading to B2B protocol standards which provide guidelines for trading partners to follow when conducting business between enterprises. EDI X12, EDIFACT, TRADACOM etc. AS2 stands for Applicability Statement 2 and is a B2B messaging protocol used to transmit Electronic Data Interchange (EDI)/Business documents from one organization to another. So EDI standards define how to format data and AS2 specifies how to securely transport...
    Read more

    How to Debug and Trace request in Azure APIM - Portal, Postman, RequestBin

    Image
    Introduction  APIM is a great option to expose API's with out of box features for applying restrictions, preprocessing, postprocessing etc. You can leverage existing API's,  by importing it and it gets added as a new API with all the operations associated with it. And based on the requirements you  apply policies at the stage/level (Inbound,Backend/Outbound) and you are ready to use the API. Before it is shared, we do some testing to make sure everything is working as per the expectation and this is where Debugging and Tracing request becomes important. APIM does provide a way to Trace a call with the help of  Ocp-Apim-Trace http header. So whichever request/call is to be traced, it needs to include this in header  with the value set to true and it has dependency on another header, so that also needs to be passed i.e.  Ocp-Apim-Subscription-Key Note: The api on which Tracing is to applied , it requires the subscription key to be en...
    Read more