Hi

I have searched far and wide for this, and either I don't use the correct search phrase or it is really uncommon to do...

Anyway, what I need help to accomplish is to store a incoming XML directly to ONE field in a database.

My database table have this structure:
| ID | ReceivedFrom | ReceivedDateTime | FinnishedProcessedDateTime | Message |

and I want to put the complete XML into the Message column.

Is it enough to map the top node of the incoming schema to the Message field in the schema that maps to the table?
The rest of the fields is populated in the mapping and should not be  a problem.

And what would the correct datatype in the table column be? VARBINARY(MAX)? nvarchar(MAX)? Xml?

I hope someone have done something similar before and can point me in the right direction.

Yes, what you're asking can be done and it has been done.

The only two options I would consider are:

1. Write a custom Archive Pipeline Component that dies the SQL interaction directly.

2. In an Orchestration, map the incoming message to a SQL Stored Procedure Message.  You can use one of these techniques to get the Xml content as a string: http://connectedpawns.wordpress.com/2009/08/01/how-to-copy-the-entire-node-to-element-of-string-type-in-a-map/

The other's you set by Distinguished Field as the Context is not available in the map.  FinnishedProcessedTime will be a challenge since you have to correlate back somehow, or do two updates.

However, from experience, this is likely not the correct solution for whatever 'problem' you're trying to address.  BizTalk has other features which provide similar functions out of the box, BAM and Tracking mostly, that satisfy such operational or business requirements when properly understood and configured. 

• Proposed as answer by M.R.ASHWINPRABHUMicrosoft community contributor 22 hours 14 minutes ago
• Marked as answer by DoSJoS 22 hours 2 minutes ago

Thanks.

I didn't think about serializing the xml to a string, that will be just what I need. I have just started using BizTalk and are still navigating throug all the endless possibilities that are out there.

And the reason for why I want to store the whole xml is so that we never loose the original incoming message. So we can go back and look at it if something fail either within BizTalk, or in the application that BizTalk deliveres information to.

I will be back if I need more help ;)

Thanks again :)

Yep, you've just cited the #1 reason for such 'requirements'.

Please, thoroughly investigate BizTalk Tracking.  >95% of the time, it satisfies the archive requirement and provides a better solution overall.

Yes, what you're asking can be done and it has been done.

The only two options I would consider are:

1. Write a custom Archive Pipeline Component that dies the SQL interaction directly.

2. In an Orchestration, map the incoming message to a SQL Stored Procedure Message.  You can use one of these techniques to get the Xml content as a string: http://connectedpawns.wordpress.com/2009/08/01/how-to-copy-the-entire-node-to-element-of-string-type-in-a-map/

The other's you set by Distinguished Field as the Context is not available in the map.  FinnishedProcessedTime will be a challenge since you have to correlate back somehow, or do two updates.

However, from experience, this is likely not the correct solution for whatever 'problem' you're trying to address.  BizTalk has other features which provide similar functions out of the box, BAM and Tracking mostly, that satisfy such operational or business requirements when properly understood and configured. 

• Proposed as answer by M.R.ASHWINPRABHUMicrosoft community contributor Thursday, November 21, 2013 1:12 PM
• Marked as answer by DoSJoS Thursday, November 21, 2013 1:25 PM

Yes, what you're asking can be done and it has been done.

The only two options I would consider are:

1. Write a custom Archive Pipeline Component that dies the SQL interaction directly.

2. In an Orchestration, map the incoming message to a SQL Stored Procedure Message.  You can use one of these techniques to get the Xml content as a string: http://connectedpawns.wordpress.com/2009/08/01/how-to-copy-the-entire-node-to-element-of-string-type-in-a-map/

The other's you set by Distinguished Field as the Context is not available in the map.  FinnishedProcessedTime will be a challenge since you have to correlate back somehow, or do two updates.

However, from experience, this is likely not the correct solution for whatever 'problem' you're trying to address.  BizTalk has other features which provide similar functions out of the box, BAM and Tracking mostly, that satisfy such operational or business requirements when properly understood and configured. 

• Proposed as answer by M.R.ASHWINPRABHUMicrosoft community contributor Thursday, November 21, 2013 1:12 PM
• Marked as answer by DoSJoS Thursday, November 21, 2013 1:25 PM