Adding transformations

When the structure of the source data is different than the structure of the target data, transformations are required. With the mapping editor, you can add transformation functions to each mapping line. The following example shows how to add a string concatenation function to the mapping model.

1. Create a new Mapping Model (as shown in Figure 8) Student_BookLoansInfo.msl with new library system.dbm as the source and Student_BookLoansInfo.xsd as the target.
   
   
   
   
2. Create mappings from STUDENT.STUDENTID to the element Student_ID, BOOK_LOANS.BOOKID to BookID, and STUDENT.NAME to Name.
   
   
   
   

3. You want to use the target XML element BookInfo to store book title and the publisher name. In order to do so, create a mapping from BOOK.TITLE to BookInfo.
4. Select the mapping line from BOOK.TITLE to BookInfo, and also select the column BOOK.PUBLISHER_NAME on the source side (keep the CTRL key pressed when selecting the element).
5. Right-click on the mapping line, and select Add to Mapping from the context menu.

6. This will result in two mapping lines connecting from the source to the target element. Right-click the mapping line, and select Transform > Add from the context menu.

7. The Expression Builder dialog comes up to assist with building the transformation. It shows the source columns that are available for the transformation on the left-hand side. On the right, the Expression Builder shows a list of common SQL transformation functions. To add source columns and transformation functions to the transformation expression text box, double-click on them.

8. For the BookInfo target element, you want to concatenate the title and the publisher name. In between, you want to add a forward slash (/) as a separator. For the concatenation, use the DB2 SQL function concat( String1, String2). This DB2 SQL function is not a standardized SQL function -- that's why it does not show up in the list of available functions.
9. Manually add the following transformation into the transformation expression text field:
   
   

CONCAT( CONCAT( SCHOOL.BOOK.TITLE, ' / ' ), SCHOOL.BOOK.PUBLISHER_NAME )

10. Generate the SQL/XML query. Note that the transformation function was added to the query.

11. Execute the query. In the result (see also Ref_StudentBookLoansInfo_result.xml) you'll see that the book title and the publishers name were correctly inserted into the XML document.
    
    

...
<student>
	<Student_ID>606789032</Student_ID>
	<Name>Jessica Wong</Name>
	<BookID>0-06-0522003        </BookID>
	<BookInfo>C++ Crash Course / Westly Publishing</BookInfo>
</student>
...

9 of 13 | Previous | Next

Comments

Back to top

Table of contents

• Before you start
• Scenario overview and problem description
• Create a simple mapping model
• Generating the SQL/XML query
• Execute the generated SQL/XML query
• Modifying the target XML schema
• Joining data
• Working with mapping groups
• Adding transformations
• Summary
• Download
• Resources
• About the author

---

9 of 13 | Previous | Next

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.