How can I map SQLXML in Dataweave

Question

I'm struggling to work out how to reference the fields in the response I get from a call to an external API which returns the response in the following format which looks like XML from MSSQL database:

<Depts>
    <Dept DeptCode="1" DeptName="Sales">
        <Employees>
            <Employee EmpCode="1234" EmpRole="Sales" DateUpdated="2017-10-17T10:23:09.950"/>
            <Employee EmpCode="56" EmpRole="Supervisor" DateUpdated="2017-09-17T10:23:09.950"/>
        </Employees>
    </Dept>
    <Dept DeptCode="2" DeptName="HR">
        <Employees>
            <Employee EmpCode="84848" EmpRole="Advisor" DateUpdated="2017-10-17T10:23:09.950"/>
            <Employee EmpCode="12" EmpRole="Director" DateUpdated="2016-10-17T10:23:09.950"/>
        </Employees>
    </Dept>
</Depts>

I need to convert it into json like this:

{
    "Depts": [{
            "DeptCode": "1",
            "DeptName": "Sales",
            "Employees": [{
                    "EmpCode": "1234",
                    "EmpRole": "Sales",
                    "DateUpdated": "2017-10-17T10:23:09.950"
                },
                {
                    "EmpCode": "56",
                    "EmpRole": "Supervisor",
                    "DateUpdated": "2017-09-17T10:23:09.950"
                }
            ]

        },
        {
            "DeptCode": "2",
            "DeptName": "HR",
            "Employees": [{
                    "EmpCode": "84848",
                    "EmpRole": "Advisor",
                    "DateUpdated": "2017-10-17T10:23:09.950"
                },
                {
                    "EmpCode": "12",
                    "EmpRole": "Director",
                    "DateUpdated": "2016-10-17T10:23:09.950"
                }
            ]

        }
    ]
}

How can I do this in Dataweave using the Transform Message component in Anypoint Studio 6.2 and Mule 3.8.3?

Thanks

Anirban Sen Chowdhary Anirban Sen Chowdhary · Accepted Answer · 2017-10-25T19:53:05

If you extract and get XML value in the payload then here is the dataweave code you need to get the expected result:

%dw 1.0
%output application/json
---
{
    Depts:payload.Depts map {
    DeptCode:$.@DeptCode,
    DeptName: $.@DeptName,
    Employees: $.Employees map {
                 EmpCode: $.@EmpCode,
                 EmpRole: $.@EmpRole,
                 DateUpdated: $.@DateUpdated
                }

    }
}

How can I map SQLXML in Dataweave

1 Answers