In the first recipe of this chapter, Working with nested structures, we built the nested structure with the help of the most universal transform in Data Services—Query transform. Query transform has the power to define column mapping, filter data, join datasets together, and merge data in nested segments. In fact, many transforms that you have used before, such as History_Preserving, Table_Comparison, Pivot, and others, can be substituted with the set of Query transforms. Of course, those would be complex ETL solutions requiring more development time, would be harder to maintain and read, and, most importantly, less efficient in terms of performance.
In this recipe, we will take a look at another transform XML_Map, which does exactly the same task as performed in the previous recipe—builds and transforms nested structures.
We will use the same source tables PERSON.PERSON and HUMANRESOURCES.EMPLOYEE to build a dataset of job titles with nested lists of employees.