locked
Issue with converting xml to json - single element array RRS feed

  • Question

  • Converting xml to json, i have used default function in logic apps @json(xml(previousstepoutput())). I am facing issue with array format, if there is single item in xml it has been taken as object , if there is more than one item in xml, taken as array. But in my case by default it needs to be taken as array. 

    Eg :

    <?xml version="1.0" encoding="ISO-8859-1"?>
    <Message>
    <date></date>
    <Orders>
    <Order>
    <OrderHeader>
    <OrderNo>12345</OrderNo>
    <DeliveryDate>2019-03-06</DeliveryDate>
    <DeliveryTime>11:35:09</DeliveryTime>
    </OrderHeader>
    <Shipments/>
    <OrderLines >
    <OrderLine>
    <LineID>1</LineID>
    <LineType>1</LineType>
    <ItemNo>ABC123</ItemNo>
    <BatchNo>ABC12345</BatchNo>
    <DeliverQty>1</DeliverQty>
    <AddedLine>0</AddedLine>
    </OrderLine>
    </OrderLines>
    </Order>
    </Orders>
    </Message>

    Actual JSON :

    {
    "Message": {
    "date": "",
    "Orders": {
    "Order": {
    "OrderHeader": {
    "OrderNo": "12345",
    "DeliveryDate": "2019-03-06",
    "DeliveryTime": "11:35:09"
    },
    "Shipments": "",
    "OrderLines": {
    "OrderLine": {
    "LineID": "1",
    "LineType": "1",
    "ItemNo": "ABC123",
    "BatchNo": "ABC12345",
    "DeliverQty": "1",
    "AddedLine": "0"
    }
    }
    }
    }
    }
    }

    Required JSON:
    {
    "Message": {
    "date": "",
    "Orders": [
    {
    "Order": {
    "OrderHeader": {
    "OrderNo": "12345",
    "DeliveryDate": "2019-03-06",
    "DeliveryTime": "11:35:09"
    },
    "Shipments": "",
    "OrderLines":[
                                    {
    "OrderLine": {
    "LineID": "1",
    "LineType": "1",
    "ItemNo": "ABC123",
    "BatchNo": "ABC12345",
    "DeliverQty": "1",
    "AddedLine": "0"
    }
    }
       ]
    }
      ]
    }
    }
    }

    I have verified few suggestions using azure functions, but in that case it says like we need to modify the xml with namespace and add attribute json:array = 'true' in required node. Is it possible to convert with array without modifying the messages?

    Kindly provide your valuable suggestions.


    Vinoth

    Wednesday, September 18, 2019 1:37 PM

Answers

  • Hi Ben,

    Thanks for the reply. I have fixed the issue by modifying the xml with adding namespace and attribute to force the array.

    <Message xmlns:json="http://james.newtonking.com/projects/json">

    <Orders json:Array="true">

    </Orders>

    </Message>


    Vinoth

    • Marked as answer by Vinoth n Friday, September 20, 2019 9:24 AM
    Friday, September 20, 2019 9:24 AM

All replies

  • Your case would be an example of a great time to use a Liquid template to get the brackets into the json. 

    This example page shows how to do it https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-enterprise-integration-liquid-transform for Transform JSON to JSON. 

    I did not have time to create the liquid template for you but the basic approach is shown on the link. 

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline

    Thursday, September 19, 2019 7:34 PM
  • Hi Ben,

    Thanks for the reply. I have fixed the issue by modifying the xml with adding namespace and attribute to force the array.

    <Message xmlns:json="http://james.newtonking.com/projects/json">

    <Orders json:Array="true">

    </Orders>

    </Message>


    Vinoth

    • Marked as answer by Vinoth n Friday, September 20, 2019 9:24 AM
    Friday, September 20, 2019 9:24 AM