Hi John,

The ANSI SQL hierarchical processor is foremost a SQL processor for SQL users used to also process native XML and produce the hierarchically correct result in XML. Its look and feel tries to stay as much like SQL as possible. As a default XML output, the hierarchically processed result set is converted to hierarchically structured XML representing SQL’s look and feel and external rowset which naturally resembles the hierarchical structure processed. XQuery and SQL/XML used in SQL is not seamless and introduces navigation. SQL/XML functions require embedding and/or sub selects to format even the structure that was used in the processing. 

I will not go into all the FOR XML switches the SQL hierarchical XML product has, but there is a switch to force the default XML output overriding of node promotion to include all the intervening empty nodes. I think this satisfies one of your output suggestions. This will also allow the structure of the input structure to be examined unless it is joined to another structure in which case the total processed structure is output which is what would be expected. So the default output is condensed and modeled after the hierarchical processing specified, this is very well suited for interactive use. Replicated data is always properly removed when necessary.

Once the SQL hierarchical processor product was up and running, I played around with SQL’s ability to isolate and processes different pieces of the working set in memory and was able to perform any-to-any structure transformations using data relationships (restructuring) or just the structure semantics (reshaping). I know these terms are used interchangeably but I think their names represent different types of transforms. The semantic reshaping can also be coded to perform polymorphic transforms so that different input structures could be input and transformed to the same output structure. These transforms support full hierarchical structures in and out and maintain their hierarchical accuracy. These transforms change the structure dynamically which is fully tracked at all times.  This may satisfy your second suggestion.

I believe your understanding of the LCA and its use in incorrect. The Lowest common ancestor node is the lowest ancestor node to any other two nodes in different pathways (legs). The LCA’s current node data occurrence establishes the meaningfully range of node data occurrences of the two nodes under it. This set of node occurrences is meaningfully related for operations involving the two nodes. The DpndID attribute is located directly under the EmpId attribute. They are on the same pathway so there is no need for LCA logic. DpndID and EaddrID are on different pathways and their LCA is the EMP node. So for each data occurrence of the EMP node, all of the data occurrences for the Dpnd and Eaddr under the current Emp node data occurrence are meaningfully related and are processed together. For example,  in WHERE DpndID=1 and EaddrID=2, this is the range of combinations that will be tested. Including data values outside of this range are not meaningful and will produce invalid results. Another use of LCA is in the SELECT operation. This is not as clear in XQuery but should still apply for processing. In SQL, when SELECT DpndID WHERE  EaddrID=2 is true, all occurrences of DpndID under the current occurrence EMP data node occurrence are selected for output.

The above description of LCA processing applies for standard hierarchical processing required for database data use. Duplicate database data types in the document should not be allowed. They should be renamed or always fully qualified. They can cause variable LCAs and database data should not allow this. This is OK for markup data, not database data. Just imagine performing an aggregation for record sales on “SALES” and a missing record data causes “SALES” of book sales to be added in. This is what you would want for markup use, but not database data use.

    /Mike