Performing Hierarchical Restructuring Using ANSI SQL : Page 2

Basic Restructuring

Hierarchical structure transformation as described in this section is known as "structure restructuring," or simply as "restructuring." You restructure physical or logical hierarchical structures by carving out separate fragments and then reassembling them differently. Pulling out separate fragments involves logically duplicating the structure by assigning different names or prefixes so the structures can be identified and accessed separately. This lets you retrieve multiple fragments that can be manipulated independently, thereby transforming the original structure. Note how simple and intuitive it is to specify the SQL query that creates the structure in Figure 2, and the hierarchical power it demonstrates.

Decomposition and transformation of logical and physical data structures is possible with standard SQL using a combination of fragment processing and SQL alias processing, letting a query access the structure view independently multiple times. Figure 2 uses the StoreView view as the single source for two separate fragments (the dotted circles in Figure 1). These reflect the data that comprises the CustView and EmpView. Essentially, these fragments decompose the StoreView and then remodel it, reshaping the data model. The reshaping operates by hierarchically re-joining the structure fragments (usually differently) based on other data value relationships in the structure.

While logical structures are free to be modeled in many different ways, physical input structures must be modeled to reflect the external structure being accessed. The StoreView in Figure 2 simply models a hierarchical structure—it could be either a physical XML or logical relational structure for this example—both are represented identically in the rowset. The example demonstrates that you can also rearrange a physical structure by separately selecting fixed fragments from the rowset containing it, and then independently join them into the main structure (unified view) with the flexibility offered by logical structures. Using the alias feature, you can give this new table or view a name and then reference that name in the SELECT clause as a high level prefix. Using the prefix enables you to target the exact object you want to reference, because the renamed objects may have the same name in both objects.

Using Alias and Structure Restructuring in a View

Figure 3. Static Restructuring: This is the structure you get by selecting all fields from the Transform view.

The example in Figure 3 demonstrates a number of SQL capabilities applied to XML transformation processing. First, you could place its restructuring SQL query (shown earlier) in an SQL view for easy invocation and to demonstrate its flexible use in abstractions. Second, the column names InvID and AddrID can be aliased to create names reflected in generated XML. Here's the previous example placed in a view that includes the new alias names:

   CREATE View Transform AS
   SELECT SV1.EmpID EmpID, SV1.DpndID DpndID, SV2.CustID 
          CustID, SV2.InvID Invoice, SV2.AddrID Address
   FROM StoreView SV1 
   LEFT JOIN StoreView SV2 ON SV1.EmpCustID=SV2.CustID

The preceding SQL assigns the aliases Invoice and Address to the InvID and AddrID columns. These new names would be reflected in XML generated from the resulting structure.

At this point, the transformation has been stored in an SQL view named Transform that you can invoke easily and use like any other view. You can ensure that it's working by issuing a SELECT * FROM Transform query, which results in the structure shown in Figure 3. Later examples will demonstrate how to modify the transformation view dynamically when it's invoked.

Restructuring Views with Dynamic Output Modification

Figure 4. Dynamic Restructuring: The query does not select the Cust node, which removes them and promotes the Invoice and Address nodes.

Most XML transformation procedures today are static, so they must be modified to include or exclude a value in the output XML structure. A SQL-based transform does not need statically placed XML formatting operations because it uses a dynamic SELECT list, allowing simple and dynamic data item output inclusion or exclusion as in Figure 4, which uses the following SQL query:

   SELECT EmpID, DpndID, Invoice, Address 
   FROM Transform

Note that the query does not SELECT the Cust node, causing the Cust nodes to be removed and promoting the Invoice and Addr nodes around them, changing the already transformed structure. You can modify the dynamic SELECT list from query to query without changing the transformation logic contained in the Transformation view. This provides and incredibly powerful dynamic and reusable operation that's both flexible and easy to use.

Previous Page

Next Page