CODEWITHSUNDEEP
jsonxmlliquidazure-logic-apps
PeterS
PeterS

Reputation: 71

XML to JSON Liquid fails in Logic APP on special Characters

enter image description here

Logic App received a XML response message. This message is transformed from XML to JSON with a liquid template,but when special characters are in the XML response the Liquid fails...

<ET_KLANTEN_ALL>
 <item>
  <KUNNR>xxx</KUNNR>
  <NAME1>Strandshop "Allaart"</NAME1>
 </item>
</ET_KLANTEN_ALL>

XML encoded data
            <ET_KLANTEN_ALL>
                <item>
                    <KUNNR>xx</KUNNR>
                    <NAME1>Strandshop &#34;Allaart&#34;</NAME1>
                </item>
            </ET_KLANTEN_ALL>
            <ET_RETURN/>

Liquid "AfnemerNaam": "{{item.AfnemerNaam}}"

Liquid Error in Logic App... IncorrectLiquidTransformOutputType.

I have tried using single quotes

Liquid "AfnemerNaam": '{{item.AfnemerNaam}}'

This works for the above issue but it fails on the bellow situation

XML input bowser display

<ET_KLANTEN_ALL>
 <item>
  <KUNNR>0000071719</KUNNR>
  <NAME1>*STK*Camping 't Strandheem</NAME1>
 </item>
</ET_KLANTEN_ALL>

<ET_KLANTEN_ALL>
                <item>
                    <KUNNR>Y</KUNNR>
                    <NAME1>*STK*Camping &#39;t Strandheem</NAME1>
                </item>
            </ET_KLANTEN_ALL>

Could use some advise on this....

Required output in JSON

{
    "Afnemers": [
       
        {
            "AfnemerNummer": "0000036082",
            "AfnemerNaam": "Strandshop \"Allaart\"",
        }
    ]
}

{
    "Afnemers": [
        {
            "AfnemerNummer": "0000071719",
            "AfnemerNaam": "*STK*Camping 't Strandheem"

        }
    ]
}

Upvotes: 2

Views: 834

Answers (2)

PeterS
PeterS

Reputation: 71

We have managed it using liquid. Please note solution to present "text" correct in the JSON use a single quote. In all other situations use the double quote.

 {% if item.AfnemerNaam contains '"' %}

      "AfnemerNaam": '{{item.AfnemerNaam}}'

 {% else %}

      "AfnemerNaam": "{{item.AfnemerNaam}}"

 {% endif %},

Upvotes: 2

Skin
Skin

Reputation: 11197

Just a suggestion but if you want to avoid using liquid, you can perform a simple conversion to JSON using an expression and then loop through the result and convert it to your new structure with the loop.

Top

Using the above, I loaded your XML (with the two items) into a string variable and then in the next step, used a Parse json step to turn it into an intermediary JSON structure that preserves the special characters, etc. like you want.

The expression above is ...

json(xml(variables('XML')))

The schema of your Parse json step (to make it easier for you) is ...

{
    "properties": {
        "ET_KLANTEN_ALL": {
            "properties": {
                "item": {
                    "items": {
                        "properties": {
                            "KUNNR": {
                                "type": "string"
                            },
                            "NAME1": {
                                "type": "string"
                            }
                        },
                        "required": [
                            "KUNNR",
                            "NAME1"
                        ],
                        "type": "object"
                    },
                    "type": "array"
                }
            },
            "type": "object"
        }
    },
    "type": "object"
}

That will create this JSON ...

{
  "ET_KLANTEN_ALL": {
    "item": [
      {
        "KUNNR": "0000071719",
        "NAME1": "*STK*Camping 't Strandheem"
      },
      {
        "KUNNR": "xxx",
        "NAME1": "Strandshop \"Allaart\""
      }
    ]
  }
}

Then all you need to do is loop through each item and append it to an array variable ...

Bottom Flow

... and finalise the result.

Set Result

This is the end game ...!

End game

This is the json definition of my answer if you want to load it into your own tenant.

{
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "For_each": {
                "actions": {
                    "Append_to_Result": {
                        "inputs": {
                            "name": "Afnemers",
                            "value": {
                                "AfnemerNaam": "@{items('For_each')?['NAME1']}",
                                "AfnemerNummer": "@{items('For_each')?['KUNNR']}"
                            }
                        },
                        "runAfter": {},
                        "type": "AppendToArrayVariable"
                    }
                },
                "foreach": "@body('Parse_JSON')?['ET_KLANTEN_ALL']?['item']",
                "runAfter": {
                    "Initialize_Array": [
                        "Succeeded"
                    ]
                },
                "type": "Foreach"
            },
            "Initialize_Array": {
                "inputs": {
                    "variables": [
                        {
                            "name": "Afnemers",
                            "type": "array"
                        }
                    ]
                },
                "runAfter": {
                    "Initialize_Result": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Initialize_Result": {
                "inputs": {
                    "variables": [
                        {
                            "name": "Result",
                            "type": "object"
                        }
                    ]
                },
                "runAfter": {
                    "Parse_JSON": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Initialize_XML": {
                "inputs": {
                    "variables": [
                        {
                            "name": "XML",
                            "type": "string",
                            "value": "<ET_KLANTEN_ALL>\n <item>\n  <KUNNR>0000071719</KUNNR>\n  <NAME1>*STK*Camping 't Strandheem</NAME1>\n </item>\n <item>\n  <KUNNR>xxx</KUNNR>\n  <NAME1>Strandshop \"Allaart\"</NAME1>\n </item>\n</ET_KLANTEN_ALL>"
                        }
                    ]
                },
                "runAfter": {},
                "type": "InitializeVariable"
            },
            "Parse_JSON": {
                "inputs": {
                    "content": "@json(xml(variables('XML')))",
                    "schema": {
                        "properties": {
                            "ET_KLANTEN_ALL": {
                                "properties": {
                                    "item": {
                                        "items": {
                                            "properties": {
                                                "KUNNR": {
                                                    "type": "string"
                                                },
                                                "NAME1": {
                                                    "type": "string"
                                                }
                                            },
                                            "required": [
                                                "KUNNR",
                                                "NAME1"
                                            ],
                                            "type": "object"
                                        },
                                        "type": "array"
                                    }
                                },
                                "type": "object"
                            }
                        },
                        "type": "object"
                    }
                },
                "runAfter": {
                    "Initialize_XML": [
                        "Succeeded"
                    ]
                },
                "type": "ParseJson"
            },
            "Set_Result": {
                "inputs": {
                    "name": "Result",
                    "value": {
                        "Afnemers": "@variables('Afnemers')"
                    }
                },
                "runAfter": {
                    "For_each": [
                        "Succeeded"
                    ]
                },
                "type": "SetVariable"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {},
        "triggers": {
            "Recurrence": {
                "evaluatedRecurrence": {
                    "frequency": "Month",
                    "interval": 12
                },
                "recurrence": {
                    "frequency": "Month",
                    "interval": 12
                },
                "type": "Recurrence"
            }
        }
    },
    "parameters": {}
}

Upvotes: 2

Related Questions