Table of Contents

Search

  1. Preface
  2. XML Concepts
  3. Using XML with PowerCenter
  4. Working with XML Sources
  5. Using the XML Editor
  6. Working with XML Targets
  7. XML Source Qualifier Transformation
  8. Midstream XML Transformations
  9. Appendix A: XML Datatype Reference
  10. Appendix B: XPath Query Functions Reference

XML Guide

XML Guide

Adding a Pivoted Column

Adding a Pivoted Column

A pivoted column in an XML definition is a multiple-occurring element that forms separate columns for the element occurrences in a view. You can create pivoted columns from any multiple-occurring element in an XML definition. You can also pivot elements in a view row.
If you add a pivoted column to a view, a default occurrence number appears in the Columns window. This number indicates which occurrence of the element to use in the column. You can change the occurrence number or add more occurrences of the element as new columns. If you do not rename the columns, the XML Editor adds a sequence number to each pivoted column name.
You cannot change a pivot value if the pivot value is part of a view row.
To add a pivoted column to a view:
  1. Select the XML view in the workspace.
  2. Highlight the element in the Navigator that you want to pivot.
    Or, highlight any element in the parent chain of the element that you want to pivot.
  3. Click Components > XPath Navigator.
  4. Select Advanced Mode.
  5. Drag the column to pivot from the XPath Navigator to the view in the XML workspace.
    The Designer adds a default occurrence number in the Columns window. This number indicates which occurrence of the element to use in the column.
    In the following figure, the view contains two occurrences of Sales for the third occurrence of Product:
    The first Sales occurrence is in the column called First_Half_Sales. The second Sales occurrence is Second_Half_Sales. Region is an attribute.
  6. Click the XPath link to change an element occurrence number.
    The Specify Query Predicate for XPath window appears.
  7. Select the multiple-occurring element to edit.
  8. Change the occurrence number in the Edit Pivot box and click OK.

0 COMMENTS

We’d like to hear from you!