The following example is based on a simple relational structure where a Company table has a one-to-many relationship with a Contact table. A Contact must refer to just one other Company. This is realized in a Company Id kept on the Contact table.
|Company and Contact Tables|
<company companyName="HUXLEY INDUSTRIES, INC.">
<contact firstName="David" lastName="King"/>
<contact firstName="Sybil" lastName="Bedford"/>
<company companyName="SWAN CONSTRUCTION CO.">
<contact firstName="Jocelyn" lastName="Brooke"/>
<company companyName="CLASSICS PRODUCTION, LLC.">
<contact firstName="Stephen" lastName="Spender"/>
<company companyName="NOVEL ENTERTAINMENT">
<contact firstName="Lee" lastName="Bartlet"/>
The companies element is repeated, as is the contacts element.
To process the XML, start with an XML schema (XSD). This link points to an XSD created using the Venitian Blind technique. This produces a single top-level element (companies) with reusable types. All other elements (company, contacts, contact) are nested within type definitions.
Start with a job that joins the source tables and writes the output to a tAdvancedFileOutputXML
|Talend RDBMS to XML Job|
|Metadata from companies.xsd and Added Items|
Right mouse-click over the contact item and "Set as Loop Element". On the company and contacts item, select "Set as Group Element". This defines the repeating groups within the XML document and turns the normalized join into a nested structure.
Next, use a tMap to map the source RDBMS fields to the fields created from the XML metadata. Note the FirstName field will render a firstName XML attribute.
|RDBMS Mapping to Target XML|