Sometimes an element that occurs multiple times is a set of the same elements containing different values. For example, an element called Sales that occurs 12 times might contain the sales figures for each month of the year. Or, an element called Address that occurs twice might be a home address and an office address.

If you have this type of element in an XML source, use pivoting to treat occurrences of elements as separate columns in a group. To pivot occurrences of an element in an XML view, create a column for each occurrence you want to represent in the definition. In the monthly sales example, if you want to represent all 12 occurrences as columns, create 12 sales columns in the view. If you want to represent the sales of one quarter, create three columns. When you run a session, the Integration Service ignores any XML data for the occurrences that you do not include in the definition.

You can pivot columns when you add or edit a view in the XML source definition.

You can pivot simple types and complex types. You cannot pivot a primary key column. When you pivot columns in a view, the resulting group structure must follow the rules for a valid normalized or denormalized view. The Designer displays warnings and errors if the pivoted column invalidates a view.

Pivoting affects an element in the view where you pivot the element. When you pivot an element in a view, you do not change same element in another view.

The following figure shows two occurrences of the Address element in the StoreInfo XML file:

First occurrence of Address pivots to home address columns with prefix HOM_. The second occurrence of Address pivots to office address columns with prefix OFC_. XPath shows the two sets of columns that come from the same elements.

The following figure shows the ADDRESS element of the StoreInfo XML file pivoted into two sets of address columns:

In the following figure, the first and second address occurrences (with HOM_ and OFC_ prefixes) appear as columns in the group: