Converting Relational to Hierarchical Data Tutorial
Relational data is typically saved as tables or views in a relational database. Hierarchical data is typically saved as XML data, and sometimes as tables or views in a hierarchical database such as IMS.
This tutorial uses three of the tables shipped with TDV from the Examples project (customers, orders, and orderdetails) to produce an XML document result.
Adding the Views to Convert
Add three of the tables shipped with TDV from the Examples project (customers, orders, and orderdetails).
To convert relational to XML data
1. Open Studio.
2. In the Studio resource tree, select Shared.
3. Right-click and select New Transformation.
4. Select Any-Any Transformation and click Next.
5. Type TBL2XML in the Transformation Name field.
6. Click Finish.
The Transformation Editor opens with the Model tab displayed.
7. From the Studio resource tree, expand Shared/examples.
8. Expand Shared/examples/ds_orders.
9. Select the following views and drag them from the resource tree onto the Transformation Editor Model tab:
— customers
— orders
— orderdetails
10. Save your transform.
Adding the Loops and Filters Used to Iterate and Select Data
This example makes use of programmatic loops to iterate through and select the data to be transformed.
To add loops and filters
1. Select the Loop Link Mode. Alternatively hold SHIFT key while creating a link (Shift+Minus) icon at the top of the editor.
2. Select the operation handle next to the result column that is listed in customers and drag the line to out.
3. Save the transform.
4. Delete the connection between loop and out.
5. On the out container, select the operation handle next to customers and right-click.
6. Select Delete “customers”.
7. Click out to select it.
8. Right-click out and select Add Parameter.
9. Select Browse.
10. Browse to XMLdefinitionSet.
11. Select Type in the Show drop down list on the right.
12. Select Customers from that list.
13. Click OK.
By default, the XML hierarchy is collapsed.
14. Click the arrow next to foo:customer, then foo:orders, and foo:orderitems to expand the hierarchy.
15. Click the Assign Link Mode. Alternatively hold CTRL key while creating a link (CTRL+Minus) icon at the top of the editor.
16. From loop, click in the operation handle next to customers and drag the line to foo:customer.
17. Save your transform.
Completing the Links Between Loop and out
To complete loop links
1. Click the Assign Link Mode icon at the top of the editor.
2. Link CustomerID to foo:customerID.
3. Link CompanyName to foo:CustomerName.
4. Save your transform.
Adding Loop2 to the Model
To add loop2
1. Select the Loop Link Mode.
2. Click in the operation handle next to the result column of the orders table and connect it to the foo:orders column of out.
3. Double-click loop2.
4. In the field, type the following:
CusID = orders/CustomerID
5. Click Close.
6. Save your transform.
Adding Loop3 to the Model
To add loop3
1. Select the Loop Link Mode.
2. Link orderdetails result to foo:orderItems.
3. Double-click loop3.
4. In the field, type the following:
OrdID = orderdetails/OrderID
5. Save your transform.
Adding Links to Direct Data Flow
Linking data on certain columns helps direct the flow of data and help select the data for transformation.
To add links
1. Open your transform.
2. Validate and or create links between the following From and to columns.
From Operator | From Column | To Operator | To Column |
loop | customers | out | out.foo.customer |
CustomerID | out | out.foo.customerID |
CompanyName | out | foo.customerName |
CustomerID | Loop2 | CusID |
loop2 | orders | out | out.foo.orders [ ] |
OrderID | out | out.foo.orderID |
OrderID | loop3 | loop3.OrdID |
loop3 | orderdetails | out | foo.orderitems [ ] |
OrderDetailID | out | foo.orderitemID |
3. Save your transform.
Executing the Transform
When you execute a transform the convert relational data into XML structured hierarchical data, you can use the results to get an idea of what would be produced at runtime.
To execute the transform
1. Execute your transform to review the generated XML code. (Click the execute button.)
2. Click Details on the Results panel to view the Value window.
3. Click OK to close the Value window when you have finished.
4. Save and close your transform.
