topic Re: Create an XML with complex type ? in Talend Studio

Create an XML with complex type ?

Fernandez — Mon, 19 Feb 2018 13:04:23 GMT

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 !

Re: Create an XML with complex type ?

Anonymous — Mon, 19 Feb 2018 13:15:27 GMT

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/50045#M87

Re: Create an XML with complex type ?

Fernandez — Mon, 19 Feb 2018 14:22:00 GMT

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

Re: Create an XML with complex type ?

Anonymous — Mon, 19 Feb 2018 14:37:38 GMT

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/99958

Re: Create an XML with complex type ?

Fernandez — Mon, 19 Feb 2018 20:38:41 GMT

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

Re: Create an XML with complex type ?

Anonymous — Mon, 19 Feb 2018 20:48:47 GMT

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.

Re: Create an XML with complex type ?

Fernandez — Mon, 19 Feb 2018 20:59:53 GMT

Wonderful!

It's working.

Many, many thanks !

Re: Create an XML with complex type ?

Anonymous — Mon, 19 Feb 2018 21:01:37 GMT

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