Here is the information and XML data for translating the ANSI SQL hierarchical query to XQuery.

From the comments I received overall, I can see that what I call full nonlinear hierarchical processing (a query involving processing across multiple paths of the hierarchical structure) is not well understood today. This happened with the advent of relational processing. Before there was relational processing, I was designing commercial immediate response full hierarchical query products. The multiple pathways of the hierarchical structures were generally referred to as legs and our customers new how to utilize joining entire multiple structures (i.e. joining  full XML documents) with a simple single join operation as you will notice in the SQL below. The processing principles of full hierarchical processing involving any number of legs were known and followed automatically internally. The user access was navigationless as is SQL.

Below are two simple XML documents, named EmpView and Custview.  In the example in my article, I used the ANSI SQL statement shown below, which I explain below it. The two XML document structures have been defined to the SQL query as two standard SQL left outer views of the same name as their corresponding XML documents. The SQL standard Left outer Join was used to define each hierarchical structure in its entirety. These global SQL hierarchical views carry no overhead, at execution; unneeded pathways are automatically not accessed (left join hierarchical data preservation enables this). This allows the query user to dynamically pick and choose in any order which data fields are desired for output without any other changes requires or incurring unnecessary overhead of paths not needed.

EmpView XML

CustView XML

</root>

This is the ANSI SQL multi-path query that will be translated into XQuery:

FROM EmpView LEFT JOIN CustView  ON EmpCustID=CustID

WHERE Invoice=”Inv02”

The query description given here is the default SQL operation. The data fields that are desired in the hierarchical XML result are specified in the SELECT statement. The selected fields stay within their XML elements when output (attribute mode is the default). Node promotion will occur automatically around empty XML elements (with no data selected) in the output node. The FROM clause identifies the input objects and how they are related which is indicated on the ON clause. In this case the EmpView XML document is (left) joined over the Custview XML document linked by the EmcustID and CustID data values. The combined hierarchical structure is then filtered by the WHERE clause filter of Invoice=’Inv02’. Referenced fields in the SQL query do not have to be selected for output.

There is no user navigation performed. The SQL hierarchical processor automatically determines the combined structure being processed and the output structure by analyzing the specific SQL query. If the EmpID field is removed from the SELECT list, its EMP Element would be eliminated from the output structure using node promotion. This dynamic change in the output structure required no other changes than removing EmpID from the SELECT list. The reverse is true for adding a field to the queries select list, the field is added to the output adding a node correctly if necessary.

Node promotion is a natural relational operation performed by relational projection occurring in the underlying SQL processing. Other hierarchical operations such as node collection and hierarchical node preservation from the left join are also relationally supported.

As you can see, this full hierarchical processing is being performed at a high hierarchical conceptual level and can be specified easily and interactively by non technical users. In XQuery terms, the FLOWR expression and output XML output template is being automatically created and performed accurately regardless of the internal processing complexity. So adding a new field in the SELECT list that will cause a new hierarchical path to also be accessed and complicate the hierarchical processing further is still all that is needed. Because the output structure is known, the proper data replication removal is performed.

Result XML

This is just a simple example. But it may also answer the question raised previously of why joining two nonlinear (multi-leg) structures may be useful. A user has two different documents that they want to link together hierarchically in a particular way and perform processing that involves many different legs in both structures and produce a new full nonlinear (multi-leg structure). Using the ANSI SQL hierarchical prototype this navigational intensive problem becomes simple when performed navigationlessly.

You have the two XML input documents above and the description of what processing the SQL hierarchical query performed. You can compare your XQuery result to the output XML.

I am not that familiar with the hierarchical nested row sets used by XQuery perform. So getting the correct hierarchical processing may fall into place or require additional coordination between the processed legs (paths) to keep the results meaningful. Let me know if you need more information or have any other questions.

            /Mike