XML Patterns - SQL Server Integration Services Design Patterns - page 261

Note The XML Source will not pick up any attribute values found on the root ele-
ment of the document. To include this value in your output, you'll need to reformat the
document to include a new root element node.
Figure 9-2 shows the schema of the destination table we will be storing the custom-
er data in. As you can see, the table wants all of the columns shown in a single row,
which means we'll have to merge the Customer and Name outputs before we can in-
sert the data. The Merge Join transform is well suited for this, but it requires that both
of its inputs are sorted the same way. We could add a Sort transform on each path be-
fore the Merge Join, but performing a sort can adversely affect performance and we
should try to avoid doing so.
Figure 9-2 . Customers database table schema
Although the XML Source component doesn't set any sort information on the
columns it produces, the output is already sorted on the generated _Id columns. To get
the Merge Join to accept these inputs without using the Sort transform, we'll have to
manually set the IsSorted and SortKeyPosition properties using the Advanced
Editor for XML Source component, as follows:
1. Right-click the XML Source component and select Show Advanced
Editor.
2. Select the Input and Output Properties tab.
3. Select the Name output and set the IsSorted property to True , as
shown in Figure 9-3 .