Using outputs from SQL procedure

[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": {}
}

[abm · Accepted solution]

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:

[ChristianAbata]

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.

[PAUser70]

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.

[PAUser70]

[abm]

Hi @PAUser70 

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

Thanks