ANSI SQL Navigationless XML Hierarchical Processing

XML Background 

Even though XQuery was supposed to be designed from the top down to be an XML processor, a number of politically driven decisions were made early in the design that were bad for XML and its SQL support of XML. 

SQL vendor designers wishing to move the industry past SQL to the new XQuery product needed a migration path from SQL to XQuery to speed up and ease this process. The solution used was to add the relational processing capability to XQuery. This can be seen today with the relational standard Inner Join operation being placed in XQuery as its default join operation.  

The decision to support the Inner Join in XQuery killed any chance of XQuery supporting XML’s natural hierarchical processing potential in a dedicated and intelligent manner. With XQuery supporting relational and hierarchical processing at the same time, it is impossible to support hierarchical processing intelligently and accurately because it can not be assumed the hierarchical processing is being performed. In addition, the default Inner Join in XQuery is a hierarchical structure killer, one use and any hierarchical structure is destroyed.  

The SQL/XML specification design and capabilities were influenced and designed following XQuery capabilities using very XML centric functions and Xpath procedural navigation. Even though XQuery advocates claim that XQuery is very similar to SQL, XQuery XML support and procedural navigation is totally foreign to SQL users especially since SQL is known as a navigationless language and SQL users are probably not that familiar with XML. 

XML was originally designed to support semistructured data with its variable format suitable for containing markuped unstructured data. Use of XML containing database structured data such as SQL’s relational data was an afterthought. Unfortunately, SQL/XML support in SQL carries with it the responsibility to support semistructured data which requires navigation because of its variable complexity. SQL’s standard structured data is fixed and is unambiguous from a querying point of view which can allow the nonprocedural and navigationless processing of structured XML data used when semistructured data is not present or being processed. Semistructured data processing is fuzzy and inexact while structured data requires exact and precise processing. The requirement of also supporting semistructured data and its required navigation is not necessary in SQL. XQuery can handle semistructured data, and in this way SQL can remain dedicated to processing structured data which can include the transparent and navigationless structured data in XML.  

XML Processing     

XML processing today is basically limited to single linear path processing. This is due to navigational complexity of navigating multiple paths at the same time and to the little known fact that multipath processing requires a complex processing that requires Lowest Common Ancestor (LCA) logic. This logic is not automatically supported in current XQuery and SQL/XML processing. 

Another requirement to support LCA processing is to operate hierarchical structure-aware, with knowledge of the structure being utilized in the processing. This is not possible today because XML processing is not limited to supporting only hierarchical structures. This is an important capability because hierarchical structures can produce many times their specific data value with multipath queries as they do with single path linear queries. LCA support in XQuery is being academically researched today using LCA functions on top of XQuery. Unfortunately this is not being performed transparently or automatically, it requires users’ knowledge of LCA logic to use, and is limited in scope. 

 SQL Hierarchical Processing 

A recent discovery that has the potential to breathe new life into SQL is that standard ANSI SQL can support full multipath hierarchical processing using standard SQL-92 with its addition of the LEFT Outer Join. This is an inherent capability in ANSI SQL that has also solved the relational/XML data integration problem with the capability to naturally raise SQL processing to a hierarchical processing level. 

Currently there is no standard for hierarchical processing. Every vendor or user is using their own hierarchical processing procedures. SQL’s inherent hierarchical processing is a valid subset of relational processing that is operating hierarchically correct. SQL’s LEFT Outer join syntax can model full multipath hierarchical structures and its associated LEFT Outer join semantics defines its hierarchical operations. The ANSI SQL engine can process this hierarchical syntax and semantics input directly to perform hierarchical processing and to automatically produce correct rowset hierarchical results. A further SQL discovery identified LCA processing occurring naturally in the relational Cartesian product used in the joining of tables or structures that model the hierarchical structures. 

Extending SQL Hierarchical Processing to XML 

Limiting SQL processing to hierarchical processing means that the hierarchical structures defined and dynamically modified during processing by joining and transforming them can be tracked. This means that hierarchical structure-aware processing can be performed. This allows the new capability of dynamically formatted XML output to be possible. This opens the door to many new capabilities such as dynamic XML publishing of reports involving structured data. 

