cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
omh
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
v-bacao-msft
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.

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

Hi @v-bacao-msft ,

 

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

 

Thank you.

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.

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.

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

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
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (3,986)