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
. 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
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
to the InvID
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
Note that the query does not SELECT
node, causing the Cust
nodes to be removed and promoting the Invoice
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.