Unlike XQuery, SQL’s Dynamic SELECT List limits the processing to only the parts of the structure necessary based dynamically on the output specified in the SELECT List. This means that hierarchical SQL views can be global with no additional overhead for using views larger than necessary for the active query. This keeps the length of query invocations terse enabling simple but complex queries and allows a global view to handle many different and dynamic query uses, reusing data and limiting the number of different views needed. This also allows SQL global queries to be easily specifies using the “SELECT *” capability where entire structures can be easily modified or filtered. This is a capability not previously feasible with XML structures. XQuery and SQL/XML functions on the other hand have their output data processing hard coded in their procedural code making them fairly static and less flexible. 

The navigationless XML processing allows nontechnical users to finally have access to XML. Users also do not have to have knowledge of the hierarchical structures being queried. This means that anyone can specify a powerful multipath query without even knowing it is a multipath query using SQL’s powerful yet simple SELECT List. 

Is this SQL Transparent XML Hierarchical Processing Possible? 

This very advanced next generation of SQL supported structured XML multipath hierarchical processing is possible and has already been accomplished today. My small company has been researching this naturally available technology and has built an online interactive ANSI SQL Transparent XML Hierarchical Processor prototype available for online interactive testing at www.adatinc.com/demo.html. The demo contains complete documentation for this technology and its demo use.  

Our latest publications on this technology can be found at http://www.adatinc.com/ourpublications.html. 

Basic hierarchical optimization that semantically removes unnecessary nodes from access consideration before processing is based on the specified query. It operates the same for logical and physical hierarchical structures. It operates successfully in SQL when performing hierarchically and outputting its result set in a hierarchical structure like XML. But when the result is left in and used from the relational result set, SQL purists have said the optimization is not valid because the optimized result set contains fewer rows than the un-optimized result and this difference in the optimized result makes it invalid. This difference is because of the effect of replicated data values produced by the Cartesian product which is naturally less for this type of optimized processing. 

This may be why hierarchical optimization has not been utilized in SQL processing when SQL is operating hierarchically. The validness of this powerful hierarchical optimization in SQL is explored in this blog after supplying some background in SQL hierarchical processing and optimization. 

Hierarchical Processing 

My research, blog and articles have shown that ANSI SQL supports full hierarchical processing that came with the addition of the ANSI SQL-92 Left Outer Join. It supports the hierarchical data preservation principles using the SQL Left Join syntax that can model hierarchical structures placing the left argument over the right argument. This includes the outer join’s ON clause which specifies the hierarchical link points at each join point. The associated ANSI SQL hierarchical semantics specifies the hierarchical processing of the hierarchical structure. This allows the Left Outer Join syntax to be processed directly by the relational engine to support hierarchical processing of hierarchical modeled structures. This is demonstrated directly below with a hierarchical data structure and its ANSI SQL hierarchical data model definition. 

          A            SELECT A.a, D.d
        /     \          FROM A.a LEFT JOIN B.b ON A.key=B.fkey
      B      C                            LEFT JOIN C.c ON A.key=C.fKey
             /    \                           LEFT JOIN D.d ON C.key=D.fkey
           D     E                        LEFT JOIN E.e ON C.key=E.fkey

                             Figure 1 

One very important process missing from the above SQL hierarchical syntax and semantics in Figure 1 is the lesser known Lowest Common Ancestor (LCA) logic necessary for the hierarchical coordination between pathways for the processing of multipath queries. This is required to keep the queries meaningful. This LCA processing turns out to be performed inherently in ANSI SQL by the natural operation of the relational Cartesian product when processing hierarchically modeled structures using the Left Outer join. This is explained more fully at: http://www.tdan.com/view-articles/11069 

Hierarchical Optimization 

Hierarchical processing optimization relies on hierarchical processing’s natural data preservation. In the SQL query in Figure 1,  SELECT A, D specifies the nodes to be output. This actually controls the optimization and the required processing from the above view in Figure 1. Only nodes A, C and D need to be accessed from the query: SELECT A, D. Node B was not referenced and is the last node on its path, so it does not require access. The hierarchical data preservation rules allow higher level nodes up the path to be preserved when there are no matching nodes down the paths from it. Node C even though not selected for output requires accessing to navigate correctly to node D. Node E was not selected for output and is followed by no other nodes and so does  not require access.  So this hierarchical optimization can be stated as only the nodes that are SELECTed for data output or are on the path to an output node require access. The resulting structure from this process and its processing SQL is represented in Figure 2. Notice how the elimination of nodes from database access can be matched and mapped from the input Left Outer Join in Figure 1 to produce the optimized Outer Join in Figure 2. 

          A            SELECT A.a, D.d
           |             FROM A.a LEFT JOIN C.c ON A.key=C.fKey
          C                                LEFT JOIN D.d ON C.key=D.fkey
           |
          D
                      Figure 2 

