Power Platform Community Forum Thread Details

Hello

I am working with a very niche ERP and have used the API to create a custom connector to collect information from it. The data is returned in the following format

{
 "RXYZ":{
 "0":{
 "F2001":"Product 1",
 "F2208":"P1",
 "F3066":"N",
 "F3529":"0"
 },
 "1":{
 "F2001":"Product 2",
 "F2208":"P2",
 "F3066":"N",
 "F3529":"0"
 },
 "2":{
 "F2001":"Product 3",
 "F2208":"P3",
 "F3066":"N",
 "F3529":"0"
 },
 "3":{
 "F2001":"Product 4",
 "F2208":"P4",
 "F3066":"N",
 "F3439":"NL",
 "F3529":"0"
 }
 }
}

So in this example, API index "RXYZ" (this changes based on which API call we wish to do) is returning a list of products. I have used the following expression in a compose box to extract the child items from RXYZ.

xpath(xml(json(concat('{"root": ', string(body('Export_information')), ' }'))), 'root/RXYZ/*')

This means I get the individual products in an xml format as the output of the compose - something like this

Which when the $content is base64 decoded gives me it in the format below

<_x0031_>
 <F2001>Product1</F2001>
 <F2208>P1</F2208>
 <F3066>N</F3066>
 <F3529>0</F3529>
</_x0031_>
<_x0032_>
 <F2001>Product2</F2001>
 <F2208>P2</F2208>
 <F3066>N</F3066>
 <F3529>0</F3529>
</_x0032_>

The "Fxxxx" are the columns/fields in the ERP database, and dependent on the type of API call we're doing can be different, but there are also some optional fields (hence some products have 4 fields, and some have 5 etc).

So now I'm at this stage where I'm not quite sure what to do next. Ultimately, I want to generate a CSV table using the data, which means I need the objects in an array - by my knowledge is lacking from now. Could anyone help please?!

Categories:

General topics