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
