Create an XML with complex type ?

Six Stars

Create an XML with complex type ?

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 !

 


Accepted Solutions
Sixteen Stars

Re: Create an XML with complex type ?

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. 


All Replies
Sixteen Stars

Re: Create an XML with complex type ?

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

Six Stars

Re: Create an XML with complex type ?

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.

Sixteen Stars

Re: Create an XML with complex type ?

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://www.talendforge.org/forum/viewtopic.php?id=6611

 

 

Six Stars

Re: Create an XML with complex type ?

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

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

Sixteen Stars

Re: Create an XML with complex type ?

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. 

Six Stars

Re: Create an XML with complex type ?

Wonderful!

It's working.

 

Many, many thanks !

Sixteen Stars

Re: Create an XML with complex type ?

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