Five Stars

XML with key as static Value in tfileoutputMSXML

Hi 

 

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

 

<Employees>
<Employee key="ID">1</Employee>
<Employee key="MID">0</Employee>
<Employee key="NAME">A</Employee>
</Employees>

 

 

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

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Fifteen Stars

Re: XML with key as static Value in tfileoutputMSXML

@jlolling'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....

Screen Shot 2018-02-20 at 22.52.57.png

 

The tFixedFlowInput represents your file component. The rest will be as you need them. @jlolling 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....

Screen Shot 2018-02-20 at 23.14.19.png

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

ID

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.

 

Screen Shot 2018-02-20 at 23.22.28.png

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....

Screen Shot 2018-02-20 at 23.28.14.png

 

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>

 

Rilhia Solutions
10 REPLIES
Five Stars

Re: XML with key as static Value in tfileoutputMSXML

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

Fifteen Stars

Re: XML with key as static Value in tfileoutputMSXML

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#M16...). I believe the links and tips I provided should be of help to you here.

Rilhia Solutions
Five Stars

Re: XML with key as static Value in tfileoutputMSXML

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

 

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

Fifteen Stars

Re: XML with key as static Value in tfileoutputMSXML

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.

Rilhia Solutions
Fifteen Stars

Re: XML with key as static Value in tfileoutputMSXML

Five Stars

Re: XML with key as static Value in tfileoutputMSXML

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.

 

 

Seventeen Stars

Re: XML with key as static Value in tfileoutputMSXML

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.

Fifteen Stars

Re: XML with key as static Value in tfileoutputMSXML

@jlolling'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....

Screen Shot 2018-02-20 at 22.52.57.png

 

The tFixedFlowInput represents your file component. The rest will be as you need them. @jlolling 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....

Screen Shot 2018-02-20 at 23.14.19.png

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

ID

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.

 

Screen Shot 2018-02-20 at 23.22.28.png

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....

Screen Shot 2018-02-20 at 23.28.14.png

 

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>

 

Rilhia Solutions
Fifteen Stars

Re: XML with key as static Value in tfileoutputMSXML

Hi @kate, did this solve your problem?

Rilhia Solutions
Five Stars

Re: XML with key as static Value in tfileoutputMSXML

Hi @rhall_2_0, 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...! Smiley Happy