Search code examples
muledataweavemulesoftanypoint-studiomule4

Convert file to JSON in Mule 4

I extracted a file that comes in a zip and looks similar to this:

Payload =

<?xml version="1.0" encoding="UTF-8"?>
    <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><dimension ref="A1"/><sheetViews><sheetView workbookViewId="0" tabSelected="true"/></sheetViews><sheetFormatPr defaultRowHeight="15.0"/><sheetData>
    <row r="1">
    <c r="A1" t="inlineStr"><is><t>First</t></is></c><c r="B1" t="inlineStr"><is><t>Middle</t></is></c><c r="C1" t="inlineStr"><is><t>Last</t></is></c><c r="D1" t="inlineStr"><is><t>SSN</t></is></c><c r="E1" t="inlineStr"><is><t>Street</t></is></c><c r="F1" t="inlineStr"><is><t>MailingState</t></is></c><c r="G1" t="inlineStr"><is><t>Code</t></is></c><c r="H1" t="inlineStr"><is><t>MailingCountry</t></is></c><c r="I1" t="inlineStr"><is><t>Birthdate</t></is></c><c r="J1" t="inlineStr"><is><t>name</t></is></c><c r="K1" 
    <row r="2">
    <c r="A2" t="inlineStr"><is><t>William</t></is></c><c r="A5" t="inlineStr"><is><t></t></is></c><c r="B2" t="inlineStr"><is><t>William</t></is></c><c r="D2" t="inlineStr"><is><t>123456798</t></is></c><c r="E2" t="inlineStr"><is><t>Test</t></is></c><c r="F2" t="inlineStr"><is><t>XX</t></is></c><c r="G2" t="inlineStr"><is><t>12345</t></is></c><c r="H2" t="inlineStr"><is><t></t></is></c><c r="I2" t="inlineStr"><is><t>1992-13-11T04:00:00</t></is></c><c r="J2"

mediaType = application/java; charset=UTF-8

How can I convert that file to something that I can manipulate better, for example JSON.

Mule Runtime version 4.4.0 EE

Solution

  • Use the read() function to parse it as the XML file the content appears to be, then transform to whatever you want.

    Example DataWeave transformation:

    output application/json
---
read(payload, "application/xml")

    Note that the payload seems truncated. If it is really XML above script will work. Also I recommend to perform any transformation before outputting to JSON, to avoid performance issues. If you are doing more transformations later in the flow is better to output to application/java and convert to JSON in the last transformation.