The Designer represents an XML hierarchy in an XML definition as a set of views. Each view represents a subset of the XML hierarchy. A view consists of columns and rows. Columns represent elements and attributes, and rows represent occurrences of elements. You use the XML Editor to edit the XML views.
In this lesson, you use the
to pivot the three occurrences of SALARY into three columns in an XML group. You do this because the multiple-occurring element SALARY represents three types of salary: a base salary, a commission, and a bonus that appear in the XML file as three instances of the SALARY element.
The following image shows contents of Employees.xml in the editor Notepad:
To work with these three instances separately, you pivot them to create three separate columns in the XML definition.
You create a custom XML view with columns from several groups. You then pivot the occurrence of SALARY to create the columns, BASESALARY, COMMISSION, and BONUS.
The following image shows the
Double-click the XML definition or right-click the XML definition and select
Edit XML Definition
to open the
Create XML View
to create an XML view.
From the EMPLOYEE group, select DEPTID and right-click it.
Show XPath Navigator
Expand the EMPLOYMENT group so that the SALARY column appears.
, select the following elements and attributes and drag them into the view:
The XML Editor names the view X_EMPLOYEE.
can transpose the order of the DEPTID and EMPID attributes when it imports them. If this occurs, you can add the columns in the order they appear in the
. Transposing the order of attributes does not affect data consistency.
button on the
Select the SALARY column and drag it into the XML view.
must include the EMPLOYEE column at the top when you drag SALARY to the XML view.
The following image shows the resulting view that includes the elements and attributes shown in the XML view:
Drag the SALARY column into the XML view two more times to create three pivoted columns.
Although the columns appear in the column window, the view shows one instance of SALARY.
The wizard adds three columns in the column view and names them SALARY, SALARY0, and SALARY1.
Rename the columns.
The following table describes the information to modify the name and pivot properties:
New Column Name
To update the pivot occurrence, click the Xpath of the column you want to edit. The
Specify query predicate for Xpath
window appears. Select the column name and change the pivot occurrence.
to save the changes to the view.
to close the XML Editor.
The following image shows the source definition that appears in the Source Analyzer, with all of the listed EMPLOYEE attributes and elements:
The pivoted SALARY columns do not display the names you entered in the Columns window. However, when you drag the ports to another transformation, the edited column names appear in the transformation.