[XML][1] [Converted JSON from XML][2]
I am trying to read a XML file & converting it to a JSON file. The JSON file contains multiple worker records and I want to iterate through all the worker records and read the specific value.
Sample of converted JSON
{
"?xml": {
"@version": "1.0",
"@encoding": "UTF-8"
},
"ws:Worker_Sync": {
"@xmlns:ws": "urn:com.workday/workersync",
"@xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance",
"ws:Header": {
"ws:Version": "25.0",
"ws:Prior_Entry_Time": "2024-05-06T23:04:49.766-07:00",
"ws:Current_Entry_Time": "2024-05-07T07:01:49.768-07:00",
"ws:Prior_Effective_Time": "2024-11-02T00:00:00.000-07:00",
"ws:Current_Effective_Time": "2024-11-03T00:00:00.000-07:00",
"ws:Full_File": "false",
"ws:Document_Retention_Policy": "90",
"ws:Worker_Count": "2"
},
"ws:Worker": [
{
"ws:Summary": {
"ws:Employee_ID": "18325",
"ws:Name": "test"
},
"ws:Eligibility": {
"@ws:PriorValue": "",
"#text": "true"
},
"ws:Personal": {
"ws:Name_Data": {
"ws:First_Name": {
"@ws:PriorValue": "",
"#text": "test"
},
"ws:Last_Name": {
"@ws:PriorValue": "",
"#text": "test"
}
}
},
"ws:Status": {
"ws:Staffing_Event": {
"@ws:PriorValue": "",
"#text": "HIR"
},
"ws:Staffing_Event_Date": {
"@ws:PriorValue": "",
"#text": "2024-05-13"
},
"ws:Employee_Status": {
"@ws:PriorValue": "",
"#text": "Active"
},
"ws:Active": {
"@ws:PriorValue": "",
"#text": "true"
},
"ws:Active_Status_Date": {
"@ws:PriorValue": "",
"#text": "2024-05-13"
},
"ws:Hire_Date": {
"@ws:PriorValue": "",
"#text": "2024-05-13"
},
"ws:Hire_Reason": {
"@ws:PriorValue": "",
"#text": "Hire_Employee_Hire_Employee_New_Hire"
},
"ws:End_Employment_Date": {
"@ws:PriorValue": "",
"#text": "2024-08-02"
},
"ws:Rehire": {
"@ws:PriorValue": "",
"#text": "false"
}
},
"ws:Position": {
"ws:Operation": "ADD",
"ws:Effective_Date": {
"@ws:PriorValue": "",
"#text": "2024-05-13"
},
"ws:Position_Title": {
"@ws:PriorValue": "",
"#text": "Intern"
},
"ws:Business_Title": {
"@ws:PriorValue": "",
"#text": "Co-Operative Student"
},
"ws:Scheduled_Weekly_Hours": {
"@ws:PriorValue": "",
"#text": "36.25"
},
"ws:Organization_Data": [
{
"ws:Operation": "ADD",
"ws:Organization": {
"@ws:PriorValue": "",
"#text": "953"
}
},
{
"ws:Operation": "ADD",
"ws:Organization": {
"@ws:PriorValue": "",
"#text": "test"
}
}
],
"ws:Business_Site_Name": {
"@ws:PriorValue": "",
"#text": "test"
},
"ws:Supervisor": {
"ws:Operation": "ADD",
"ws:Supervisor_Name": {
"@ws:PriorValue": "",
"#text": "test"
}
}
},
"ws:Additional_Information": {
"ws:Mobile_Number": {
"@ws:PriorValue": "",
"#text": "test"
},
"ws:Legal_Name": {
"@ws:PriorValue": "",
"#text": "test"
},
"ws:Preferred_Name": {
"@ws:PriorValue": "",
"#text": "test"
},
"ws:SUPERVISORY_ORG": {
"@ws:PriorValue": "",
"#text": "test"
},
"ws:COST_CENTRE": {
"@ws:PriorValue": "",
"#text": "test"
},
"ws:USERNAME": {
"@ws:PriorValue": "",
"#text": "stest"
},
"ws:Annual_Leave_Balance": {
"@ws:PriorValue": "",
"#text": "5"
},
"ws:Company": {
"@ws:PriorValue": "",
"#text": "test"
},
"ws:Job_Profile": {
"@ws:PriorValue": "",
"#text": "Intern"
}
}
}
]
}
}
I am trying to iterate through the ws:worker array and fetch the value from {"ws:Worker" --> "ws:Status" --> "ws:Staffing_Event" --> "#text"
When I run the logic app, I get the following error: The execution of template action 'For_each' failed: the result of the evaluation of 'foreach' expression '@body('Parse_JSON_FIle')?['ws:Worker']' is of type 'Null'. The result must be a valid array.
Please can you share your insights. [1]: https://i.sstatic.net/wi0QD03Y.png [2]: https://i.sstatic.net/3qsr4ElD.png
You are getting this error –
the result of the evaluation of 'foreach' expression '@body('Parse_JSON_FIle')?['ws:Worker']' is of type 'Null'
– because the ws:Worker array is inside the ws:Worker_Sync object, so your For each action should loop over @body('Parse_JSON_FIle')?['ws:Worker_Sync']?['ws:Worker'].
Then, inside your loop you'll be able to get the @item()?['ws:Status']?['ws:Staffing_Event']?['#text'] value.
