Unanswered

Pare JSON as Input for SQL Server Stored Procedure

(0) Share

Report

Posted on by tjestesjr

135

My basic flow is pictured below. I am parsing an Excel file passed in from a file attachment control in Power Apps. This works fine, I am doing a Get Tables as I am not sure what the user will name the table in the file, then a List Rows, Compose, and Parse JSON. This is all working well. My problem is how do I take the "Parse JSON" output and use it as input for my stored procedure which I will then use OPENSJON in Sql Server to load the data. The SP works fine when I pass it the raw JSON produced by the Parse JSON step.

Categories:

General topics

I have the same question (0)

All responses (6)

Answers (1)

SudeepGhatakNZ 14,394 Most Valuable Professional on at

Like (0)

Report

@tjestesjr

What are the input parameters for your stored procedure?

Do you want to call the stored procedure for each row in excel?

If yes, you need to move the action inside the loop and refer the Parse JSON fields from the dynamic content.

Was this reply helpful? Yes No
tjestesjr 135 on at

Like (0)

Report

My Stored Procedure accepts a NVARCHAR(MAX) for the JSON string. I do not want to call the SP but once when the Excel has been parsed to JSON.

Was this reply helpful? Yes No
SudeepGhatakNZ 14,394 Most Valuable Professional on at

Like (1)

Report

Do you prefer executing the stored procedure separately for each record in Excel, or transmitting all items in a JSON array to Excel at once?

Using 'Parse JSON' is typically reserved for extracting specific values from JSON output, which doesn't seem to be your intention.

Given that the output of 'List rows' is usually in JSON format, why are you using 'Parse JSON' here?

Can you explain with an example?

Was this reply helpful? Yes No
tjestesjr 135 on at

Like (0)

Report

I am trying to Parse an Excel file and save it to SQL Server, that is my ultimate goal. I can do this with a loop and insert by row from excel to SQL but the performance is poor inserting row by row, so now I am attempting to use Power Automate to parse the excel file into JSON and pass the JSON string to my Stored Procedure which then does an OPENJSON call to bulk load the data. The stored procedure works fine with a JSON string as input just trying to figure out how to do that with Power Automate. If the Parse JSON is an unneeded step in my case then that is just my inexperience.

Was this reply helpful? Yes No
Verified answer

SudeepGhatakNZ 14,394 Most Valuable Professional on at

Like (1)

Report

Okay,

In that case you can just provide the output of the List rows action to your Stored procedure

Let me know if you run into issues

Was this reply helpful? Yes No
tjestesjr 135 on at

Like (0)

Report

I was able to achieve what I wanted with the following flow. Thanks for your help!

Was this reply helpful? Yes No