Performing Hierarchical Restructuring Using ANSI SQL : Page 3

Changing Leg Order and Replicating Nodes

The next example (see Figure 5) starts with the full StoreView data structure from Figure 1 and transforms it in a number of ways. The StoreView view used in Figure 5 is composed of two views, CustView and EmpView. This transform operation breaks the CustView out of the StoreView by isolating each of its nodes. Having done this, it reassembles the Cust view and deliberately swaps its Addr and Invoice legs around by altering the order in which the joins are performed, which works because the structure is built from the top down, and left to right. You can see this change by comparing the result structure to the previous examples.

Figure 5. Changing Leg Order: Using several joins, this query extracts the Cust view from the StoreView and then reassembles it, swapping the Address and Invoice legs and adding an additional Invoice node under Addr.

   SELECT Cust.custid, Invoice.invid, Invoice.invcustid, Addr.addrid,
          Addr.addrcustid, invoice2.invid NewInv
   FROM Storeview Cust
   LEFT JOIN StoreView Addr ON Cust.custid=Addr.addrcustid
   LEFT JOIN StoreView Invoice ON Cust.custid=Invoice.invcustid
   LEFT JOIN StoreView Invoice2 ON Addr.addrcustid=Invoice2.invcustid

The replicated data caused by the combination of the EmpView and Custview data is automatically removed when the output data is converted to XML. In addition, the transformation selects an additional Invoice node and places it under the Addr node in Figure 5.

The previous restructuring examples used fragments to move data around. In contrast, this example operates at the node level. For complete control, the node level works well, but fragments are generally easier to work with, and also offer an important capability that node level does not. Often nodes in a group are already in the structure desired and the group may no longer contain the values (i.e. foreign keys) that would enable you to rejoin them. Fragments offer a solution because you can move them as a single group that remains intact as shown in the next example.

Restructuring Using Below-the-Root Linking

This next example links two multipath structure fragments by linking them below the root of the lower fragment. The example demonstrates how such linking opens up many more opportunities for combining fragments. The question is, is linking below the root valid, and if so, how is it handled semantically? It turns out that it is valid because it operates on the same principles as "linking below the root" described in the previous article in this series. The root of the lower structure still remains the hierarchical modeling link point.

Figure 6. Restructuring using Below-the-Root Linking: Even though this query relies on the Cust root node, that node isn't selected for output.

The SQL example below demonstrates isolating two fragments represented by the prefix "SV1" for the Emp fragment and "SV2" for the Cust fragment. The query links the fragments by matching the EaddrID and AddrID data values in the Eaddr and Addr nodes. This is allowed, and the Cust root of the lower level fragment is used as the data modeling entry point as shown (see Figure 6).

   SELECT SV1.EmpID, SV1.DpndID, SV2.InvID, SV2.AddrID
   FROM   StoreView SV1 LEFT JOIN StoreView SV2 
          ON SV1.EaddrID= SV2.AddrID 

Interestingly, the example also shows that despite the query's reliance on the Cust root node, that node does not need to be specified for output. This changes the question to "How is the Invoice node handled?" because Invoice is only indirectly related to the Addr node—through the common higher level node Cust node that is removed for output. The answer is that internally, the lower fragment is hierarchically combined with the upper fragment from the Eaddr node to the lower Cust root node. Then the unselected Cust and Eaddr nodes are removed and node collection takes place, connecting the Invoice and Addr nodes to the next higher level Emp node. The concept of node collection was also explained in the previous article in this series. This example demonstrates both the power of combining hierarchical operations and shows how they maintain the validity of hierarchical semantics.

The dashed arrow in Figure 6 represents the data linking between the two fragments, while the solid arrow represents the resulting structure.

Previous Page

Next Page