Trying to iterate over XML converted to JSON
I would like to do the following things with the US State Department RSS feed located here: https://travel.state.gov/_res/rss/TAsTWs.xml
- convert the data into an array
- filter that array by date so that dates on or before a given date are removed
- update various country tags (for example making A3 to 44)
- Save the array to an excel file with only the country name, threat-level, and country tag columns retained.
To accomplish this, my flow so far uploads the XML to onedrive, converted it to JSON, Parses that JSON, then proceeds to be unable to successfully iterate over anything.
The schema my power automate generated is below. After parsing, I start with an apply to each for @{body('Parse_JSON')?['rss']?['channel']?['item']}. Within that apply each I use a second apply each on @{items('Apply_to_each_2')?['pubDate']} and then I attempt to see if I can appended each result within the apply each to an array Variable I have previously initialized. The outer apply is called "Apply for each" the inner is called "Apply for each 2". When I try to append the pubDate variable to the array variable I initialized I use the following syntax:
{ "pd": @{items('Apply_to_each_2')?['pubDate']}} what power automate
comes back with is the message:
The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@items('Apply_to_each_2')?['pubDate']' is of type 'String'. The result must be a valid array.
Where am I going wrong?
{
"type": "object",
"properties": {
"?xml": {
"type": "object",
"properties": {
"@@version": {
"type": "string"
},
"@@encoding": {
"type": "string"
}
}
},
"rss": {
"type": "object",
"properties": {
"@@xmlns:dc": {
"type": "string"
},
"@@version": {
"type": "string"
},
"channel": {
"type": "object",
"properties": {
"title": {
"type": "string"
},
"link": {
"type": "string"
},
"description": {
"type": "string"
},
"item": {
"type": "array",
"items": {
"type": "object",
"properties": {
"title": {
"type": "string"
},
"pubDate": {
"type": "string"
},
"link": {
"type": "string"
},
"guid": {
"type": "string"
},
"category": {
"type": "array",
"items": {
"type": "object",
"properties": {
"@@domain": {
"type": "string"
},
"#text": {
"type": "string"
}
},
"required": [
"@@domain",
"#text"
]
}
},
"dc:identifier": {
"type": "string"
},
"description": {
"type": "object",
"properties": {
"#cdata-section": {
"type": "string"
}
}
}
},
"required": [
"title",
"pubDate",
"link",
"guid",
"category",
"dc:identifier",
"description"
]
}
}
}
}
}
}
}
}
I think you need to be taking an approach of using the Select operation if you want to transform the converted JSON to a more simplified structure.
Bottom line, Apply to each and Select need to iterate over arrays and I may be telling you how to suck eggs here but, an array is denoted by square brackets, e.g. ...
{
"thisIsAnArray": [
{
"property1": "value1.1",
"property2": "value1.2",
"property3": "value1.3"
},
{
"property1": "value2.1",
"property2": "value2.2",
"property3": "value2.3"
}
]
}
Instead of using Parse JSON I just used a compose statement.
Sample Flow
Compose XML
This is nothing more than the XML you provided.
Compose JSON
It merely contains this expression ...
json(xml(outputs('Compose_XML')))
Select
This step transforms the pubDate property to a pd property (like you were doing) and essentially flattens the JSON back to a more simplified array of objects.
These are the expressions contained within ...
From = outputs('Compose_JSON')['rss']['channel']['item']
pd = item()['pubDate']
You just need to build on the maps for additional properties.
Alternatively, you could use an Apply to each but it will take longer and also need additional controls if you want the original order of the JSON to be honoured in the resulting output AND you'd need to do the work to append to an existing array variable. Again, more work than using Select.
Result
