|
Home > Archive > BizTalk Server > March 2006 > SQL Adapter - Hierarchically Messages
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL Adapter - Hierarchically Messages
|
|
| docnyc@gmail.com 2006-03-16, 5:51 pm |
| 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?
| |
| Greg Forsythe 2006-03-16, 8:47 pm |
| 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?
>
| |
| docnyc@gmail.com 2006-03-17, 5:52 pm |
| 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
| |
| Greg Forsythe 2006-03-17, 5:52 pm |
| 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><root><record><field>Data</field></record></root></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
>
|
|
|
|
|