Inner and Full Outer Joins have different preservation rules that do not follow hierarchical processing rules and can not be optimized by this technique. For example, the Inner Join views always require every node in the input view to be accessed for existence because any missing data occurrences anywhere in the view can cause the entire row occurrence to be removed. The hierarchical optimization described here is quite powerful because it removes entire nodes from access consideration from the start of processing. This hierarchical optimization can and should be followed by the standard relational optimization which can still offer further processing optimization which has also been further helped by the smaller hierarchically optimized view being optimized. 

Replicated Data 

Replicated data in relational processing is data that is replicated because of relational Cartesian product processing where the same data occurrence value needs to be represented in more than one row to make sure the correct result is produced. This should not be confused with duplicate data in the input which represents different data occurrences of the same data value. Replicated data is usually removed from the result while all duplicate values are distinct in meaning making each occurrence necessary. 

Is Hierarchical Optimization in Relational Rowsets Invalid? 

Applying hierarchical optimization as described within can reduce the number of rows in a rowset because of the removal of unnecessary nodes (tables) from the active view supplied avoiding their join operation which usually produces considerably less replicated values causing less rows to be generated and returned even though the actual meaningful data in the rowset returned remains the same. Some relational purists say this makes the hierarchical optimization invalid because it has changed the number of rows that would normally be produced and returned without optimization by eliminating the unnecessary replicated values. They feel any change between the optimized and un-optimized results are invalid because the optimized result should return the identical result as the un-optimized result. They feel the number of rows returned could be significant to the query. In actuality, the optimized result actually produces the most correct result as explained below. 

Proving that hierarchical optimization in rowsets is valid is the fact that hierarchical processed rowsets mapped to physical hierarchical structures removes all replicated data. Replicated data is not used or is necessary in a hierarchical structure. This demonstrates that replicated data are extraneous and have no effect or meaning in physical hierarchical structures and this should be the same for hierarchically processed result sets. In addition, mapping physical hierarchical structures back to hierarchically structured rowsets would exactly re-produce the hierarchically optimized rowset results with only the necessary replicated rows re-produced. This proves that hierarchically optimized rowsets are the most accurate rowset with no unnecessary replicated rows. So Hierarchical optimization is valid and actually offers a more correct solution in relational rowsets.  

For more information on SQL hierarchical optimization and processing, see my site at: www.adatinc.com.

SQL’s newer Left Outer Join operation is hierarchical in nature. It preserves its left argument over its right argument when there is no match. This enables ANSI SQL to model and define hierarchical structures using SQL’s Left Join syntax. This enables the SQL processor to directly process the defined hierarchical structures by naturally following the semantics of the Left Outer Join. Unlike today’s XML hierarchical processing, this is a full multipath hierarchical processing allowing SQL queries to reference and process multiple pathways of the structure. The hierarchical data preservation spreading across multiple pathways works perfectly by the Left Outer Join syntax shown below.

                    Structure Example

The SQL above models the hierarchical structure above it. You can see how the hierarchical paths are extended and when new paths are started off of existing paths by comparing the SQL to the data structure. The SQL syntax acts as the hierarchical data modeling language with left structure arguments placed over right structure argument and the ON clause specifying the node link points in the structure. The syntax’s associated semantics describes the SQL hierarchical preservation operation when directly processed by the SQL engine.

Multipath Semantics

Often overlooked in the XML industry is an extremely imported higher level hierarchical processing logic necessary to assure that hierarchical multipath queries produce correct meaningful results. Take for example an SQL query selecting data from one path of a hierarchical structure based on a data value from another path. Such as SELECT C.c FROM TestView WHERE G.g=4 using the structure and SQL view above. There is a semantic hierarchical relationship expressed across the referenced pathways that is not represented in the Left Join operation. The utilization of this hierarchical structure semantic information is necessary to processes multipath queries.

