Unanswered

Convert WebServiceResponse or json string into list or data row

(1) Share

Report

Posted on by sakshinatekar

Is there any way to convert WebServiceResponse or json string into list or data row.

I want to insert it to SQL table later, so want to create a insert query using the data received as a WebServiceResponse

eg. data is as below

{

"SITEID": "15",

"SITENAME": "ABC"

}

Categories:

Power Automate Desktop

I have the same question (0)

All responses (6)

Answers (1)

mmonline 169 on at

Like (1)

Report
Just convert it to a custom object. You can reference the key/value directly or put them into variables and use the variables in the SQL.

The SQL below assumes both fields are text/string. If the SiteIDField is numeric just remove the single quotes around the value.
Insert into TABLENAME (SiteIDField, SiteNameField) VALUES ('%SiteId%','%SiteName%')

If you are receiving several site key/value pairs, use a For Each loop to iterate through and build your SQL in the loop.

Was this reply helpful? Yes No
sakshinatekar 39 on at

Like (0)

Report

sorry for the confusion.
My data is like below
{
"db_status": "Success",
"data": [
{
"SITEID": "11",
"SITENAME": "ABC",
}
]
}

so it gives error to jsonAsObject.SITEID
Also tried jsonAsObject['data'].SITEID

one more problem found is after making it as a custom object it is converting data as below
{ 'SITEID': 4, 'SITENAME': ABC}
It is replacing double quotes to single quotes to keys and no quotes to values. This again creates a new problem

Was this reply helpful? Yes No
mmonline 169 on at

Like (0)

Report
Before I provide a solution, are the potentially multiple sites returned - where the array of sites several site key/value pairs?

As in:
{ "db_status": "Success", "data": [ { "SITEID": "11", "SITENAME": "ABC", }, { "SITEID": "15", "SITENAME": "XYZ", }, { "SITEID": "18", "SITENAME": "QRS", } ] }

Was this reply helpful? Yes No
Verified answer

mmonline 169 on at

Like (1)

Report

If your data only has a single site, per JSON returned, you would use:
theJSONObject.data[0].SITEID & theJSONObject.data[0].SITENAME to reference the keys and return the values.

When you have an array [], the arrays create an ordinal value, starting at 0. So, if there is a single key/value pair, you can always get to it using the [0] indicator.

However, if you have multiple sites returned, per my example above, your flow will look like this.

You will loop through theJSONObject.data.

Each iteration will return an individual site/sitename pair. I set my For Each variable as currentSite rather than CurrentItem for the sake of variable clarity.

Then, in each iteration I can refer to:
currentSite.SITEID & currentSite.SITENAME.

Hope that helps.

Was this reply helpful? Yes No
sakshinatekar 39 on at

Like (1)

Report

This worked for me. Thanks a lot

Was this reply helpful? Yes No
Suggested answer

CU03121756-0 2 on at

Like (0)

Report

If you dont Want use Loop then use this Approach

Was this reply helpful? Yes No