I had an XML document “SCEPTRESafetyReport” which consists of multiple node i.e there are node which repeat itself and out of which one node is “Case_Reaction”. In Case_reaction node there are 2 elements Start Date and End Date which are going as input to the Stored Procedure.
Consider an Example where
Start date End date
Reaction 1 16-MAR-2007 19-MAR-2007
Reaction 2 30-MAR- 2007
Now in this case when I am looping through the Input document “SCEPTRESafetyReport/case_reaction”, and mapping the input values to the Stored procedures input parameters, the first Record get inserted properly but in second record the Value of End Date is getting populated from Reaction 1.
I had done proper null checks and try everything.
Can any one tell me how to set the date value as NULL in a stored procedure?
Did you tried stepping thru the the loop step of /Reaction and see if the pipeline getting the second iteration value also and mapping to your SP??I think this should be a loop iteration problem
Yes I did check the pipeline value, during second iteration of the loop the value is getting picked but while setting it is always setting the value of Iteration 1. If you specify End Date for both the iteration the logic works fine and if we specify the first end date and leave the second end date blank it fails.
In this case you can use a Branch step inside the loop and check Start date, End date if $null value (ignore mapping) else $default map the value to the SP.It should solve your issue.
It is still mapping the End Date value from first Iteration only. The Input document which is getting passed to the SP has end Date = null. My problem is how can I set this null as input value to a date column of the Stored Procedure.
So is the $null condition criteria of the branch step not working properly? Can you tell us your flow steps or upload screen shots if possible that currently you have in place and so that we can get a better picture.If not i other users can assist you.
The input to the Stored Procedure is an XML document. I added a map step inside a loop from where I declare one string variable and map the value of End Date from XML in to this variable. This variable gets mapped to the input of the Stored procedure. After doing the Insert I am dropping the End Date variable. But still during the next Iteration the the value of first iteration is getting set as the input.
Resolution was when I was traversing in a loop, after calling the adapter service I was not dropping the Input of that adapter service. Once I drop that input before moving ahead for next iteration it works.