This hierarchical structure semantics processing is based on Lowest Common Ancestor (LCA) logic. This occurs because the LCA is the node between the two referenced points that defines or limits the range of a valid meaningful relationship between the two points. We determined the existence of this LCA multipath required operation in ANSI SQL and traced its operation to the relational Cartesian product. How and why this occurs naturally in the operation of the Cartesian product is explained in the article: The Ghost in the Machine at; http://www.tdan.com/view-articles/11069.

SELECT LCA Logic Usage

In the query above repeated here: SELECT C.c FROM TestView WHERE G.g=4, the Lowest Common Ancestor (LCA) node derived from C.c and G.g is node A. The LCA node limits the range of the relationship. In this query: SELECT F.f FROM TestView WHERE G.g=2, the LCA node in this case is Node E. There can be multiple LCA nodes from a single query as in: SELECT C.c, F.e FROM TestView WHERE G.g=2, which utilizes LCA node A and node E applied separately for each SELECTed data value.

This automatic use of LCA logic in multipath processing is called a Schema-free query in academic research. This is because the navigation for multipath queries needs to be automatic and this means that the user does not need to know the data structure. XQuery support of LCA and Schema-free query is still in academic research.

WHERE LCA Logic Usage

LCA logic is also required for a complex WHERE clause filtering which references multiple pathways as is in: SELECT  A.a FROM TestView WHERE C=4 AND D=5. In this case node B is the LCA node and all combination of matches is applied under the B node. This is called XML Keyword Search in academic research; it is also in XQuery academic research. Both types of LCA logic usage can be applied together.

Schema-Free Query

The automatic processing of multipath queries means that they are always processed correctly and automatically without user navigation or knowledge of the data structure necessary. To try this first hand, click on the demo at www.adatinc.com/demo.html.

When available to the user, Schema-Free queries can be a much better alternative to using structure transformations when used for reshaping the structure to accommodate access to the data. Restructuring could include taking the data structure apart and putting it together again using different relationships in the data to create totally new semantic meaning to the data structure. I call this Restructuring. This is not what I am concerned with in this blog. Transformation could also mean what I call Reshaping where the data structure is reshaped into a different data structure preserving the relationships. This is usually performed to satisfy the requirement for a specific data structure shape or organization.

Schema-free queries imply two aspects of query execution. One is that the query is independent of the structure of the data to be processed. This also implies the query navigation is automatic and the user does not need to have knowledge of the structure. The other aspect of schema-free processing is that it is used with multipath hierarchical structures and can process multipath queries. For example, selecting data from one path of the structure based on a different path of the structure. The above references to transformations and Schema-free queries will become clear with the use of the hierarchical diagrams below. 

     Actual Structure      Transformed Structure 

      A                  B 
     / \                 | 
    B   C                A 
                         |
                         C 

The Actual Structure above was probably built over time first by the user requiring access to path A/B and later partially reused to support access to path A/C. Both relationships are most likely one-to-many relationships. Now let’s suppose the user needs the structure represented by the Transformed Structure above based on the semantics of the Actual Structure.  The basic query the user needs to use is SELECT C WHERE B>4. The Actual Structure can not be queried because it involves a complex multipath query requiring complex semantic logic so the Actual Structure is reshaped into the Transformed Structure as shown above and the same basic query is used which is another characteristic of schema-free queries. 

If the user wanted to achieve the exact same semantics as in the Actual Structure, this is not achieved because the A/B path transformed into the B/A path has changed the relationship from a one-to-many relationship into an many-to-one relationship and this can alter the semantics of the query. If the WHERE B>4 condition is true multiple times in B, it still only qualifies A once in the Actual Structure where path A/B is a one-to-many relationship. On the other hand,  in the Transformed Structure, the WHERE B>4 condition qualifies A for each “>4″ match in B where path B/A is a many-to-one relationship. This causes multiple structures with the root being B in the result and additional data replication. This occurs because of the difference in semantics between one-to-many and many-to-one relationships which is intuitive. 

