How to use Parse JSON on SQL Stored Procedure results in Microsoft Power Automate

Microsoft Power Automate can parse the results of a SQL Stored Procedure from an On-Premise SQL Gateway connection, but it is not very intuitive. You need to take the ResultSets from the Stored Procedure and remove some of the formatting at the beginning and end to enable the Parse JSON step to correctly process it. Otherwise, you will get errors like the below on the Parse JSON step or the following Apply to Each step:

"invalid type. Expected Array but got Object"

"The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression ... is of type 'Object'. The result must be a valid array"

The problem is that the ResultSets value returned by the Stored Procedure step is in a slightly different format than the Parse JSON step is expecting. The SQL Stored Procedure step in Power Automate always outputs the first result set formatted as an Object containing the array “Table1” instead of just outputting the array itself. To use a basic array of names as an example, this is what the Execute Stored Procedure step will return:

{

"Table1": [

{

"FirstName": "Sam",

"LastName": "Stern"

}

]

}

But the Parse JSON step only wants the array itself, like this:

[

{

"FirstName": "Sam",

"LastName": "Stern"

}

]

So we have to update the format for the Schema input and the Content input in the Parse JSON step.

The easiest way to set the Schema is to create your “Execute Stored Procedure” step and add a “Compose” step that inputs the “ResultSets” from the Stored Procedure.

Then run your Flow. The Compose output should look like the first example above, with the array wrapped in “Table1”. Copy the Output from the Compose step into a Notepad file. Manually delete the first “{” and “‘Table1’ :” from the beginning and the corresponding “}” from the end, which should leave it looking like the 2nd example shown above. This can now generate a working Schema, so add your Parse JSON step and hit “Generate from sample” and paste it in, then hit “Done”:

Now that the schema is set we need to format the “Content” input correctly, which is a little harder. The way I found to do this is to convert the whole ResultSets into a string then take the Substring excluding both the first 10 characters (removing { “Table1”:) and the last 1 character (removing }). I did this in 4 Compose Steps stored inside a Scope, but you could alternatively combine it into a single larger step if you prefer.

The 1st step transforms the “ResultSets” into a string:

substring(outputs('Compose_Result_Set_as_String'),10)

The 3rd step gets the length of the remaining string minus 1 to use in the next substring step, which will remove the last character:

sub(length(outputs('Compose_Result_Set_stripped')),1)

The 4th and final step takes the substring excluding the last character, which leaves only the characters we need.

substring(outputs('Compose_Result_Set_stripped'),0,outputs('Compose_Length_of_Substring_minus_1'))

Note that the output is still in string format, so populate your Parse JSON step Content field with the JSON() function to convert it back:

json(outputs('Compose_Result_Set_ready_to_be_an_array'))

With those adjustments, you should now be able to successfully parse JSON on the output of your Execute Stored Procedure step!

Note another common error can occur around the “type” of each field specified in the schema. When the schema sets an explicit type for a field, but the actual data returns Null for a record then it can cause an error. To mitigate that, just remove the “type” line from each field and remove it from the list of required attributes. The default schema generated may look like this:

{

    "type": "array",

    "items": {

        "type": "object",

        "properties": {

            "FirstName": {

                "type": "string"

            },

            "LastName": {

                "type": "string"

            }

        },

        "required": [

            "FirstName",

            "LastName"

        ]

    }

}

To resolve those errors, clear out the “type” lines for each field, then remove each field from the “required” area. Your result should look like this:

{

    "type": "array",

    "items": {

        "type": "object",

        "properties": {

            "FirstName": {

            },

            "LastName": {

            }

        },

        "required": [

        ]

    }

}

Sam Stern

Sam is a Senior Consultant in RSM's Dynamics practice. He has experience working with many aspects of Dynamics CE as well as integration technologies like Scribe and SmartConnect.