A question concerning the BizTalk SQL Adapter, can you call a store
procedure and pass the message as a XML document to the store proc? If
so can you explain how?
Secondly if the answer is no to the above, how do you insert
hierarchically structured XML messages with the BizTalk SQL Adapter?

[ Post a follow-up to this message ]

You can use a stored proc passing a ntext parameter.
xmlDocVar = inputmessage
xpath(SQL_SP_Message, "parameter xpath") = xmlDoc.Var.OuterXml;

You stored procedure will need to use OPENXML to process this Xml data.

You can also use an updategram - see Insert Order
http://msdn.microsoft.com/library/d...768ff245df1.asp

Greg

<docnyc@gmail.com> wrote in message
news:1142552778.352213.29760@z34g2000cwc.googlegroups.com...
>A question concerning the BizTalk SQL Adapter, can you call a store
> procedure and pass the message as a XML document to the store proc? If
> so can you explain how?
> Secondly if the answer is no to the above, how do you insert
> hierarchically structured XML messages with the BizTalk SQL Adapter?
>

[ Post a follow-up to this message ]

Thanks for your response.  A clarification question. where are u
executing the following script
xmlDocVar = inputmessage
xpath(SQL_SP_Message, "parameter xpath") = xmlDoc.Var.OuterXml;

in a Orchestration?
.
Our solution is a  message only solution, so should I execute this in a
pipeline?
.
Thanks
Derek

[ Post a follow-up to this message ]

Yes this code would execute in a Message Assignment shape in an
orchestration. It is XLANGs code so can only be used in an orchestration.
You might be able to do this in a map. The Xml needs to be escaped, so some
scripting functoid will be required.
You will need something like this
<sqlmessage>
<parameter>&lt;root&gt;&lt;record&gt;&lt;field&gt;Data&lt;/field&gt;&lt;/rec
ord&gt;&lt;/root&gt;</parameter>
</sqlmessage>

The updategram approach would require a simpler map.

One of the problems with a message only approach using SQL adapter will be
error handling.

Greg

<docnyc@gmail.com> wrote in message
news:1142632231.502657.124770@e56g2000cwe.googlegroups.com...
> Thanks for your response.  A clarification question. where are u
> executing the following script
> xmlDocVar = inputmessage
> xpath(SQL_SP_Message, "parameter xpath") = xmlDoc.Var.OuterXml;
>
> in a Orchestration?
> .
> Our solution is a  message only solution, so should I execute this in a
> pipeline?
> .
> Thanks
> Derek
>

[ Post a follow-up to this message ]