If the above Transformed Structure semantics is desired, than the transform is necessary. This operation is usually not desired, but is tolerated if it is the only solution available.  If this operation is not desired and Schema-free processing of multipath structures is possible, than there is no need to perform the transform. In fact any multipath query applied to the Actual Structure will produce the correct and desired result and semantics. This significantly increases the number of queries possible from the single hierarchical structure. These multipath queries also dynamically increase the value of the data in the structures because the processing across pathways utilizes the naturally existing structure semantics existing between pathways. This increases the natural processing power and correctness of these queries. 

Our new comprehensive paper that documents the new capabilities described above can be found at: http://www.slideshare.net/mmdavid/ansi-sql-transparent-xml-structured-data-multipath-hierarchical-processing-with-dynamic-output-2360420

There is a newer type of database navigation on the market that is also referred to as navigational but is quite different than  the currently understood meaning of navigational data access. I call this newer type of navigation interactive navigational access and compare its operation to the other types of navigation below. 

Navigationless access is the ability of the database software to access the database without the user having to instruct the application on how to access the required data. This is specified at a nonprocedural level where the user’s query indicates what data is needed and not how to locate it. Navigational access requires the user to specify a series of step-by-step instructions on how to locate the desired database at a low level procedural level. Navigational processing requires control of the access operation. There is a relatively newer access operation which is also referred to as navigational processing which is actually controlled at a high nonprocedural level where high level controlled access through the database is necessary but low level procedural access is not necessary. This is done by the user stating where in the database to go next and the database system takes care of the low level navigation to get there automatically. This process is interactively progressed continuing through the database from one place in the database to any other without requiring low level navigation.

Navigationless Access

Navigationless database processing is possible when the hierarchical data structure is structured, well behaved, unambiguous, and its data structure is known. In this way the system can perform the entire query automatically. All of the navigation is performed automatically. The system decides in what order the required data items are processed in order to process the query in the most efficient way.

Navigational Access

Navigational procedural access is necessary when data is semistructured, its structure is dynamic and ambiguous for automatic querying with multiple nodes with the same name. Because of this the system needs to be instructed at a low level step-by-step procedural manner on how to access the required data. 

Interactive Navigational Access 

Interactive navigational access is a combination of navigationless and navigational access with the benefits of both. It allows the user to move quickly and nonprocedurally to any specific position in the database and then depending on the data at that location to decide to follow the trail to another place in the database. This traveling between desired destinations in the database is performed at a high nonprocedural level but maintains procedural control of the higher level database application logic. While navigationless queries specify all of the query information in a single query, interactive navigational access must follow the data clues in the database, but can perform this at a high navigationless level which is still navigational at a higher conceptual level.

I have been researching and writing on how ANSI SQL can perform very powerful nonlinear hierarchical processing that supports multipath processing known as LCA Query processing. The LCA acronym stands for Lowest Common Ancestor processing. This is the lowest common link point between multiple pathways. This lowest LCA node is used to assure that the results of the query are meaningful even though they encompass multiple pathways. Amazingly, this LCA processing is also performed automatically in ANSI SQL. It turned out to be performed by the ever useful relational Cartesian product used in SQL when hierarchical structures are modeled using the hierarchically oriented SQL Left Outer Join operation.

Putting all this research together my partner Lee Fesperman and I built an ANSI SQL Transparent XML Hierarchical Processor prototype. The prototype which operates interactively worked so well that it allowed me to easily and quickly experiment with hierarchical structure transformations. In researching hierarchical transformations using XML, I was bothered by the loose transformation terms Restructure and Reshaping which are used interchangeably. In my data structure transformation research, I had divided my research between two types of transformations that I relabeled Restructuring and Reshaping which I feel their different names implied naturally. 

Restructuring is performed by breaking the structure apart and putting it back together differently using other data relationships that naturally exist in the data. This introduces new meaning to the structure. On the other hand, Reshaping implies molding the structure into a new shape utilizing the natural semantics in the data structure. This naturally changes the data structure, but preserves the meaning and semantics in the structure. Restructuring and Reshaping usually have different uses. Reshaping can be used to match the structure to an application. While Restructuring is used to produce a structure that is a new in meaning and use. The processing of both of these techniques can be combined. 

