Creating the SchoolXML2Rel Transformation
To transform XML to relational data
1. Create a folder called Transformations.
2. Right-click the Transformations folder, and select New Transformation.
3. Select Any-Any Transformation, and click Next.
4. Type SchoolXML2Rel, and select Finish.
The transformation editor opens showing an empty transformation.
5. Expand the data source File_SchoolXML, and drag school.xml to the transformation design area.
6. Completely expand the data nodes (the little triangle to the right of school:Class[1-*]) in the school_xml object to see the entire structure.
7. Drag a loop object from the Transformation Editor palette onto the design area.
Use a Loop Link to connect the input sequence school:Class[1-*] to the loop source.
8. Select Loop Link Mode on the toolbar.
9. Click and drag to connect the output of the school:Class [1-*] element of the school_xml file to the source input tab of the loop object.
10. Drag another Loop Link from the input sequence school:Student[1-*] to the loop. When the words “insert source” appear, release the mouse button and a new loop source is created.
11. Create an output cursor. Right-click the out block and click Add Parameter.
12. Select Complex > CURSOR.
A cursor is added to the out block.
13. Rename the cursor to ClassList.
14. Use a Loop Link to connect the loop object’s Student output handle to ClassList input handle of the out object.
Doing so ensures that the output rows are based on the bottom level of the XML input hierarchy. To connect to the ClassList handle, make sure that when you release the mouse, ClassList[] is highlighted and “insert param” does not appear. If “insert param” appears, you will connect to a newly created parameter instead of ClassList.
15. Expand the school:Teacher node on the loop, so that you see all the columns.
16. Select Assign Link mode by clicking the button on the toolbar.
17. Drag the Assign Link lines from each of the loop’s individual data items to the out box. Release the mouse button when the words “insert param” appear, so that a new parameter is created.
The connection lines are red, the next few steps will change that.
18. Make sure that ClassList[] is at the top of the list. Select ClassList[] and use the toolbar arrow to move it to the top.
19. Select each field under ClassList[], and use the toolbar Move In button to indent.
The red connection lines vanish, because the newly indented columns are now a different category of data, belonging to the ClassList[] cursor. Their names and types are now defined. All that remains is to reconnect them.
20. Connect all the bottom-level loop fields to the corresponding out fields, and the design is complete.
21. Save the transformation and execute it.