cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Returning JSON from Execute Stored Procedure and parsing it with Parse JSON

Hello,

 

I am trying to parse the JSON returned by a stored procedure. The JSON string is built inside SQL stored procedure using SELECT ... from @Table FOR JSON PATH

The result of the stored procedure is as follows:

{
"OutputParameters": {},
"ResultSets": {
"Table1": [
{
"Column1": "[{\"ErrorM\":\"\",\"ErrorC\":\"\",\"ReturnD\":null,\"ReturnM\":\"bla\"}]"
}
]
}
}
 
I am trying to parse this result using the Parse JSON step, but somehow it sees the above Column1 as an array, not as an object. 
What could I do in order to be able to extract the data from te procedure as it is, a JSON, not an array?
 
Thank you.
7 REPLIES 7
Highlighted
Community Support
Community Support

 

Hi @omh ,

 

From the output you provide, the value of Column1 is a string, which is the value enclosed in double-quotes.

 

If it is array, it should be "Column1": [{\"ErrorM\":\"\",\"ErrorC\":\"\",\"ReturnD\":null,\"ReturnM\":\ "bla\"}].

 

You could parse this output using Parse JSON. I tested it on my side and it seems that there is no issue.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

Hello @v-bacao-msft ,

 

Is there any way to return an array from the sql procedure? Even if I set the return as JSON, I get the output as a string..

 

My script from SQL is 

SELECT ErrorMessage, ErrorCode, ReturnData , ReturnMessage from @ResultsTable FOR JSON PATH , INCLUDE_NULL_VALUES

Highlighted

Hi @v-bacao-msft ,

 

Could you please share the flow configuration and the JSON schema?

 

Thank you.

Highlighted

Hi @omh , I use stored procedures a lot with Flow (mostly from OnPremises SQL Servers).
But I've never used the FOR JSON PATH , INCLUDE_NULL_VALUES syntax, just the regular SELECT and then parse the results in Flow.

Works like a charm for me.

Highlighted

Hello @Maurits ,

 

Thank you for your answer. This is what I successfully managed to do..but the request is to return the data as a JSON string.

Highlighted

That's what I do too most of the times.
Like image below, Flow is executed from a PowerApp.
It executes a stored procedure. The Response action sends the data back to the PowerApp.
The Response action actually creates the JSON string format based on the desired schema I supplied.

 

Capture.PNG

Highlighted

Hello @Maurits,

 

This is the configuration of my flow and the result of the stored procedure.  I managed to extract the data in the end, but I don;t know if this is a "happy" path 🙂  StoredProcedure Output.PNGJSON Configuration.PNG

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Users online (9,055)