Maverick support.

One of the (not so intentional) assumptions built into the mapping schema technologies of SqlXml is that the hierarchy of the target Xml roughly matched the PK/ FK relationships on the underlying relation source. In other words, elements are mapped to tables and the hierarchy of two elements is established in the instance document via joining along key relationships.

The generalized rule is that two elements in a mapping schema must have a sql:relationship defined between them that defines the join utilized to build the hierarchy. There is a simple exception to this rule in that an element which maps to a table can contain simple content sub-elements which map to columns. You can also do some fringe tricks with sql:constant, but in general this restriction means that the Xml which is generated/ consumed by SqlXml must be about as normalized as the underlying tables.

One interesting caveat to this rule is that the sql:relationship can be defined along any two columns – not exclusively key columns, so it is possible to do some interesting shapes that don’t particularly follow the key relationships of ones database. For example, joining on dates.

In ends up that this limitation seems to be a real pain for customers and one of the top feature requests for SqlXml. For lack of a better term, this feature is called “Maverick” support by the SqlXml team. (The SqlXml team utilized the names of characters from the movie Top Gun as code/ feature names for several releases).

There seems to be equal demand for allowing several tables to be mapped to a single element – which SqlXml can handle better, but with some difficulty – particularly for CUD operations.

This type of feature can actually be quite simple. For example, taking a very denormalized table with a large amount of columns and exposing the rows as Xml with several levels of hierarchy.

Or quite complicated. For example pushing a column from a table mapped an element deep in a hierarchy up to ancestor thereby “skipping” levels.

Interestingly, this really isn’t exclusively a SqlXml problem but one of the inherent temptations of building a mapping technology that maps one domain to another: The temptation to add in transformation features. O/R has the same issue – but with some more restrictive considerations around object identity.

Another way to think about this is to decompose the current SqlXml mapping process into three abstract parts. The default mapping between the relation database and the Xml (tables map to elements of the same name – and attributes map to columns), the ability to filter the results of the default mapping (XPath), and transformation. A simple example of the latter is changing elements names thereby allowing an element to be mapped to a table where the names differed. A more complicated example would be allowing one table to map to multiple elements (Maverick support). Sure, any one could serialize these tasks. For example, I could run a sql query, generate Xml from the results, run an XPath over the results, and then transform it – but the trick is to compose all the tasks together as much as possible for performance reasons.

IMO, I believe with SqlXml we actually went a bit overboard with the composability aspect in that we did not allow for a certain amount of post query transformation to occur as part of the overall design. This lead to rather big and complex For Xml Explicit queries being generated – so much in that some scenarios the product became unusable. Later designs have shown that doing some of the transformation work on the client post query is probably a better design, but I digress.

Back to the third feature from above – transformation. From my experiences, this is a slippery slope. Some of the features we put in to support this made sense (sql:hide) – but depending on what one wants do to, it can get complicated very quickly. And I thought I would never say this – but we do have XSLT.

So back to reality – what are the current work arounds for not having the “Maverick” feature? This basically comes down to utilizing some sort of pre or post process for getting the target data into a shape which then can be consumed by SqlXml.

For query scenarios, this means mapping to Sql Views instead of underlying tables or post processing the query results with XSLT. The latter in many cases actually can have some quite nice runtime performance depending on the required transformation since both SqlXml and XSLT will avoid caching as much as possible.

For update/ bulkload scenarios this means loading data into temporary staging tables or preprocessing the source Xml with XSLT. OpenXml is also an option for smaller source documents – but could require one to write some complex sql statements.