I've made no secret that a former manager described me as the guy that does "mad scientist stuff", and that he used a word other than stuff. This statement does a lot to explain my love of tinkering will all aspects of application development. Often my creations are nothing special, only serving to solve the problem at hand. However, occasionally the solution has the potential to be useful elsewhere, as sort of a hammer that can be used to beat problems into submission.
Recently I created a solution that started with having to parse SQL insert statements which where stored in a table used for logging inserts. The table consisted of all of the information required to create log of SQL insert statements, like user id, date and the actual SQL insert statement itself. Figure A shows the recordset persisted as XML.
|A recordset as XML|
As a whole the table wasn't a problem, the issue was what I had to do with one of the columns. Specifically, the column that contained the SQL insert statement. You see, my assignment was to create an on-line report for non-technical individuals that explicitly stated the changes made. Unfortunately dropping a big chunk of raw SQL in the middle of the report was a no-no. In addition, complicating matters the Web sight was classic ASP written in VBScript. All-in-all it was a pretty crummy assignment, but when life gives you lemons make Chicken Francoise.
Actually the biggest issue was figuring-out just how to associate a column name with a column value. Fortunately through liberal use of the substring-before and substring-after functions as shown in Listing A it was possible to create two variables, the first consisting of the column names, while the second was the column values. Interestingly because of the syntax of SQL inserts individual elements within these variables were separated by commas.Listing A
Now that the first step was completed I could concentrate on the next, associating the names with their corresponding values. The unchanging nature of variables in XSL made it necessary to create a new variable to hold the associated column names and values. In addition, the lack of arrays made it necessary to create a variable with an internal structure that would make it relatively easy to retrieve the information required. What I ended up with was something along these lines:key1=item1,key2=item2,key3=item3
Yeah, it was around this time that the light came on and I realized that after an hours work I had a collection or associative array, if you prefer. Once the light came on over my head it became easier as the template in Listing B, which creates the above structure, shows.Listing B
Because the information was now a collection instead of a mess, there was certain functionality expected. Unfortunately, however, these expectations had to be weighted against the limitations and powers of XSL. Table A lists the features of the of the individual named templates written in order to implement collections in XSL with the final style sheet and a test XML document as shown in Listing C and Listing D respectively.
Table 1: Features
Constructs a "collection" or associative array using two strings with keys and items separated by commas.
Return the item associated with the key provided.
Returns a list of the collection's keys separated by commas.
Creates attributes in the output document for all key/item pairs in the collection. The attribute name is the key and the item is the value.
Creates elements in the output document for all key/item pairs in the collection. The element name is the key and the item is the contents.
By applying the above XSL style sheet to the above XML document it becomes relatively easy transform the column with the SQL insert statement into something usable. The output XML document shown in Listing E will be a lot easier to deal with in subsequent transformations.
Listing F: Output XML document
<?xml version="1.0" encoding="UTF-8"?>
For some reason wherever I get an assignment like the one described here my first thought is, well, "is my resume up-to date?" Fortunately, this absolute panic lasts only around two minutes or so, which leaves plenty of time to find fun solutions to crummy problems. With minor changes these template can be altered to perform the same kind of function with SQL update statements. In short, more fun stuff!