|
Home > Archive > BizTalk Server General > December 2004 > How Do I Use Promoted Property Value In an Orchestration
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 |
How Do I Use Promoted Property Value In an Orchestration
|
|
|
| Hi,
How do I use the value of a promoted property as a parameter to a stored
procedure using the SQL Adapter?
In my orchestration, I have a receive port that runs a stored procedure
(e.g., "GetPlans") every 60 seconds looking for records that have been
flagged to be processed. Let's say that the storedproc finds one record to
process, so the SQL Adapter returns that record as an XML message. Here's an
example of what gets returned:
<Plan>
<PlanID>123</PlanId
<PlanName>My Test Plan</PlanName>
</Plan>
I have set PlanID as a promoted property. Within my orchestration, I would
like to use the value of PlanID to call a second stored procedure (e.g.,
"GetPlanParticipants") that takes a PlanID input parameter to get the
information about participants that belong to PlanID "123". How do I go about
using the value of PlanID returned from my first stored procedure as the
input parameter of my second stored procedure?
The XML returned from the second stored procedure would look something like
this:
<Plan>
<PlanID>123</PlanID>
<Participants>
<Participant>
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Participant>
<Participant>
<FirstName>Charlie</FirstName>
<LastName>Buckets</LastName>
</Participant>
</Participants>
</Plan>
Any help is greatly appreciated!!!
Thanks,
Mike
| |
| Jon Flanders 2004-12-30, 5:52 pm |
| Hi Mike. You can get the value from the message context. You access the
message context in an orchestration from an expression shape.
Message_1(MyNamespace.PropName);
If you add a variable you can do this:
var = Message_1(MyNamespace.PropName);
--
Jon Flanders [DevelopMentor]
http://staff.develop.com/jfland/
http://www.develop.com/courses/biztalk
"Mike" <Mike@discussions.microsoft.com> wrote in message
news:004E979F-4A58-4A33-AE4F-66BC16A7F2BC@microsoft.com...
> Hi,
>
> How do I use the value of a promoted property as a parameter to a stored
> procedure using the SQL Adapter?
>
> In my orchestration, I have a receive port that runs a stored procedure
> (e.g., "GetPlans") every 60 seconds looking for records that have been
> flagged to be processed. Let's say that the storedproc finds one record to
> process, so the SQL Adapter returns that record as an XML message. Here's
an
> example of what gets returned:
>
> <Plan>
> <PlanID>123</PlanId
> <PlanName>My Test Plan</PlanName>
> </Plan>
>
> I have set PlanID as a promoted property. Within my orchestration, I would
> like to use the value of PlanID to call a second stored procedure (e.g.,
> "GetPlanParticipants") that takes a PlanID input parameter to get the
> information about participants that belong to PlanID "123". How do I go
about
> using the value of PlanID returned from my first stored procedure as the
> input parameter of my second stored procedure?
>
> The XML returned from the second stored procedure would look something
like
> this:
>
> <Plan>
> <PlanID>123</PlanID>
> <Participants>
> <Participant>
> <FirstName>John</FirstName>
> <LastName>Doe</LastName>
> </Participant>
> <Participant>
> <FirstName>Charlie</FirstName>
> <LastName>Buckets</LastName>
> </Participant>
> </Participants>
> </Plan>
>
> Any help is greatly appreciated!!!
>
> Thanks,
>
> Mike
>
>
| |
| Matt Milner 2004-12-30, 5:52 pm |
| Keep in mind that promoted properties are only able to be used on
non-repeating elements/attributes. So, if your first receive port returns
more than one row, you won't be able to access your plan id. you'll need to
use something like XPath or mapping to get at the values you want.
Matt
"Mike" <Mike@discussions.microsoft.com> wrote in message
news:004E979F-4A58-4A33-AE4F-66BC16A7F2BC@microsoft.com...
> Hi,
>
> How do I use the value of a promoted property as a parameter to a stored
> procedure using the SQL Adapter?
>
> In my orchestration, I have a receive port that runs a stored procedure
> (e.g., "GetPlans") every 60 seconds looking for records that have been
> flagged to be processed. Let's say that the storedproc finds one record to
> process, so the SQL Adapter returns that record as an XML message. Here's
> an
> example of what gets returned:
>
> <Plan>
> <PlanID>123</PlanId
> <PlanName>My Test Plan</PlanName>
> </Plan>
>
> I have set PlanID as a promoted property. Within my orchestration, I would
> like to use the value of PlanID to call a second stored procedure (e.g.,
> "GetPlanParticipants") that takes a PlanID input parameter to get the
> information about participants that belong to PlanID "123". How do I go
> about
> using the value of PlanID returned from my first stored procedure as the
> input parameter of my second stored procedure?
>
> The XML returned from the second stored procedure would look something
> like
> this:
>
> <Plan>
> <PlanID>123</PlanID>
> <Participants>
> <Participant>
> <FirstName>John</FirstName>
> <LastName>Doe</LastName>
> </Participant>
> <Participant>
> <FirstName>Charlie</FirstName>
> <LastName>Buckets</LastName>
> </Participant>
> </Participants>
> </Plan>
>
> Any help is greatly appreciated!!!
>
> Thanks,
>
> Mike
>
>
| |
|
| Thanks Jon!
"Jon Flanders" wrote:
> Hi Mike. You can get the value from the message context. You access the
> message context in an orchestration from an expression shape.
>
> Message_1(MyNamespace.PropName);
>
>
> If you add a variable you can do this:
>
> var = Message_1(MyNamespace.PropName);
>
> --
> Jon Flanders [DevelopMentor]
> http://staff.develop.com/jfland/
> http://www.develop.com/courses/biztalk
> "Mike" <Mike@discussions.microsoft.com> wrote in message
> news:004E979F-4A58-4A33-AE4F-66BC16A7F2BC@microsoft.com...
> an
> about
> like
>
>
>
| |
|
| Thanks for your response, Matt. My first receive port will definitely be
returning more than one record, so I guess using a promoted property is out.
If possible, could you suggest a general idea of how my orchestration might
look? (I'm a beginner at BizTalk w/no .Net experience, so any suggestions
would be greatly appreciated). I have my first receive port that calls a
stored proc & returns my plans. This port is hooked to a receive shape. I
then had a send shape hooked to my second port that gets the participant
info. I'm guessing that I would need to some extra steps between the first
receive shape & the send shape that is connected to my second port. How
would I go about extracting the PlanID value from my first message & pass it
as a parameter to the second port that get participant information (i.e.,
what shapes should be used to accomplish this)?
Thanks,
Mike
"Matt Milner" wrote:
> Keep in mind that promoted properties are only able to be used on
> non-repeating elements/attributes. So, if your first receive port returns
> more than one row, you won't be able to access your plan id. you'll need to
> use something like XPath or mapping to get at the values you want.
>
> Matt
>
> "Mike" <Mike@discussions.microsoft.com> wrote in message
> news:004E979F-4A58-4A33-AE4F-66BC16A7F2BC@microsoft.com...
>
>
>
| |
| Matt Milner 2004-12-31, 5:48 pm |
| The easiest way is probably a map which would use a script functoid with
XSLT to get out the plan IDs from the first message and copy them into the
second message. It depends on how your second stored procedure is going to
handle multiple plan IDs though. If you are going to call it once for each
ID, then you can use XPath in your message assignment shape (see the docs on
this), and/or in the looping shape.
So, for example, you might have a loop, and before you start hte loop, you'd
extract the count of plan IDs being returned in your first message. You'd
set a variable with this value and then loop until an index variable was
equal to that count. Then in your loop you would have to extract each plan
ID using XPath and make a call to your second stored procedure. Then
increment your counter.
The other option, which is probably easier to implement and likely to
perform better since you won't have to do XPath in the orchestration is to
treat your response from the first stored procedure as an envelope and have
each Plan element end up being its own message. Then you'll get a message
for each plan and therefore an orchestration instance for each plan. Then
you should be able to use the syntax Jon mentioned and a promoted property
to get your parameter, or map it to the new message.
There are examples of envelopes in the SDK and the XPath stuff is documented
in the help. I'd start with the envelopes and see if that will work for you.
Matt
"Mike" <Mike@discussions.microsoft.com> wrote in message
news:04A65CAD-B942-479A-80AD-B1DF7E830DEB@microsoft.com...[vbcol=seagreen]
> Thanks for your response, Matt. My first receive port will definitely be
> returning more than one record, so I guess using a promoted property is
> out.
>
> If possible, could you suggest a general idea of how my orchestration
> might
> look? (I'm a beginner at BizTalk w/no .Net experience, so any suggestions
> would be greatly appreciated). I have my first receive port that calls a
> stored proc & returns my plans. This port is hooked to a receive shape. I
> then had a send shape hooked to my second port that gets the participant
> info. I'm guessing that I would need to some extra steps between the
> first
> receive shape & the send shape that is connected to my second port. How
> would I go about extracting the PlanID value from my first message & pass
> it
> as a parameter to the second port that get participant information (i.e.,
> what shapes should be used to accomplish this)?
>
> Thanks,
>
> Mike
>
>
> "Matt Milner" wrote:
>
| |
|
| Matt,
Thanks for the great suggestions (and the help you offer to everyone on
these message boards)! I think I will start by going through the examples of
envelopes in the SDK as you suggest. Have a Happy New Year!
Mike
"Matt Milner" wrote:
> The easiest way is probably a map which would use a script functoid with
> XSLT to get out the plan IDs from the first message and copy them into the
> second message. It depends on how your second stored procedure is going to
> handle multiple plan IDs though. If you are going to call it once for each
> ID, then you can use XPath in your message assignment shape (see the docs on
> this), and/or in the looping shape.
>
> So, for example, you might have a loop, and before you start hte loop, you'd
> extract the count of plan IDs being returned in your first message. You'd
> set a variable with this value and then loop until an index variable was
> equal to that count. Then in your loop you would have to extract each plan
> ID using XPath and make a call to your second stored procedure. Then
> increment your counter.
>
> The other option, which is probably easier to implement and likely to
> perform better since you won't have to do XPath in the orchestration is to
> treat your response from the first stored procedure as an envelope and have
> each Plan element end up being its own message. Then you'll get a message
> for each plan and therefore an orchestration instance for each plan. Then
> you should be able to use the syntax Jon mentioned and a promoted property
> to get your parameter, or map it to the new message.
>
> There are examples of envelopes in the SDK and the XPath stuff is documented
> in the help. I'd start with the envelopes and see if that will work for you.
>
> Matt
>
>
> "Mike" <Mike@discussions.microsoft.com> wrote in message
> news:04A65CAD-B942-479A-80AD-B1DF7E830DEB@microsoft.com...
>
>
>
|
|
|
|
|