Introduction:

In this blog, I will discuss the challenges my team faced while creating the process, and since the resources we had at hand were unable to answer our questions, we came up with our own approach to solve the issue.

Since, ICRT uses XQuery, we used XQuery programming for our use.

Note: The scenario we are describing and the situation we faced might differ.

Problem 1:

In an XML note.xml you have the sender (‘from’) and the recipient (‘to’) details. We need to store the information of those whose recipient is only ’Tove1’.

Solution:

CAI method:

While using head and tail for loop, you can always use the decision box, but that would be a little time consuming, as you have to filter one by one and also store at the end.

Approach Taken:

If for loop you are going for head and tail, you can filter for your condition only at the beginning.

Below is an example: 

Remember to use the same filter condition in tail also, to achieve a better result of filter condition. 

If more cases were added like the value of ‘heading’ should be ‘Reminder2’, you can always use  more filter conditions by just adding ‘and’ and the name of the child node in its parent node. You can also use other functions like max and min in the same ‘[]’ square brackets. 

Below are the screenshots:

For more scenarios where you have to search for a value in only the first node, you can use if else condition.

You can use nested if else, nested for loop. For user defined function, it may cause some error while publishing the process.

Problem 2:

You are using a table having more than 10 columns, but you need only some of them, and you need to have different names (key) for the result to show in json.

Solution:

Process Objects:

Process Objects are very useful; in the best use case scenario, you can loop and add them in the same objects to create an array. But what if a new key needs to be added in json? You would again have to add in process object in the process.

But since we can write a XQuery in CAI assignment (select type as any), we can create for loop in the assignment. Also, instead of creating Process Objects you can directly create parent child xml tags in the return field. Below are the screenshots:

Note: While using for loop, you can also use filter condition as we have done in Problem 1.

Problem 3:

There are a bunch of JSON files landing in a folder– you need to process the files and send the processed files to one folder and the unprocessed files to another. 

Solution:

File Parser:

The File Parser is one of the most useful connections for me. The best thing about this connection is that it automatically moves the processed file to the ‘. done’ folder (if processed successfully) or to ‘. error’ folder (if the process fails).

But what if the file getting processed is the same? In this case, the file will be overwritten. Now the best solution here is to separate them in a folder (based on timestamp). See below:

Move to: E:\tempFolder\.done\${date:now:dd-MM-yyyy_HH}\${file:name}

Move if Failure: E:\tempFolder\.error\${date:now:dd-MM-yyyy_HH}\${file:name}

For more info: https://camel.apache.org/components/latest/languages/file-language.html

Problem 4:

There exists a csv file that we need to read and show the output as a JSON.

Solution:

CSV to JSON:

If you use File Parser here, the the process will be an even based and you will not have any URL to call the process; as soon as the even finds the file it will automatically get triggered.

So, we went for a different approach. We wrote XQuery to convert csv to json.

The type of the assignment should be of any.

Below is the code for the same:

let $parsedData := fn:unparsed-text-lines($temp.path)

let $headerLine := $parsedData[1]

let $keys := fn:tokenize($headerLine, ‘,’)

let $listArray := for $item in fn:subsequence($parsedData, 2, fn:count($parsedData))

let $vals := fn:tokenize($item, ‘,’)

let $jsonString:= for $n in 1 to fn:count($vals)

  return concat($keys[$n],’: ‘,$vals[$n])

return string-join($jsonString,”,”)

let $json := <json>

        [ {concat(‘{‘,string-join($listArray,”},{“), “}”)}]

    </json>

return $json/text()

Problem 5:

We have values and the results need to be shown in JSON array objects.

Solution:

Array Objects:

if the node repetition is more than one in the same scale, the output will show the array of objects in JSON format. But if there is no repetition, you will not get array objects, even if you use ‘list of any’.

Instead of this, you can use the below method:

<root xmlns:m=”urn:informatica:ae:xquery:json2xml:meta-data” m:isArray=”true”>

The above namespace will force convert any node in an array of objects.

Note: XQuery is case sensitive.

To try XQuery and check your functions, check out: https://xqueryfiddle.liberty-development.net/nbUY4kv


2 Comments

Reetwika Pandey · April 29, 2020 at 7:33 pm

EXCELLENT

    Sneha Mary Christall · April 30, 2020 at 8:57 am

    Thank you Reetwika!

Leave a Reply

Your email address will not be published. Required fields are marked *