Table of Contents

Search

  1. Preface
  2. Introduction to Data Validation Option
  3. Repositories
  4. XML Data Source
  5. Tests for XML Data Sources
  6. Connections
  7. Expressions
  8. Table Pairs
  9. Tests for Table Pairs
  10. Single-Table Constraints
  11. Tests for Single-Table Constraints
  12. Examples of Tests from Spreadsheets
  13. SQL Views
  14. Lookup Views
  15. Join Views
  16. Aggregate Views
  17. Business Intelligence and Reporting Tools Reports
  18. Dashboards
  19. DVOCmd Command Line Program
  20. Troubleshooting
  21. Appendix A: Datatype Reference
  22. Appendix B: Reporting Views
  23. Appendix C: Metadata Import Syntax
  24. Appendix D: Jasper Reports
  25. Appendix E: Glossary

Data Validation Option User Guide

Data Validation Option User Guide

Flattening XML using a Join View

Flattening XML using a Join View

To select and test columns across groups, the XML hierarchy must be flattened by joining across the XML groups.
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.