I have a challenge with a Logic App I haven't been able to solve. The idea of the app is to
- take an XML file stored as a binary file to blob storage and JSONify it (Parse JSON action)
- loop through the "Values" array in the JSON and add a column with certain value on each round (Compose action) and append the output into an array variable
- store the array variable to CSV (Create CSV table action) and upload the file into data lake
This is easy with a simple JSON, but in our case the schema is very complex.
The first step seems to work as we get a JSON that includes what we need in an array "Values". But it might be that the schema here needs some tweaking as we run into problems later. The resulting JSON is down below.
Where I run into problems is the second step:
Here I get the following error: ExpressionEvaluationFailed. The execution of template action 'For_each' failed: the result of the evaluation of 'foreach' expression '@body('Parse_JSON')?['diffgr:diffgram']?['Results']?['Values']' is of type 'Null'. The result must be a valid array.
I tried also reading the Values into an array variable but it just confirms that we really do not get the actual data rows from under the "Values" tag in the JSON as the output is just empty brackets:
Below are the details of the Parse JSON action and the resulting JSON to help in understanding the problem.
Parse JSON action
Content: Json(Xml(base64ToString(body('Get_blob_content')?['$content'])))
Schema:
{
"properties": {
"diffgr:diffgram": {
"properties": {
"@@xmlns:diffgr": {
"type": "string"
},
"@@xmlns:msdata": {
"type": "string"
},
"Results": {
"properties": {
"Properties": {
"properties": {
"@@diffgr:id": {
"type": "string"
},
"@@msdata:rowOrder": {
"type": "string"
},
"@@stepped": {
"type": "string"
}
},
"type": "object"
},
"Values": {
"items": {
"properties": {
"@@diffgr:id": {
"type": "string"
},
"@@msdata:rowOrder": {
"type": "string"
},
"@@status": {
"type": "string"
},
"@@ts": {
"type": "string"
},
"@@value": {
"type": "string"
}
},
"required": [
"@@diffgr:id",
"@@msdata:rowOrder",
"@@ts",
"@@value",
"@@status"
],
"type": "object"
},
"type": "array"
}
},
"type": "object"
}
},
"type": "object"
},
"xs:schema": {
"properties": {
"@@id": {
"type": "string"
},
"@@xmlns": {
"type": "string"
},
"@@xmlns:msdata": {
"type": "string"
},
"@@xmlns:xs": {
"type": "string"
},
"xs:element": {
"properties": {
"@@msdata:IsDataSet": {
"type": "string"
},
"@@msdata:Locale": {
"type": "string"
},
"@@name": {
"type": "string"
},
"xs:complexType": {
"properties": {
"xs:choice": {
"properties": {
"@@maxOccurs": {
"type": "string"
},
"@@minOccurs": {
"type": "string"
},
"xs:element": {
"items": {
"properties": {
"@@msdata:Locale": {
"type": "string"
},
"@@name": {
"type": "string"
},
"xs:complexType": {
"properties": {
"xs:attribute": {
"items": {
"properties": {
"@@msdata:Caption": {
"type": "string"
},
"@@name": {
"type": "string"
},
"@@type": {
"type": "string"
}
},
"required": [
"@@name",
"@@msdata:Caption",
"@@type"
],
"type": "object"
},
"type": "array"
}
},
"type": "object"
}
},
"required": [
"@@name",
"@@msdata:Locale",
"xs:complexType"
],
"type": "object"
},
"type": "array"
}
},
"type": "object"
}
},
"type": "object"
}
},
"type": "object"
}
},
"type": "object"
}
},
"type": "object"
}
JSON we get as a result from Parse JSON
{
"body": {
"?xml": {
"@version": "1.0",
"@encoding": "utf-8"
},
"DataSet": {
"xs:schema": {
"@id": "Results",
"@xmlns": "",
"@xmlns:xs": "http://www.w3.org/2001/XMLSchema",
"@xmlns:msdata": "urn:schemas-microsoft-com:xml-msdata",
"xs:element": {
"@name": "Results",
"@msdata:IsDataSet": "true",
"@msdata:Locale": "",
"xs:complexType": {
"xs:choice": {
"@minOccurs": "0",
"@maxOccurs": "unbounded",
"xs:element": [
{
"@name": "Values",
"@msdata:Locale": "",
"xs:complexType": {
"xs:attribute": [
{
"@name": "ts",
"@msdata:Caption": "81000-F901C Trend ts (81000-F901C)",
"@type": "xs:dateTime"
},
{
"@name": "value",
"@msdata:Caption": "81000-F901C Trend value (81000-F901C)",
"@type": "xs:float"
},
{
"@name": "status",
"@msdata:Caption": "81000-F901C Trend status (81000-F901C)",
"@type": "xs:string"
}
]
}
},
{
"@name": "Properties",
"@msdata:Locale": "",
"xs:complexType": {
"xs:attribute": {
"@name": "stepped",
"@type": "xs:int"
}
}
}
]
}
}
}
},
"diffgr:diffgram": {
"@xmlns:msdata": "urn:schemas-microsoft-com:xml-msdata",
"@xmlns:diffgr": "urn:schemas-microsoft-com:xml-diffgram-v1",
"Results": {
"Values": [
{
"@diffgr:id": "Values1",
"@msdata:rowOrder": "0",
"@ts": "2020-09-02T00:00:00+03:00",
"@value": "1.41233063",
"@status": "Good"
},
{
"@diffgr:id": "Values2",
"@msdata:rowOrder": "1",
"@ts": "2020-09-02T01:00:00+03:00",
"@value": "1.5102241",
"@status": "Good"
},
{
"@diffgr:id": "Values3",
"@msdata:rowOrder": "2",
"@ts": "2020-09-02T02:00:00+03:00",
"@value": "1.18518186",
"@status": "Good"
}
],
"Properties": {
"@diffgr:id": "Properties1",
"@msdata:rowOrder": "0",
"@stepped": "0"
}
}
}
}
}
}
