Answered

parse json of SQL stored procedure

(0) Share

Report

Posted on by asynadinou

Hello,

I want to create a flow that makes the following: It is being triggered on an http request. It then executes an SQL stored procedure and the resultset of the stored procedure should be returned back as the http response. The result of the stored procedure is a json like the following:

{
"Cards": [
{
"Card": {
"CardCategory": "Credit Cards",
"CardNumber": "1007"
}
},
{
"Card": {
"CardCategory": "Debit Cards",
"CardNumber": "9657"
}
}
]
}

When I use the Outputs of the Execute Stored Procedure step as the Body of the http Response box and make the call through postman I get the following as a response body:

"body": {

"ResultSets": {

"Table1": [

{

"JSON_F52E2B61-18A1-11d1-B105-00805F49916B": "{\"Cards\":[{\"Card\":{\"CardCategory\":\"Credit Cards\",\"CardNumber\":\"9999\"}},{\"Card\":{\"CardCategory\":\"Debit Cards\",\"CardNumber\":\"3015\"}}]}"

}

]

"OutputParameters": {}

Does anybody know how I can extract the json (without Table 1 etc..)? Should I use parse json or any compose box? If yes, how should I configure them?

Thanks a lot

Katerina

Categories:

General topics

All responses (14)

Answers (1)

Sort by

Archy 83 on at

Like
a
(0)

Report
Copy link

Link copied!
After executing the stored procedure, you will have the result in the format you've shown, with the JSON string nested under "Table1".
Add a "Parse JSON" action after your "Execute Stored Procedure" action.
For the "Content" field in the "Parse JSON" action, use the expression to reference the JSON string.
Based on your example, the expression would be something like:

body('Execute_Stored_Procedure')?['ResultSets']['Table1'][0]['JSON_F52E2B61-18A1-11d1-B105-00805F49916B']

For the "Schema" field, click on "Generate from sample" and paste a sample of the JSON structure you expect to receive

Now in the "HTTP response" action, for the body, you can directly use the output of the "Parse JSON" action. Since the "Parse JSON" action has already parsed the JSON string into an object, you can reference this object directly.

This setup should allow you to extract and return the JSON data from the SQL stored procedure result without the "Table1" wrapper in your HTTP response.
_____________________________________________________________________________________________
If my answer helps you solve the problem, please hit the 👍 and accept it as a solution.
Chriddle 8,729 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!
3 different approaches:

Compose

If there is only one item in the Table1 array and you know its property name

json( first( body('ResponseData')['ResultSets/Table1'] )['JSON_F52E2B61-18A1-11d1-B105-00805F49916B'] )

Compose 2

If there is only one item in the Table1 array and you don't know its property name

json( first( xpath( xml(json(concat('{"root":', body('ResponseData'), '}'))), '//Table1/*/text()' ) ) )

Select

If there are multiple items in Table1 and you don't know their propery names

From:

xpath( xml(json(concat('{"root":', body('ResponseData'), '}'))), '//Table1/*/text()' )

Map:

json(item())

Result

Of course, you'll need to match body('ResponseData') in these expressions to your connector name 😉
asynadinou 61 on at

Like
a
(0)

Report
Copy link

Link copied!

It seems that the content of parse json node is not defined properly. I get the following error:
The 'content' property of actions of type 'ParseJson' must be valid JSON. The provided value cannot be parsed: 'Unexpected character encountered while parsing value: b. Path '', line 0, position 0.'.
Archy 83 on at

Like
a
(0)

Report
Copy link

Link copied!

Add a "Compose" action right before the "Parse JSON" action.
Set the input of the "Compose" action to the same expression or dynamic content you're trying to use in the "Parse JSON" action. This will allow you to see exactly what data is being passed to the "Parse JSON" action, helping you identify any discrepancies or unexpected characters.
asynadinou 61 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for your response Archy. Although I added the compose action, it still isn't clear what the issue is, since both Input and Output are exactly the same.

Do you have any other idea?
Chriddle 8,729 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!

You should input this as an expression 😉
asynadinou 61 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Chriddle, thanks a lot for your reply. Below the result.
Any idea?
Chriddle 8,729 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!

You should not mix @Archy 's and my expressions 😉
asynadinou 61 on at

Like
a
(0)

Report
Copy link

Link copied!

Sorry for that!
Using your select action as configured in your previous post and then using the output of the select as the body in the response action, the results are the following

when calling from postman:
Chriddle 8,729 Super User 2026 Season 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Does your Select look like this?

With Map: json(item())