How can convert Store procedure body in table and sent via email in power automate

[Manojpatidar840]

How can convert Store procedure body in table and sent via email in power automate, below is the sample from store procedure

[Andrew_J]

@Manojpatidar840 

Okay so you have some JSON in the following:

I do not understand what you want to do with the above.  I can see from your question that you mention Store which has a field of Department.    Can you pleas give me some more detail in what you want to do.

Regards,

Andrew

[Manojpatidar840]

@Andrew_J  Hi there! I'm retrieving data from a stored procedure, which includes fields such as ID, GRVNumber, GRVDate, PONumber, and Department. I'm trying to convert this data into a table format and send it via email. However, I'm facing some issues with JSON formatting. Can you please assist me in resolving this? Thank you!

Or can you please tell me what is the process to sent email as table after retrieving data in store procedure...

[Andrew_J]

@Manojpatidar840 

Thanks that makes better sense for me now.

The data is in JSON so I would think taking this through a select would then enable you to construct a HTML table that you could insert into an email and send.

Perhaps if you have time you can try and let me know how you get on.

Regards,

Andrew

[Manojpatidar840]

@Andrew_J , I am getting below error in Parse JSON "
ValidationFailed
The schema validation failed"

I  wrote the below schema, can you please check anything is wrong with that..

{
"type": "array",
"items": {
"type": "object",
"properties": {
"ID": {
"type": "integer"
},
"GRVNumber": {
"type": "string"
},
"GRVDate": {
"type": "string",
"format": "date-time"
},
"Department": {
"type": "string"
},
"SupplierName": {
"type": "string"
},
"GRVStatus": {
"type": "string"
},
"CreatedBy": {
"type": "string"
},
"PONumber": {
"type": "string"
}
},
"required": ["ID", "GRVNumber", "GRVDate", "Department", "SupplierName", "GRVStatus", "CreatedBy", "PONumber"]
}
}

Thank You 

Manoj

[Andrew_J]

@Manojpatidar840 

That looks fine to me but I am concern that all you have done is to take JSON from the body of the image above and put into anther JSON which has not worked.  Imeadilty below the step in the image can you please add a SELECT, then below that a CREATE HTML TABLE.  I assume you are okay with the dynamics of that.

SELECT - use body from image above then map the fields that you want.

HTML - use the body of the SELECT above.

Once you have tested can you please send me an image of the steps expanded to show the dynamic links and the results of any test in classic UI.

Andrew

[Manojpatidar840]

@Andrew_J Which i shared earlier image it was Body from store procedure not JSON.

Kindly find the below for better understanding.

1:  -Body from SP

2:  - JSON

Getting Error in JSON Output

[Andrew_J]

@Manojpatidar840 

Now I understand better, so it SQL to JSON, I have not delt with this before and have no knowledge of SQL, I am hoping the below may assist if you know more about SQL.

https://powerusers.microsoft.com/t5/Building-Flows/Build-JSON-object/td-p/112517

Andrew