Unanswered

Using outputs from SQL procedure

(0) Share

Report

Posted on by SamTimmins

Hi Team,

I'm attempting to use a flow which returns an SQL stored procedure, and then use the values to create entries into a shared calendar in Outlook.

However, I'm not having much luck with getting the Email & Date value extracted from the return (example below)

I had a look at the string conversion options, and trying to use substring to gather the required values with indexValues however it seemed very messy and I'm sure there is a better way to convert/filter (to an array perhaps?) so that I can easily extract the email and date, particularly when there will be multiple rows returned.

Any suggestions appreciated

{
 "ResultSets": {
 "Table1": [
 {
 "EMail": "email@domain.com.au",
 "TransDate": "2020-02-20T00:00:00"
 }
 ]
 },
 "OutputParameters": {}
}

Categories:

Building flows

I have the same question (1)

All responses (5)

Answers (1)

Verified answer

abm abm 32,865 Most Valuable Professional on at

Like (2)

Report

Hi @SamTimmins

Follow the below steps:

Next use a Parse JSON to extract the values

The above schema I used is as follows: (Copy & Paste the same schema in your flow action step)

{

"type": "object",

"properties": {

"ResultSets": {

"type": "object",

"properties": {

"Table1": {

"type": "array",

"items": {

"type": "object",

"properties": {

"EMail": {

"type": "string"

},

"TransDate": {

"type": "string"

}

},

"required": [

"EMail",

"TransDate"

]

}

}

}

},

"OutputParameters": {

"type": "object",

"properties": {}

}

}

}

Next we need to iterate the JSON object to extract the values

Here are my test results:

Was this reply helpful? Yes No
ChristianAbata 8,951 Most Valuable Professional on at

Like (0)

Report

hi @SamTimmins you can parse your asnwer with parse json.
Frist in content you need to pass your output, then clic on generate from sample an paste the answer that you paste here and then just done, you are going to be able to use every part of your response.

Was this reply helpful? Yes No
PAUser70 10 on at

Like (0)

Report

HI,

I have the same issue trying to extract values from the store procedure's output body. I followed all the steps to create compose and Parse JSON but I am getting an error

ExpressionEvaluationFailed. The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@body('Parse_JSON')?['ResultSets']?['Table1']' is of type 'Null'. The result must be a valid array.

Was this reply helpful? Yes No
PAUser70 10 on at

Like (0)

Report

Was this reply helpful? Yes No
abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

Hi @PAUser70

Could you please post a sample JSON data where I can copy and paste in my test flow.

Thanks

Was this reply helpful? Yes No