In an earlier blog post I demonstrated how to map between hierarchical (XML) and relational (SQL) data using Altova MapForce. I also mentioned that MapForce can generate code (C#, C++, XSLT for instance) to execute the mapping in an environment outside MapForce. This being a blog mostly about SQL Server, one obvious question is how to run the generated mapping from SSIS.
In an earlier blog post, I demonstrated a way to insert a hierarchical XML document into a relational table structure by using XQuery. That solution works well in smaller solutions, but it’s a “code only” solution that can become complex and hard to maintain in larger projects.
An alternative solution would be to use the graphical mapping tool Mapforce from Altova.
The min and max aggregates in SQL Server are normally used to work over rows, getting the max or min value from a set of rows. But what if you want to get the max or min value from a set of columns instead?