In PowerCenter, this is normally done manually by developers when they create mappings sourcing XML.
In Data Validation Option, this can be done using the Join View to create joins across selected XML groups in the XML structure.
There are two ways to create these Join Views for XML data:
Manually creating joins using the standard Join View dialog.
Using the Create Join View dialog specifically available to flatten XML sources/targets.
This example explains how to do create a join view using the Create Join View dialog.
Right-click on an XML definition and select
Create Join View
The XML Join View creation dialog appears. Fill in the fields in the dialog and select the groups you want to Join.
The Join View Table Alias and Group names will be used by default to prefix the actual column names of the elements in the generated Join View. Thus an element called EMAIL in a Group called CONTACT with Join View Table Alias called EMPLOYEE will be EMPLOYEE_CONTACT_EMAIL. You can override this in the Join View Table Alias field.
Once you have entered all the information, click OK to create the Join View.
The Join View can be seen in the Join View section of the Navigator.
Looking at the Join View, there are 4 "tables" joined, one for each group in the XML definition. All fields are selected for output.
You can edit any aspect of the view to suit your needs, such as reducing the number of output fields, changing their order or adding joins with other data sources. For example, you can add a join if one is needed between the XML and a relational table for testing.