topic Re: XML with key as static Value in tfileoutputMSXML in Talend Studio

XML with key as static Value in tfileoutputMSXML

Anonymous — Tue, 20 Feb 2018 10:44:04 GMT

I've one input file like below

ID,MID,NAME
1,0,A

And want to convert it into xml using tfileoutputMSxml. Output should be like below

Am not able to set "<Employee key="ID">1</Employee>" kind of tags in component. Can anyone help how to set this in component ..?

Re: XML with key as static Value in tfileoutputMSXML

Anonymous — Tue, 20 Feb 2018 12:31:47 GMT

Is it possible using component .................?

Re: XML with key as static Value in tfileoutputMSXML

Anonymous — Tue, 20 Feb 2018 16:05:13 GMT

Hi @kate, take a look at this thread (https://community.talend.com/t5/Design-and-Development/Create-an-XML-with-complex-type/m-p/51014#M16636). I believe the links and tips I provided should be of help to you here.

Re: XML with key as static Value in tfileoutputMSXML

Anonymous — Tue, 20 Feb 2018 16:12:41 GMT

Hi @rhall, thanks for sharing. But I dont think so that will fit into my issue.

How can I achieve this using tfileoutputMSxml component's editor...?

Re: XML with key as static Value in tfileoutputMSXML

Anonymous — Tue, 20 Feb 2018 16:25:16 GMT

It is not too different from what I pointed you towards. Here is the Talend documentation for this component (https://help.talend.com/reader/mjoDghHoMPI0yuyZ83a13Q/pi3qmrjr1dUoHVrQhYoW8Q). You will need to either click the green plus symbol to add sub-elements or attributes, or drag your input columns onto (what starts off as the "rootTag") and then add them as either attributes (for your "Key") or sub-elements. You will also need to set your loop status on your "Employees" element in the "Node Status" column.

This is actually very much like the example I gave a link to.

Re: XML with key as static Value in tfileoutputMSXML

Anonymous — Tue, 20 Feb 2018 16:26:13 GMT

Try this link.....

https://help.talend.com/reader/mjoDghHoMPI0yuyZ83a13Q/pi3qmrjr1dUoHVrQhYoW8Q

Re: XML with key as static Value in tfileoutputMSXML

Anonymous — Tue, 20 Feb 2018 17:42:48 GMT

Can you please share one example within screen shot..? Because for creating key it gives only one static value and no any other option left to put input column value once key set as static value.

Re: XML with key as static Value in tfileoutputMSXML

Anonymous — Tue, 20 Feb 2018 22:16:36 GMT

Ok first of all you have to turn the dataset and provide records with key value pairs.

ID; 1

MID; 0

NAME; A

and so on....

If the columns are fixed - I would suggest doing that with a tMap and one normal output + 2 addition joined outputs.

Next: you do not need tFileOutputMSXML. The normal tFileOutputXML ist quite enough. Here you can use the internal mapper to define the XML output.

Re: XML with key as static Value in tfileoutputMSXML

Anonymous — Tue, 20 Feb 2018 23:29:09 GMT

@lli's post helped me understand where your problem is. Your issue is not with configuring the XML component, it is reshaping your data to map to your XML component. I have just knocked up a job to do this quickly. I am basing this on the fact that you stated your data looks like this.....

ID,MID,NAME
1,0,A

If your data changes (to include more columns or more rows) you will need to tweak this to work. But the theory is sound. See a screenshot of my job below....

The tFixedFlowInput represents your file component. The rest will be as you need them. @lli was correct, you do not need to use the tFileOutputMSXML, but I have used it as you specifically asked to use it.

The first tMap (tMap_1) is used to combine your data into header row and value pairs and then to join all of those pairs into one long String. This is done so that the tNormalize component can be used to turn your 1 row into 3 rows (one per column). I have included a header row in my tFixedFlowInput, you will need to read yours in from the file. The tMap looks like below....

The tMap variable code is shown below with the "Merged_Row" expression....

Var.ID==null ? row1.ID : Var.ID + ";" + row1.ID

MID

Var.MID==null ? row1.MID : Var.MID + ";" + row1.MID

Name

Var.Name==null ? row1.Name : Var.Name + ";" + row1.Name

Merged_Row

Var.ID +","+ Var.MID +","+Var.Name

The tAggregateRow is imply used to return the last row only from the tMap. This is done by NOT setting a group by value and setting the Merged_Row function to "Last". That is it.

The tNormalize is configured by selecting the "Column to normalize" as "Merged_Row" and the separator as ",". That is all you need. The data will be converted to 3 rows (one for each column) of key/value pairs. We split the key and value for each column in the next tMap.

The tMap variable expressions are shown below....

Key

row2.Merged_Row.substring(0, row2.Merged_Row.indexOf(';'))

Value

row2.Merged_Row.substring(row2.Merged_Row.indexOf(';')+1)

The data is now in 3 rows with 2 columns (key and value). As you need it. The next component is the tFileOutputMSXML. The configuration of this is shown below....

When this is run, it produces an XML file that looks like this....

<?xml version="1.0" encoding="ISO-8859-15"?>
<Root>
<Employees>
<Employee key="ID">1</Employee>
<Employee key="MID">0</Employee>
<Employee key="Name">A</Employee>
</Employees>
</Root>

Re: XML with key as static Value in tfileoutputMSXML

Anonymous — Fri, 23 Feb 2018 16:50:17 GMT

Hi @kate, did this solve your problem?

Re: XML with key as static Value in tfileoutputMSXML

Anonymous — Fri, 23 Feb 2018 16:59:37 GMT

Hi @rhall, thanks for sharing your logic. Yes I did solve my problem using java. I did some operation that applies on xml once its generated.

Your logic also sounds interesting. Will try also as well and will update you how it goes.

Thanks...!