TechNotes
Logic in DB
Beyond Java
Data Access
SOX
SQL/XML
Restructuring
Trees
Open Source
BizIntel
MySQL
Drivers
ODBC
JDBC
OLE DB
.NET
Podcast
SQL:2003
MS SQL 2005
XML DBs
XQuery
Webcast
XQuery
SQL:2003
MS SQL 2005
|
|
<<Prev 1 2 3
4 5 6 7 8
9 Next >>
Heterogeneous Hierarchical Structure JoiningIn this third SQLfX® beta example, the previously defined EmpView, is joined over the previously defined
CustView between their Emp and Cust nodes as indicated by the ON clause join condition in Example 3. Notice how simple and hierarchically conceptual it is using a
single Left Outer Join of SQL views to combine hierarchical structures in the same basic way the EmpView was constructed. It also demonstrates a seamless
heterogeneous join combining relational and XML structures seamlessly; all view types operate the same regardless of their data source type.
|
 Fast, reliable data access for ODBC, JDBC, ADO.NET and XML |
Notice in the result structure of the hierarchical join in Example 3, that the CustView root node Cust linked under the Emp node was placed after Employee’s Eaddr node
because it was the last sibling node added under Emp. This is the default because sibling nodes are added to the structure being built in a left to right order. Sibling
order in hierarchical structures has no semantic importance, except possibly when procedurally navigating the structure when using a procedural XML tool. The Eaddr node
indicated by the dashed box was not included in the result hierarchical structure because it had no data fields selected for output. Unselected nodes are sliced out of
the structure and any descendent nodes are promoted around them. There were no descendent nodes in this example.
|
Example 3: Heterogeneous nonlinear hierarchical join
The entire combined unified nonlinear hierarchical
structure was hierarchical filtered by the WHERE clause. The WHERE clause, unlike the ON clause, operates on the entire processed structure, this is standard ANSI
SQL. This causes “Inv02” to be directly qualified along with all other related node occurrences. All other unqualified nodes were removed (filtered) from the result
structure. The XML result can be verified by comparing it against the XML output structure values from Examples 1 and 2. Notice how “Inv01” filtering removal caused
the removal of the “Emp02” node and all of its dependent nodes, while “Emp01” and its depended nodes are preserved because it also contained “Inv02” which was
preserved because it was qualified on the WHERE clause. This hierarchical operation follows the way relational processing normally operates on the working rowset when
it is hierarchically modeled using the Left Outer Join.
SELECT DpndID, CustID, EmpID,
InvID, AddrID
FROM EmpView LEFT JOIN CustView
ON EmpCustID=CustID
WHERE Invoice=”Inv02” 
<root>
<emp empid="Emp01">
<dpnd dpndid="Dpnd01"/>
<cust custid="Cust01">
<invoice invid="Inv02"/>
<addr addrid="Addr01"/>
</cust>
</emp>
</root>
The simple SQL query above in Example 3 is joining two full nonlinear structures hierarchically together by using their global views. Notice that no procedural
navigation is performed in the request, the selected data items are specified in any order without regard to the structure, and the output hierarchical structure is
automatically assumed from the processed structure. The WHERE clause filtering automatically and hierarchically filters the hierarchical combined result, and all
internal processing is performed using fully principled hierarchical processing producing a correct hierarchical result that is also relationally accurate. This is
possible because SQLfX® is structure aware, always knowing the exact structure of the hierarchical structure being processed.
<< Prev 1 2
3 4 5 6 7
8 9 Next >>
Database Server Watch
SQL Summit Home
Page Articles
© 2008, Ken North Computing LLC, All rights
reserved.
|
|
|
