Inserting entire XML message into database

Hi, sure I’ll try to explain better.  I’ll just use our example and then you could perhaps convert that to your situation.  First to answer your questions:“I’m assuming you mean you generated it in BizTalk through the “Add Generated Items” option” – Yes.  And yes SQLService.XSD can use attributes just as well 🙂  so now on I’ll talk about attributes not elements to keep things simple.

OK, my situation: I have a Receive shape at the top that activates this orchestration(In this case it is the ProcessSalesOrdersOrchestration).  The very first thing that I do after the Receive shape is an Expression shape with this code:

MsgXmlLogMessage = MsgSalesOrderRcvd;  // Initialize the XmlDocument message

Now I have two messages in the Orchestration: MsgXmlLogMessage(type System.Xml.XmlDocument) and typed MsgSalesOrderRcvd.

Now I need to create a typed message from schema CreateLogEntry(your case SQLService) called MsgRcvLogRequest.  To do that I have a Construct shape and inside it I have a Transform and an Assignment shape.  In the Transform shape I have a map from schema SalesOrder to schema CreateLogEntry where I copy the OrderId, Date, etc to some of my columns in the Log table(in this map I for example map the OrderId to the attribute XmlMessage so that I initialize that attribute before the assignment shape).  Then in the Assignment shape I copy the message MsgXmlLogMessage into this attribute XmlMessage like this:

MsgRcvLogRequest.parameters.CreateLogEntry.XmlMessage = MsgXmlLogMessage.OuterXml;  // I promoted the property XmlMessage

Now I have three messages the original SalesOrder, the typed CreateLogEntry with some attributes like OrderId, Date, Origin, etc and also a attribute called XmlMessage which contains a copy of the SalesOrder xml message and the untyped XmlDocument MsgXmlLogMessage.  To do this we needed the untyped XmlDocument message just to copy the data for us through this fine OuterXml property 🙂

OK, now I have explained better I hope.

I just read your post over again and saw a few things:

“Also, if the incoming message in your example is typed
(MsgSalesOrderRcvd), then the expression editor throws an error when
it’s assigned to a different type message (MsgXmlLogMessage)”  – I assign the other way around, I assign the typed MsgSalesOrderRcvd into the untyped MsgXmlLogMessage: MsgXmlLogMessage = MsgSalesOrderRcvd;

“but if I’m receiving an untyped message (of type System.Xml.Document
already because multiple other orchestrations need to send various
messages of different formats to this one for logging in the DB) then I
don’t have the option of selecting it as a source for the map” – I see this as an problem.  In order to assign the promoted property through Assignment shape we need to initialize the original message with a transform shape.  Perhaps you could create a very general schema with nothing but the type <any> element and then create a map from that schema to your SQLService schema and just put some hard coded value into your XmlMessage attribute (and other attributes you need) and then right after assign the correct Xml document to the XmlMessage attribute… ??  Maybe some xpath could help you here I no very little about xpath so sorry about that.

I’ll monitor this thead until you get this to work because I know we are close 🙂