topic Re: xml and json Transformaion in Workato Pros Discussion Board

xml and json Transformaion

amit_mohanty88 — Mon, 03 Nov 2025 12:38:26 GMT

I want to achieve below functionality in Workato without Ruby / Java Script / Python Scripting as scripts are not allowed by our customer .Also JSON Transformations by Workato Action not available in my plan .Without these is there a way to acheive below ?

1) Sort a input json alphabetically ascedending

2) Remove any specific fields from a input json and return updated json

3)Convert any given input json to xml and any given input xml to json

4) Compare 2 json elment structure and return any field mismatch between the 2 json which is present in 1 json not the other json

Re: xml and json Transformaion

rajeshjanapati — Mon, 03 Nov 2025 17:03:05 GMT

HI @amit_mohanty88,

Have you tried using SQL Collections by Workato?? like below

Thanks,

J Rajesh Kumar.

Re: xml and json Transformaion

amit_mohanty88 — Tue, 04 Nov 2025 09:21:18 GMT

Hi @rajeshjanapati

Let me try and get back . Thank you for your suggestion

Re: xml and json Transformaion

shivakumara — Tue, 04 Nov 2025 16:15:29 GMT

Hi @rajeshjanapati

We can combine steps 4 and 5, as the sorting part is not being used in any subsequent steps.
For XML ↔ JSON conversion, we can directly feed the data into Parse XML and Parse JSON action/ step as needed.

This approach will help optimize the overall solution. Please free to add your thoughts.

Thanks and Regards,
Shivakumara K A

Re: xml and json Transformaion

amit_mohanty88 — Wed, 05 Nov 2025 04:29:30 GMT

Hi @rajeshjanapati @shivakumara ,

Thank you for your suggestion. 1 query i got like in step 2 and step 9 parse json doument for that we need to give sample json . But this recipe should be able to process any json not only any specific json so what can we give sample json in parse json doument in step 2 and step 9 ?

Regards,

Amit Mohanty

Re: xml and json Transformaion

rajeshjanapati — Wed, 05 Nov 2025 06:33:17 GMT

Hi @amit_mohanty88,

It can be possible with any type of json, with respect to that we need to provide the sample json in the Parse Json step.

Please find the below sample json data which i have used in step_2 and step_9 and output from step_11 and query:
step_2 json:

{ "employees": [ {"firstname": "John", "lastname": "Doe", "city": "San Jose", "state": "CA"}, {"firstname": "Apple", "lastname": "fruit", "city": "1", "state": "def@gmail.com"}, {"firstname": "Monkey", "lastname": "animal", "city": "2", "state": "ghi@gmail.com"} ] }

Step_9 json:

{ "employees": [ {"firstname": "John", "lastname": "Doe", "city": "San Jose", "state": "CA"} ] }

Step_11 output:

{ "rows": [ { "firstname": "Apple", "lastname": "fruit", "city": "1", "state": "def@gmail.com" }, { "firstname": "Monkey", "lastname": "animal", "city": "2", "state": "ghi@gmail.com" } ] }

Query:

Thanks & Regards,

J Rajesh Kumar.

Re: xml and json Transformaion

rajeshjanapati — Wed, 05 Nov 2025 06:50:27 GMT

Hi @shivakumara ,

Thank you for your valuable suggestion and the additional step.

Here’s my thought process:

As per @amit_mohanty88's question, the goal was to verify whether each scenario is possible or not. Hence, I designed the flow in such a way to demonstrate all the possibilities.
Agreed with your point regarding optimization.

Thanks again for your inputs!

Thanks & Regards,

J Rajesh Kumar.

Re: xml and json Transformaion

amit_mohanty88 — Wed, 05 Nov 2025 06:57:32 GMT

Hi @rajeshjanapati ,

What if to the recipe we got input json as below . Here in your step 2 & step 9 you expect json with employee as input .So the parse json document wont be able to parse the below json right with your above sample json document ?

{

"glossary": {

"title": "example glossary",

"GlossDiv": {

"title": "S",

"GlossList": {

"GlossEntry": {

"ID": "SGML",

"SortAs": "SGML",

"GlossTerm": "Standard Generalized Markup Language",

"Acronym": "SGML",

"Abbrev": "ISO 8879:1986",

"GlossDef": {

"para": "A meta-markup language, used to create markup languages such as DocBook.",

"GlossSeeAlso": ["GML", "XML"]

"GlossSee": "markup"

}

Re: xml and json Transformaion

rajeshjanapati — Wed, 05 Nov 2025 08:06:36 GMT

@amit_mohanty88, Based on my analysis and understanding, if you want to execute a query in Workato, the input must be in a table-like structure in a collection.

Suggestions:

My suggestion is that for scenarios where native Workato actions cannot handle the requirement, we should leverage scripting, that’s exactly why Workato introduced this capability. You can also communicate to the customer or business team that this particular scenario cannot be implemented without scripting, and the script involved is minimal, simple, and easy to understand, especially when it comes to data comparison.
If the team is still not comfortable using scripting (Python), an alternative approach would be to convert the JSON into CSV or send the data in CSV File, can read from any source system or store each required JSON field into a data table. Then, using SQL Transformations by Workato, you can easily compare both CSV files (file_1 and file_2) through a single query step.

In my view, it’s best to communicate this clearly to the team and proceed with the scripting approach, as it’s the simplest and most efficient way to handle this scenario.

Hope you got my point?

@shivakumara, If possible, will you please add any suggestions to the problem?

Re: xml and json Transformaion

shivakumara — Wed, 05 Nov 2025 09:48:08 GMT

Hi @amit_mohanty88 ,

Here is my answer for the asked question:
the Parse JSON action cannot parse the given JSON without a defined schema?
Answer: The sample document is essential because it defines the schema structure required to parse incoming data. The sample data space is used to establish this schema, which will then be considered when forming the output for future steps.

Please ensure you have clarity on the schema structure from the requirements. Once that is confirmed, any one of us can assist you further.

Thanks and Regards,
Shivakumara K A

Re: xml and json Transformaion

amit_mohanty88 — Wed, 05 Nov 2025 11:57:41 GMT

Hi @shivakumara

Yes got it . Since we are making the recipe as an API to sort any incoming json so Parse Json Document Action can not be used in this case . Wanted to know if there are any other action using which we can sort any incoming json without ruby/python/javascript snippets/Json Transformation by Workato ?