Create an XML with complex type ?

[Fernandez]

Hi,

I want to create XML files from SQL data with using tAdvancedFileOutputXML.

How to do this when XML has complex type ?

In attachment a sample of XML I have to generate.

Thanks for your feedback and your help !

[Anonymous]

This doesn't look too complicated and can be done using a tXMLMap component quite easily. The documentation can be found here: https://help.talend.com/reader/MjIZDoU6r0vLbCpGh5GAcg/s8O15oJ~4D78_NSJIRZN8w

The same sort of rules apply to the tAdvancedFileOutputXML component. Take a look here: 

https://help.talend.com/reader/WWQ40R_iTE5~~9VkUQrjgQ/i_YvOl2oUaVpW1UTnJao_g

Essentially you will need to set a loop in your output. Your "value" element is looped so you will set the "loop" on "values". Your "keyName" attribute will need to be set as an attribute. Other than that, the explanations in the links should suffice.

If you are after building something which is a little more complicated (multiple loops, for example), you will either need to use the DataMapper (only in the Enterprise Edition) or make use of something like this....

https://community.talend.com/t5/How-Tos-and-Best-Practices/How-to-create-XML-with-multiple-loop/m-p/...

[Fernandez]

In input of tXmlMap, we need to have an XML, no ?

From my side, I get datas from SQL and I want to get:

<values>
      <value keyName="City">Rennes</value>
      <value keyName="Code">35000</value>
      <value keyName="Country">France</value>
</values>

In Tree Configuration of tAdvancedFileOutputXML (as in attchment), I don't find how to do the link.

TreeConfiguration.PNG

[Anonymous]

You need to change your SQL record of City, Code and Country to be delivered in 3 rows. So, for example, you could return two columns (value and type) and supply it like this....

Value - Type
London - City
LON - Code
UK - Country

Either do that with your SQL query or you can do it with a tNormalize component and following this post : https://community.talend.com/t5/Design-and-Development/How-to-split-one-row-into-multiples-rows/td-p...

[Fernandez]

I'm feeling a little bit stupid but I didn't succeed

I simplify my request.

Suppose I have only one index "City" with the value "London" in my database.

I get the couple <"City",London> from the component tMSSQLinput for example.

And with using tAdvancedFileOutputXML, I want to get :

<value keyName="City"> LONDON </value> 

I don't understand how to do as you can see in attachment...

Capture.PNG

[Anonymous · Accepted solution]

Don't worry, this stuff can be a little counter-intuitive to start with. I believe ( from looking at your image) that you need to connect your CityValue column to your "Value" XML entity. 

[Fernandez]

Wonderful!

It's working.

Many, many thanks !

[Anonymous]

Glad you got it working. The more you use Talend, the more you will get used to it and it will become more intuitive 🙂