Restructuring is pretty standard and direct in performing transformations. Reshaping is more vague and problematic and would probably require more work at accomplishing, and there is the question of whether the result is semantically valid. What got me interested in Reshaping was an example I saw used in XQuery where a linear hierarchical pathway was inverted. By using Restructuring using primary and foreign keys, this would be a simple task. But in XML the structure is represented contiguously in storage so keys are not necessary in XML and are usually not present. The means the problem of inverting a structure in XML becomes much more of a problem. It becomes a problem of reshaping.  

The inversion example I saw in XQuery used an interesting technique of comparing the structure to itself to simulate key relationship that was able to rebuild the structure backwards to accomplish the linear inversion. With experimentation I was able to accomplish this linear path segment inversion in standard SQL using our prototype by simulating the XQuery inversion technique, but it was made considerably easier in SQL using its higher level language. With the SQL prototype solution operating hierarchically, the semantics was preserved and the result was assured to be correct. 

With accomplishing the linear inversion, I believed a full any-to-any nonlinear hierarchical structure reshaping solution was possible. The interactive ANSI SQL hierarchical processor prototype gave me the opportunity to experiment in real-time and I found the general solution. In addition, since SQL was performing the transform nonprocedurally, the any-to-any structure transformations could be specified to operate in a polymorphic fashion meaning that the input structure did not have be known beforehand. In other words, different input structures could be used with the same SQL transformation solution.

This new Any-to-Any Data Structure Reshaping is described fully with actual prototype examples in my article at: http://semanticuniverse.com/articles-semantically-controlled-any-any-data-structure-reshaping.html 

My Restructuring algorithm is described fully also with examples at

http://www.devx.com/xml/Article/41464/1954?pf=true 

Hierarchical structures are very unique not only in how well they organize data, but also in how they naturally capture more meaning than is stored with the data. Even more impressive is their ability to dynamically process this natural goldmine of meaning in unlimited ways that further increases the value of the stored data. With the increased use of hierarchical XML data structures today, there is an incredible amount of unused data value potential available.

Hierarchical structures are easily built and expanded naturally over time. Suppose we start building a hierarchical structure by creating and populating node A over node B. Then at some point we add node C also directly under node A and also start populating it. This is an example of reuse by reusing the already existing node A and its data. We also know what nodes node A has control over and how nodes B and C are related to each other. With the addition of each new node, the data value will increase in a nonlinear fashion. At this point, this multipath nonlinear hierarchical structure looks like the one below.

                 A
                /   \
              B   C

The interesting question is how are nodes B and C related. How does a query language handle this? Can node C be selected for output based on a data value in node B?  Can node A be selected on data values in multiple nodes B and C?  Does this capability dynamically increase data value? The answers to these questions are examined in my current article below:

       http://www.tdan.com/view-articles/11494 

Occasionally I produce a document or correspondence that I feel is also useful as a blog. This blog is such a case. It will demonstrate what will be possible in ANSI SQL structured XML data processing in the near future. A major problem I noticed with XML processors today is that they try to process two types of XML together without differentiating their processing. The two different types of XML are markup (semistructured, document) and database (structured, data). By separating the types of processing, each type can be better processed in isolation, but processed the same, both types will suffer. SQL processes structured data, so its processing should assume it is processing only structured XML data. This allows new XML capabilities such as navigationless XML and all the new capabilities this enables as will be discussed. 

My work in this area started when I discovered that ANSI SQL can inherently perform full hierarchical processing straight out of the box. My research showed how to make this happen by modeling full multipath hierarchical structures in SQL using the Left Outer Join syntax. The associated Left Outer Join semantics as defined in the ANSI SQL specification precisely defines how the ANSI SQL engine would process the Left Outer Join basic data preservation processing. This data preservation exactly equates to basic full multipath hierarchical processing. The Outer Join’s ON clause specifies the structure link points at each specific join point; the older single WHERE clause could not unambiguously do this. It is now used for global hierarchical data filtering. 

Most XML professionals do not realize that XML processors today are basically limited to single linear path queries. This is for two reasons. First, all XML processing processors today require user navigation. Multipath nonlinear hierarchical data processing is too difficult to code using user navigation. But the real stumbling block is the second reason that multipath hierarchical processing requires Lowest Common Ancestor (LCA) logic processing in order to coordination the processing between hierarchical pathways in order to produce meaningful results. Most XML professionals today are not familiar with LCA processing. This is because there are no W3C standards for how to carry out hierarchical processing and each vendor can have their own processing rules for processing. This means invalid processing can go unnoticed today and can’t be positively trusted. But ANSI SQL’s inherent hierarchical processing automatically follows hierarchical principles in the hierarchically modeled Left Outer Join for correct processing. 

Even XQuery does not support LCA query processing today. But this capability is important which is proven out by a number of academic projects attempting to add LCA processing logic to XQuery.  Quite amazingly, ANSI SQL automatically supports this LCA processing fully when a hierarchical structure has been modeled in SQL and is processed directly by an ANSI SQL processor. See my recent article on this amazing unplanned ANSI SQL capability at: http://www.tdan.com/view-articles/11069. 

As you may know, IBM, Oracle and Microsoft SQL Servers have not solved the relational and XML data integration problem. Because of this the SQL vendors have all gone off in different proprietary directions becoming incompatible with each other even though SQL/XML functions have been standardized. Additionally, these SQL/XML functions are XML centric and require XML knowledge to code procedurally often requiring looping similar to XQuery looping structures. All of this is unnecessary by utilizing ANSI SQL’s inherent hierarchical processing. By naturally elevating ANSI SQL processing to a full hierarchical level, relational and XML data are integrated naturally at a lossless hierarchical level, solving the relational/XML integration problem. 

My partner and I have seamlessly extended ANSI SQL’s full and correct hierarchical multipath processing to support transparent native XML hierarchical processing by using a middleware XML enabler prototype that operates on top of the customer’s SQL processor. This enables the customer’s SQL processor to transparently support full hierarchical and XML processing. This is because ANSI SQL structured processing of hierarchical XML structures requires no user navigation or knowledge of the data structure enabling use by nontechnical users. This is possible because structured hierarchical data is unambiguous and is well behaved in its processing. This processing automatically supports full multipath processing and it is correctly performed. This is also important because the nonprofessional user without knowledge of the structure may be specifying a simple query that is accessing more than one pathway without any way to know this. Multipath queries allow any query to be specified within the processed structure’s multipath range without imposing any limitations. 

This hierarchical processing in the middleware is automatically structure-aware and allows many hierarchical capabilities such as automatic XML structured output. This dynamic capability is necessary since the ANSI SQL can dynamically join hierarchical structures dynamically creating a new combined structure which can be dynamically output in formatted XML that matches the dynamic result. 

As you can see, multipath processing is extremely powerful and easy to use. It also significantly increases the number of possible different structured queries possible increasing the value of the data. But the real power of multipath processing is that it dynamically increases the power of the query many times over by automatically utilizing the naturally occurring structure semantics that exist between the accessed pathways. For example, SELECTing data from one hierarchical pathway based on data values in another pathway. This is a more sophisticated query requiring additional structure semantic, but is still specified by the same simple SELECT, FROM, WHERE syntax. This opens the door to extremely powerful capabilities. 

ANSI SQL’s dynamic SELECT list also allows for very flexible dynamic control of the internal logic of the hierarchical query by dynamically allowing the addition or removal of output data types. This flexibility in dynamic execution is missing in XQuery. XQuery’s ability to process hierarchical structures was also severely limited by the decision of its designers to also support relational processing needed as a bridge from relational to XML data processing. This severely limits the hierarchical processing capabilities by having to support two different types of database processing (relational and hierarchical) at the same time. Our ANSI SQL prototype only supports hierarchical processing allowing it to know the hierarchical structure it is processing at all times allowing advanced hierarchical capabilities to be performed naturally. 

The new capabilities mentioned above are many and very significant for having a single ANSI SQL solution that solves all of the existing SQL/XML database integration problems today. To operate our online interactive ANSI SQL Transparent XML Hierarchical Processor prototype and to see our documentation visit: www.adatinc.com/demo.html. You will also notice a lot of additional hierarchical processing capabilities in the prototype that can launch XML into the next generation of multipath processing and accuracy for database processing. 

You may be wondering why we have not taken this technology directly to the SQL vendors. We tried. This technology is too disruptive for them to utilize. This makes it too costly for them to change their technology. In addition, vendors like to be able to differentiate their products and technologies from each other and a single solution would prevent this. The only way to make this better more advanced and accurate technology available is to make our product available on the market which we are working towards. Any help would be appreciated. If our technology is better than the Big Three SQL vendors, they will have to come around and offer it too. 

