'Convert JSON to XML in Logic Apps
I have parsed a JSON string in Logic Apps using the parse json connector. How do I put the output from that step into an xml file? One suggestion I've seen is to use "when a http request is received", but this is a trigger and I need an action. On user voice there is a request for a parse xml function here . I need a workaround. Another problem I'm having is that parse json is returning an object, but I think I need an array.
This is the schema in my parse json connector. After parse json I'm trying to initialise a variable. It's not accepting a type of string.
<code>
{
"properties": {
"PR_ADD1": {
"type": "string"
},
"PR_ADD2": {
"type": "string"
},
"PR_ADD3": {
"type": "string"
},
"RLS_GROUP": {
"type": "string"
}
},
"title": "The Root Schema",
"type": "object"
}
</code>
How do I initialize the first variable as string with the output from my
previous parse json step? In [this example]
(https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-perform-
data-operations#parse-json-action) there is only one record. My parse
json step has lots of records.
Here is some sample JSON
<code>
{
"properties": {
"PR_ADD1": {
"type": "string"
},
"PR_ADD2": {
"type": "string"
},
"PR_ADD3": {
"type": "string"
},
"PR_ZONEC": {
"type": "string"
},
"RLS_GROUP": {
"type": "string"
}
},
"title": "The Root Schema",
"type": "object"
}
</code>
Here is my Logic APP
{
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"actions": {
"Compose_2": {
"inputs": "@length(variables('IntermediateArray'))",
"runAfter": {
"Scope": [
"Succeeded"
],
"Set_variable": [
"Succeeded",
"Skipped"
]
},
"type": "compose"
},
"Compose_From_HTTP2": {
"inputs": "@body('HTTP_2')",
"runAfter": {
"HTTP_2": [
"Succeeded"
]
},
"type": "compose"
},
"HTTP_2": {
"inputs": {
"body": {
"FORMAT": "payload",
"FROM": 0,
"GRIDID": "PROP",
"GRIDVIEW": "1",
"HITS": 100,
"ORDERBY": "PR_DATESOLD",
"PROFILE": [
{
"PR_NAME": "G*",
"PR_USER1": "GENERATED"
}
],
"sessionID": "@body('Parse_JSON3')['sessionID']"
},
"headers": {
"Accept": "text/json",
"Content-Type": "text/json"
},
"method": "POST",
"uri": "@variables('uri_DefGrid')"
},
"runAfter": {
"Parse_JSON3": [
"Succeeded"
]
},
"type": "Http"
},
"HTTP_Logout": {
"inputs": {
"body": {
"method": "logout",
"sessionID": "@body('Parse_JSON3')['sessionID']"
},
"headers": {
"Accept": "text/json",
"Content-Type": "text/json"
},
"method": "POST",
"uri": "@variables('uri_logout')"
},
"runAfter": {
"Initialize_uri_logout": [
"Succeeded"
]
},
"type": "Http"
},
"Initialize_Header": {
"inputs": {
"variables": [
{
"name": "Header",
"type": "string",
"value": "{\"Accept\":\"text/json\",\"Content-Type\":\"text/json\"}"
}
]
},
"runAfter": {
"Initialize_body_login": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_body_login": {
"inputs": {
"variables": [
{
"name": "body_login",
"type": "string",
"value": "json(@{triggerBody()})"
}
]
},
"runAfter": {},
"type": "InitializeVariable"
},
"Initialize_intermediateArray": {
"inputs": {
"variables": [
{
"name": "intermediateArray",
"type": "array"
}
]
},
"runAfter": {
"Compose_From_HTTP2": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_sharepointLibraryPath": {
"inputs": {
"variables": [
{
"name": "sharepointLibraryPath",
"type": "string",
"value": "https://qqqonline.sharepoint.com/teams-and-projects/dev/hhh"
}
]
},
"runAfter": {
"Compose_2": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_uri_DefGrid": {
"inputs": {
"variables": [
{
"name": "uri_DefGrid",
"type": "string",
"value": "https://mh-uat.ttt-app.uk:443/qqq_uat/wrd/run/pppMHAPI.GRIDGET"
}
]
},
"runAfter": {
"Initialize_uri_login": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_uri_login": {
"inputs": {
"variables": [
{
"name": "uri_login",
"type": "string",
"value": "https://mh-uat.ttt-app.uk:443/qqq_uat/wrd/run/pppJSONSERVICE.LOGIN"
}
]
},
"runAfter": {
"Initialize_Header": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_uri_logout": {
"inputs": {
"variables": [
{
"name": "uri_logout",
"type": "string",
"value": "https://mh-uat.ttt-app.uk:443/qqq_uat/wrd/run/pppJSONSERVICE.LOGOUT"
}
]
},
"runAfter": {
"Initialize_sharepointLibraryPath": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_variable": {
"inputs": {
"variables": [
{
"name": "initializeArray",
"type": "array",
"value": "@outputs('Compose_From_HTTP2')"
}
]
},
"runAfter": {
"Initialize_intermediateArray": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Parse_JSON3": {
"inputs": {
"content": "@triggerBody()",
"schema": {
"properties": {
"RLS_WHERE": {
"$id": "#/properties/RLS_WHERE",
"type": "string"
},
"contact": {
"type": "string"
},
"error": {
"type": "string"
},
"errorId": {
"type": "string"
},
"fullName": {
"type": "string"
},
"labellanguage": {
"type": "string"
},
"language": {
"type": "string"
},
"message": {
"type": "string"
},
"params": {
"properties": {
"WOPARTSOPT": {
"type": "string"
}
},
"required": [
"WOPARTSOPT"
],
"title": "The Params Schema",
"type": "object"
},
"role": {
"type": "string"
},
"sessionID": {
"type": "string"
},
"success": {
"type": "string"
},
"userEmail": {
"$id": "#/properties/userEmail",
"type": "string"
}
},
"required": [
"success",
"message",
"sessionID",
"language",
"labellanguage",
"error",
"errorId",
"fullName",
"role",
"contact",
"RLS_WHERE",
"userEmail",
"params"
],
"title": "The Root Schema",
"type": "object"
}
},
"runAfter": {
"Initialize_uri_DefGrid": [
"Succeeded"
]
},
"type": "ParseJson"
},
"Scope": {
"actions": {
"Scope_2": {
"actions": {
"Compose": {
"inputs": "@array(outputs('Compose_From_HTTP2'))",
"runAfter": {},
"type": "compose"
},
"Set_variable_2": {
"inputs": {
"name": "intermediateArray",
"value": "@outputs('Compose')"
},
"runAfter": {
"Compose": [
"Succeeded"
]
},
"type": "SetVariable"
}
},
"runAfter": {},
"type": "Scope"
}
},
"runAfter": {
"Initialize_variable": [
"Failed"
]
},
"type": "Scope"
},
"Set_variable": {
"inputs": {
"name": "intermediateArray",
"value": "@outputs('Compose_From_HTTP2')"
},
"runAfter": {
"Initialize_variable": [
"Succeeded"
]
},
"type": "SetVariable"
}
},
"contentVersion": "1.0.0.0",
"outputs": {},
"parameters": {},
"triggers": {
"HTTP": {
"inputs": {
"body": {
"method": "login",
"password": "qqq_API",
"username": "qqq_API"
},
"headers": {
"Accept": "text/json",
"Content-Type": "text/json"
},
"method": "POST",
"uri": "https://mh-uat.ttt-app.uk:443/qqq_uat/wrd/run/pppJSONSERVICE.LOGIN"
},
"recurrence": {
"frequency": "Minute",
"interval": 4
},
"type": "Http"
}
}
},
"parameters": {}
}
The parse json screenshot showed the top of the schema. Here is the bottom.
"RLS_GROUP": {
"type": "string"
}
},
"title": "The Root Schema",
"type": "object"
}
When I generate the schema from sample output in parse json I get error messages like this. I think that when integer fields have not value they are coming back with two double quotes to denote empty rather than null. Also, I think the schema is basing itself on the first record which could have data. Subsequent records may not. Conversely the first record may not have data for a particular field, but subsequent records will have data for that field.
"message": "Invalid type. Expected Integer but got String.",
"lineNumber": 0,
"linePosition": 0,
"path": "Payload[1].PR_RENT",
"value": "",
"schemaId": "#/properties/Payload/items/properties/PR_RENT",
"errorType": "type",
"childErrors": []
},
{
"message": "Invalid type. Expected String but got Integer.",
"lineNumber": 0,
"linePosition": 0,
"path": "Payload[3].PR_ID",
"value": 1363,
"schemaId": "#/properties/Payload/items/properties/PR_ID",
"errorType": "type",
"childErrors": []
},
{
"message": "Invalid type. Expected String but got Integer.",
"lineNumber": 0,
"linePosition": 0,
"path": "Payload[3].PR_USER8",
"value": 0,
"schemaId": "#/properties/Payload/items/properties/PR_USER8",
"errorType": "type",
"childErrors": []
}
Solution 1:[1]
1. For your first question, we can use xml() method in logic app to do the convert. As I don't know your json data(you mentioned it is a json string in your question), so I assuming that your data is like the data below:
[
{
"name": "Mike",
"city": "ABC"
},
{
"name": "Tom",
"city": "DEF"
}
]
Then we can convert it by following the steps in the screenshot below:
The expression in the screenshot is:
xml(json(concat('{"root":{"person":', variables('test'), '}}')))
After that, we can get the xml data at last:
<root><person><name>Mike</name><city>ABC</city></person><person><name>Tom</name><city>DEF</city></person></root>
2. For your second question about return object or array, it depends on the data you input to the "Parse JSON" action.
If you input the data which is array shown as the screenshot below
We will get a array return from the "Parse JSON" action
If you input the data which is object shown as the screenshot below
We will get an object return from the "Parse JSON" action
Hope it would be helpful to your problems~
Update:
According to your comments, you want to choose some of the fields from the "Parse JSON" and then convert it to xml. I post an example below for your reference:
I assume that your data is like this?
{
"PR_ADD1": "aaa",
"PR_ADD2": "bbb",
"PR_ADD3": "ccc",
"PR_ZONEC": "ddd",
"RLS_GROUP": "eee"
}
And in after parse json, I assume you don't want the field "PR_ZONEC" and keep other four fields, and convert them to xml. So please refer to my logic app:
I do the "Parse JSON" action.
Then, use "Compose" action, and in "Compose" action I remove the field "PR_ZONEC", keep the other four fields.
At last, convert it to xml.
The expression is
xml(json(concat('{"root":', string(outputs('Compose')), '}')))
- After these steps, I got the result I want.
The whole of the xml is
<root>
<PR_ADD1>aaa</PR_ADD1>
<PR_ADD2>bbb</PR_ADD2>
<PR_ADD3>ccc</PR_ADD3>
<RLS_GROUP>eee</RLS_GROUP>
</root>
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |