locked
Storing the incoming message into SQL Server Table with a column of type XML/nvarchar and later retreiving it RRS feed

  • Question

  • Hi, I have a requirement where I need to store the entire incoming message to a SQL Server table with a column of type XML/nVarchar, and also I need to later retrieve it to reprocess again, How do I proceed about this.
    Wednesday, September 17, 2014 10:17 AM

Answers

All replies

  • Hi Praveen,

    You can use WCF-SQL adapter for inserting and retrieving the message from DB. Use below link for the reference

    http://social.technet.microsoft.com/wiki/contents/articles/18906.biztalk-server-wcf-sql-adapter-table-operations.aspx

    Simply use insert operation to insert the message and for retrival use Select operation. Please revert back if you need more detailed explanation.

    ______________________________________________________________

    Please indicate "Mark as Answer" or "Mark as Helpful" if this post has answered the question

    Regards,

    Rahul Madaan

    biztalkvillage.blopspot.com


    • Edited by Rahul_Madaan Wednesday, September 17, 2014 10:56 AM
    • Proposed as answer by Rahul_Madaan Wednesday, September 17, 2014 11:44 AM
    • Marked as answer by Angie Xu Monday, September 22, 2014 1:34 AM
    Wednesday, September 17, 2014 10:40 AM


  • Hi Praveen,

    You can make use of WCF-SQL adapter.

    For inserting and retrieval of the data, you can write stored procedures.

    You can read Steef-Jan's (http://soa-thoughts.blogspot.co.uk/2010/08/wcf-sql-adapter-table-operations.html). This gives you the step-by-step tutorial on all CRUD operations (i.e. CREATE, REQUEST, UPDATE and DELETE).

    You can also download sample from Microsoft related SQL Adapter through (http://msdn.microsoft.com/en-us/biztalk/dd796258.aspx). In samples, you can find the Performing Operations on Tables sample which shows you how to perform operations select/insert operation on tables in SQL Server.

    This (social.technet.microsoft.com/wiki/contents/articles/6781.biztalk-adapter-pack-2010-wcf-sql-adapter-en-us.aspx) on BizTalk Adapter Pack 2010 - WCF SQL Adapter should also help you.

    And (http://www.cloudcasts.net/ViewWebcast.aspx?webcastid=2521554268962766719) webcast on this context is also helpful.

    I hope this helps.

    Regards,

    Rachit

    --------------------------------------------------------------------------------------------

    Please mark as answered if this answers your question.

    Wednesday, September 17, 2014 10:49 AM
    Moderator
  • There are some ways to do it:
    • Create a archive pipeline component which can insert/archive the recieved message in to an column in SQL. This can use standard SQL-data access code to insert the message.
    • Use a standard .NET helper and use standard .NET code to insert the XML into SQL table column.
    • In an Orchestration, map the incoming message to a SQL Stored Procedure Message.  Convert the Xml content as a string using the following suggestion and insert the string (which is an received XML) in to db: http://connectedpawns.wordpress.com/2009/08/01/how-to-copy-the-entire-node-to-element-of-string-type-in-a-map/
    • I don’t know the exact requirement but depending on the requirement, you can consider Tracking. I have many question on this context but people oversee the usage of BizTalk Tracking. Here the receive message is stored in SQL DB.

    Some of the following articles can help you on this context:

    Using a SQL Adapter to update an XML datatype column

    http://zeetalks.wordpress.com/2008/07/03/using-a-sql-adapter-to-update-an-xml-dattype-column/

    http://social.msdn.microsoft.com/Forums/en-US/8fea88e6-5f8b-4e51-9c7a-6b97f90c07ea/save-whole-xml-file-in-sql-table-field?forum=biztalkgeneral

    http://connectedpawns.wordpress.com/2009/08/01/how-to-copy-the-entire-node-to-element-of-string-type-in-a-map/

     


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply.

    • Marked as answer by Praveen PN Friday, September 26, 2014 4:54 AM
    Wednesday, September 17, 2014 11:10 AM
  • If you want to also store some metadata, you should first create the table that includes all the columns, including that field for the xml message. I have used nvarchar (max) out of habit. Then create the procedure that stores the data to the table.

    With WCF adapter & "Consume Adapter Service" you can create the structures and port configurations for you.

    You can create the xml message quite easily like this (or you can use map) in a message assignment scope.A

    varIncomingXml = msgReq.parameters;

    xmlDoc.LoadXml("<ns0:Procedure_Name xmlns:ns0=\"http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"><ns0:SystemId>" + varSystemId + "</ns0:SystemId><ns0:Name>" + varName + "</ns0:Name><ns0:Message>Placeholder</ns0:Message></ns0:Procedure_Name>");

    msgToSQL= xmlDoc;

    xpath(msgToSQL,"//*[local-name()='Message']/text()") = varIncomingXml.OuterXml;

    After that you have the xml in a nvarchar-field in the database.

    Next you create a procedure that returns the correct lines when you want to process it, and it should be quite similar process to load to XML-type variable with LoadXml the content from the return message field.

    I had the store part in hand, still a little bit work to be done before I implement the receive from SQL part...

    Wednesday, September 17, 2014 11:22 AM
  • The first question is why wouldn't you use a Long Running Orchestration?
    Wednesday, September 17, 2014 11:23 AM
    Moderator