The multipath hierarchical processing that has been described is not new; it was utilized three decades ago when hierarchical databases were popular. It has been forgotten (lost knowledge, no Internet back then to preserve it) and now that XML hierarchical structures are back in use, it is not being fully utilized. So this multipath hierarchical processing is not untested. Its use has been fully proven and already tested. Now that it is automatically available through ANSI SQL, it just makes very good business sense to utilize and leverage this powerful natural capability. For more information on ANSI SQL Hierarchical Processing of XML see: http://www.devx.com/xml/Article/39183/1954 

XQuery relies on the user to specify the hierarchical processing logic along with the hierarchical logic that builds the XML result. Both must be hierarchically correct and kept in semantic sync to get a valid meaningful answer. This hierarchical processing can be quite tricky.  There is no control in XQuery to make sure that the XQuery user specified processing follows hierarchical principles to assure the correct result. XQuery is also not aware of the structure it is processing to help assist with user processing. This structure-aware processing is not possible because the structure being processed may not even be hierarchical even though the result is usually structured XML. The problem is that XQuery was designed to serve both as an XML processor and a relational processor which weakens its processing capabilities for both types of data and being able to detect proper processing when relational and XML processing have different processing principles.

SQLfX’s ANSI SQL transparent XML processing can perform correct hierarchical processing from input, through processing to output when it is operating hierarchically. Standard SQL operates hierarchically correct by following the hierarchical relationships that make up the hierarchical structure being processed and. Because SQLfX insures that only hierarchical modeled logical relational and physical XML data structures are processed by SQL, it is always structure-aware insuring the hierarchical processing results and automatically formatted XML output are always correct. This also enables the processing of more powerful hierarchical processing capabilities. Try our ANSI SQL Transparent XML Hierarchical Processor below:

                       www.adatinc.com/demo.html

Relational processing succeeded because there was one single proven vision. Object Oriented processing never fully succeeded because all the vendors rushed off in different directions. This is what has happened to today’s XML and SQL/XML database processing with no solid single set of principles or procedures being enforced. While there was the development of common ANSI SQL set of standard SQL/XML functions, it was  already too late. IBM, Oracle and Microsoft had already branded their different solutions and were still incompatible with each other. This is understandable, because differentiation drives the Big Three, but at a cost to the customer. 

Another reason for the above problems is that they are all limited and based on a relational flat linear mindset. Hierarchical data structures are extremely powerful nonlinear data structures that represent many times the data value then actually collected. This is made possible by the natural semantics that automatically exist between paths that are added as the hierarchical structure grows naturally. Every hierarchical path is related to every other path. Unfortunately all the current solutions ignore this extremely powerful semantic processing opportunity and limit query processing to single paths. Part of this is due to XML product designers unwavering requirement of procedural user navigation. Breaking away from this mindset to a much more powerful, easier to use and correct nonlinear hierarchical processing requires the use of nonprocedural access which is possible with database XML which is more fixed than markup XML. Multipath queries are just too complex to perform using procedural navigation. 

Politics also helped limit XML hierarchical processing by forcing relational capabilities into XQuery in order to be more like SQL’s SQL/XML solutions and to offer a migration path from SQL to XQuery. Instead of XQuery being a dedicated hierarchical processor, it was side tracked into supporting both relational and hierarchical processing. This can be seen in the fact that XQuery’s main join operation is the relational inner join. The inner join operation destroys hierarchical structures. This means that the operation of XQuery can not be counted on to perform strict hierarchical processing. The powerful advantages of hierarchical processing are severely weakened by this and can not be counted on. 

Academic projects are trying to add multipath, schema-free processing to XQuery with the introduction of Lowest Common Ancestor (LCA) functions. These LCA functions are very limited, still require the user to be knowledgeable of XML and additionally require the user to be knowledgeable of complex LCA processing to use properly. ANSI SQL performs LCA processing automatically and is not limited in any way; it can handle any number of hierarchical paths being accessed in a query to produce the correct meaningful semantic result.  For a hands-on test, see our online ANSI SQL transparent XML hierarchical processor prototype at:

         www.adatinc.com/demo.html.