I had another interesting problem when an application framework we’ve been working with came out with a new version. Before, a value relating to the application user’s “Position Name” (i.e. Manager) was a user-entered value. In the new release, this value was supposed to come from an XML configuration. Our client had already entered over 200 different positions and understandably didn’t want to take the time to hand enter each one into this new configuration. I found out that DB2 has the XMLSERIALIZE() function and decided that this would be the easiest way to accomplish this without hand-typing each position name back into the system.
As mentioned above, in the original version of the framework the position name was typed in, but in the new version the configuration would be populated in a drop-down. Plus the configuration XML added two new elements that were obviously not present before. So the technique I used capitalized on some of the knowledge I gained working out the Vacuum of Columns. It turned out to be a simple select with two “dummied” columns.
select distinct XMLSERIALIZE(content XMLELEMENT(name "Position_Name", XMLELEMENT(name "Value", system_users.position_name), XMLELEMENT(name "Dollar_Limit", CAST(NULL AS VARCHAR(1))), XMLELEMENT(name "Level", CAST(NULL AS VARCHAR(1)))) as VARCHAR(120)) as "Result" from system_users
You can see that I used the cast(null as ___) technique from my earlier post to make sure the element tags existed even though they were empty, so that I didn’t have to put those in by hand either. I executed this in SQireL and just cut and pasted it into the configuration and voila! All done with little fuss and less muss.
This particular example was on the verge of not being worth the time it took to figure this out, but I again learned something that may be extremely handy in the future, and it might save someone else some time, too. For more information, here is the article on IBM developerWorks :
http://www.ibm.com/developerworks/data/library/techarticle/dm-0511melnyk/