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:

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

Configuring the Application and Testing


After Developing and  deploying the application start with creating ReceivePort with a File based receive location.



Send port which will send data to SQL, in this case you need to use WCF-SQL adapter or WCF-Custom adapter can be used.
1. PassThru pipeline is used as no processing is to be done .



2. SQL binding is to be selected as WCF-Custom adapter is used, if WCF-SQL was used then it would have already been there.



After both Receive port and Send Port are created, bind it to the Orchestration. Right click the orchestration and configure.




Testing

After done with configuration, time to test the application. Start the application and drop the sample Order File at Receive Location and you should be able to see in SQL as shown below.



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


Related Post:

Monday, September 1, 2014

Begineer to BizTalk Expert : Interview with Rohit Sharma


Welcome to third Interview of the series, today's BizTalk Expert is Rohit Sharma.

Rohit Sharma is technical lead developer and application architect and has good experience in architecting, designing, developing, and supporting sophisticated applications using many different Microsoft technologies and products. Rohit Sharma is very active in BizTalk community as blogger, Wiki author/editor and forums. He has been awarded the Microsoft Most Valuable Professional (MVP) award for two consecutive years (2012 and 2013) based on his contributions to the world-wide BizTalk Server community.

Mahesh: Who are you and what you do? 
Rohit: I am Rohit Sharma born and brought up in Punjab and now settled in area near to Chandigarh. I am technology enthusiast and work with Microsoft Integration technologies. 

Mahesh: When did you start working on BizTalk? 
Rohit: The organisation where I started my career was having Microsoft as one of its clients. My journey with BizTalk Server started in 2007 when I got the chance to work with Connected System Division of Microsoft (the BizTalk Server product team). My team was responsible for handling the BizTalk Server Accelerators (HL7, RosettaNet and SWIFT). 

Mahesh: How did you mastered BizTalk (Learning path)? 
Rohit: I got chance to work during release of BizTalk Server 2006 R2 and BizTalk Server 2009 and during these releases I got the chance to learn about the different aspects of BizTalk Server.

Mahesh: Which are the major projects you handled so far? 
Rohit: There has been few being part of CSD gave me lot of exposure to Microsoft Technologies another one I would like to highlight was for a leading airline company where my team was responsible to set-up two new data centres. As a part of this engagement we got chance to did almost everything from configuration, disaster recovery, development, optimization and experimentation.

Mahesh: How do you see BizTalk compare to other integration platform? 
Rohit: Based on my experience with other platform I would say solution management is bit easier in BizTalk but it lack a web based interface but this gap has been filled by some third party products. 

Mahesh: What as per you is must to know to become an Integration (BizTalk) Expert? 
Rohit: There is no substitute to experience in integration space. By doing more and more integrations you come to know about the challenges of integrating with specific products. 

Mahesh: What are your thoughts on forums, blogs and articles etc.? 
Rohit: These are good platform to look for help and to learn from experience of others and to share your own experience. 

Mahesh: Your suggestion to a newcomers? What should be approach to get sound knowledge in Biztalk? 
Rohit: If you get chance to be part of a team where lot of good BizTalk experts are available then grab it, learning through them would be much easier. 

Mahesh: What are your thoughts around BizTalk certification? 
Rohit: As I have already mentioned there is no substitute to experience so for me certification is not that important. 

Mahesh: What is the future of BizTalk? 
Rohit: BizTalk Server is very mature product and I feel that on-premises version of BizTalk will be there as many organizations are still using legacy systems and are not comfortable in moving the key strategic systems to the cloud. 

Mahesh: Any thoughts on cloud? 
Rohit: Cloud is a game changer and is the future. It is the solution to lot of challenges being faced by organizations these days. Looking forward to see cloud based integration offering to be more mature. The on-premises offering are already providing the way to integrate with cloud. 


Thanks a lot Rohit, am sure many of us will be benefited from your insights !!!!!!


Feel free to ask questions to Rohit, in the comments.