XML to JSON - Liquid Mapping - 2 level XML repeating records - Logic Apps

Question

Looking for your feedback on my liquid template mapping script, I cannot parse 2nd level order within in product. I am new to the syntax trying to parse as per the expected output. Looking for your suggestions on how to fix, and handle second-level repeating records.

Input XML

<Products>
    <Product>
        <productname>iPhone</productname>
        <productid>EL123</productid>
        <productcategory>Electronics</productcategory>
        <order>
            <sold_to_customer_number>33</sold_to_customer_number>
            <ship_to_customer_number>1</ship_to_customer_number>
        </order>
    </Product>
    <Product>
        <productname>OnePlus</productname>
        <productid>EL124</productid>
        <productcategory>Electronics</productcategory>
        <order>
            <sold_to_customer_number>2</sold_to_customer_number>
            <ship_to_customer_number>2</ship_to_customer_number>
        </order>
        <order>
            <sold_to_customer_number>3</sold_to_customer_number>
            <ship_to_customer_number>3</ship_to_customer_number>
        </order>
    </Product>
</Products>

Expected JSON

{
    "Products": [
        {
            "productname": "iPhone",
            "productid": "EL123",
            "productcategory": "Electronics",
            "order": [
                {
                    "sold_to_customer_number": "33",
                    "ship_to_customer_number": "1"
                }
            ]
        },
        {
            "productname": "OnePlus",
            "productid": "EL124",
            "productcategory": "Electronics",
            "order": [
                {
                    "sold_to_customer_number": "2",
                    "ship_to_customer_number": "2"
                },
                {
                    "sold_to_customer_number": "3",
                    "ship_to_customer_number": "3"
                }
            ]
        }
    ]
}

Liquid Tempalte - Issue with Order to parse

{
         "Products":[
             {% for item in content.Products %}         
                { 
                     "productname":"{{item.productname}}",
                     "productid":"{{item.productid}}",
                     "productcategory":"{{item.productcategory}}",
                     "order":[
                             {% for item in content.Products.Product %}         
                                { 
                                     "sold_to_customer_number":"{{item.order.sold_to_customer_number}}",
                                      "ship_to_customer_number":"{{item.order.ship_to_customer_number}}"
                                     
                                },
                             {% endfor %}
                     ]
                },
            {% endfor %}
         
         ]
}

Thanks SMSVikasK

I’m not across the exact problem but using `item` as the inner and outer looping variable is surely not allowed. Also, isn’t your inner loop meant to loop over the `order` array for the current item being processed? If you really struggle, the advanced data operations XML to Json operation is really easy and will do what you want. — Skin, Apr 16 '23 at 12:11
The default xml to json expression is having problem only when we have one order, it will return as single object not array of order object. Which xml to json operation you are reffering to? I am trying to use the liquid template for xml to json. Thanks for your input. — SMSVikasK, Apr 16 '23 at 12:23
I’m referring to this operation … https://learn.microsoft.com/en-au/connectors/advanceddataoperatio/#xml-to-json … you’re unlikely to be interested in it but it’s very powerful AND the additional operations are worth the money if you have a need. It’s really not expensive. It’s worth a trial if you get stuck. — Skin, Apr 16 '23 at 12:26
Thank you, I will indeed check it out. As of now will try to achieve an out-of-the-box solution. — SMSVikasK, Apr 16 '23 at 12:41

score 1 · Accepted Answer · answered Apr 17 '23 at 00:39

1

If you can't solve your issue (I've tried and I can't either) using Liquid then I'd look at using the Advanced Data Operations connector, it will do it for you.

The only thing it doesn't do is wrap the resulting primary array inside a Products property. The Compose step at the end does that though.

You'll also notice that I've changed the names of the destination properties slightly to show the distinct difference between the source and target.

This basic flow demonstrates how ...

This is the resulting JSON (noting that it appears to have ordered the properties alphabetically but that shouldn't be an issue) ...

answered Apr 17 '23 at 00:39

Skin

9,085
2
13
29

Thanks for your input. It helps. I also tried something to have a working solution to continue to progress. – SMSVikasK Apr 17 '23 at 16:20
what you mentioned is right, I tried all options and ended up as per below to sort my issue, and it's working for me as per expected.
- XML to JSON conversion using logic app expressions
- JSON to JSON liquid mapping transformation
- JSON would have a repeating array or single object mix of both as per the above sample ie Products
- I am trying to loop for both array and object with if the condition
- below is a sample solution for
– SMSVikasK Apr 17 '23 at 16:29
my working solution as a workaround solution, sample code. { "ETA_STATUS_CODE": "{{content.ETA_STATUS_CODE}}", {% if content.ORDER.size >= 1 %} "ORDER":[ {% for ord in content.ORDER %} { "SOLD_TO_CUSTOMER_NUMBER": "{{ ord.SOLD_TO_CUSTOMER_NUMBER }}", }, {% endfor %} ], {% else %} "ORDER":[ { "SOLD_TO_CUSTOMER_NUMBER": "{{ content.ORDER.SOLD_TO_CUSTOMER_NUMBER }}", } ] {% endif %} } – SMSVikasK Apr 17 '23 at 16:30

SMSVikasK · Answer 2 · 2023-04-17T16:40:22.227

Thanks for your input. It helps. I also tried something to have a working solution to continue to progress.

what you mentioned is right, I tried all options and ended up as per below to sort my issue, and it's working for me as per expected.

XML to JSON conversion using logic app expressions.
JSON to JSON liquid mapping transformation.
JSON would have a repeating array or single object mix of both as per the above sample ie Products.
I am trying to loop for both array and object with the if condition.
below is a sample solution for similar objects.

the below steps/solution is working as expected.

{
 "ETA_STATUS_CODE": "{{content.ETA_STATUS_CODE}}",
                        
    {% if content.ORDER.size >= 1 %}
       "ORDER":[
           {% for ord in content.ORDER %}           
                { 
                  "SOLD_TO_CUSTOMER_NUMBER": "{{ ord.SOLD_TO_CUSTOMER_NUMBER }}",
                },
            {% endfor %}
            ],
    {% else %}
        "ORDER":[                            
            { 
                "SOLD_TO_CUSTOMER_NUMBER": "{{ content.ORDER.SOLD_TO_CUSTOMER_NUMBER }}",
                                                             
            }
        ]                            
    {% endif %}
}

XML to JSON - Liquid Mapping - 2 level XML repeating records - Logic Apps

2 Answers2