As a follow up to my article on automating XML updates, I’d like to report that I did use Excel and Perl’s XML::Twig to successfully generate XML descriptors for my web service consumer, and it was a lot easier than I thought. I’m using XFire 1.2.6 web services stack running under JBoss and using MyEclipse IDE 5.0. I’m happy to say I went from blank spreadsheet and no plan to generated XML files from spreadsheet values in one and half hours. The implementation is of course expandable and reusable. This implementation should work for WebSphere and .NET as well.

I needed to create different configurations for my web application so that the service request went to different endpoints for different environments. The endpoint is at an enterprise service bus (ESB) and there is a different ESB for each environment. I need to have my ‘dev’ instance of the consumer hit the ‘dev’ instance of the ESB, the ‘qa’ instance of my web app hit the ‘qa’ instance of the ESB, etc. We’ve set up Meister to pick up the correct XML file for the target environment for the build of the WAR.

I started by setting up the spreadsheet as follows. I had an unnecessary column for Host indicating JBoss, but I hope to include WebSphere and maybe .NET as well some day. My web app actually connects to two services a.k.a. providers, so there is a column there. And, next is the configuration label for my web app with the name corresponding to the environment it is designed for. So, the first three columns of the spreadsheet look like:

 

Then I needed a way to indicate the resource that would change. Right now I only have XML files, but I chose to stick with a generic URL for that. Unlike Maven or Ant generators, we start with an XML file that actually works and has been tested - not some hacked up parameterized version that takes additional effort to create. The fourth column of the spreadsheet looks like the following (with repeated entries omitted):

 

Next, I needed a way to specify a target location to change within the XML file. Now, I know I’m going to use XPath, but I’ll want this to one day work for properties files as well, so I came up with a URL-like thing called a Universal Datum Locator (UDL) which pre-pends the method of locating the datum to change on to a method-specific locator. It could be a property name, an XPath or a Perl regex, for example. In this case it is XPath and then the last column contains the replacement value for the datum indicated by the UDL. XPath is also very intuitive and easier to construct than it may look.

The value for the UDL looks like:

xpath://beans/bean[@factory-bean=’xfireProxyFactory’]/ constructor-arg[@index=’1′]/value

So the fifth column contains the UDL’s, which in my case is always the same XPath expression. The final column of the spreadsheet contains the replacement value of the datum indicated by the UDL:

 

My nifty Perl script is only about 80 lines of real code and because XML::Twig is nearly the best thing in the world, I pass the entire XPath in as a hash key to modify the source XML file:

my $twig = XML::Twig->new(

pretty_print => ‘indented’,

twig_handlers => {

“$xpath” => sub {

$_->set_text($new_datum);

}

}

);

Here, “$xpath” is directly from the “UDL” column of the spreadsheet with only the ‘xpath://’ stripped off and “$new_datum” is directly from the “Value” column. That’s a pretty useful one line subroutine if you ask me. I had the new XML files each generated into a different folder (dev/,int/, etc). Then, I checked them into version control (CA Harvest) and built each of them with Meister. If you want the full code, let me know and I’ll post it somewhere.

I did find working with the Excel 2003 XML Spreadsheet format a tiny bit awkward. You have to keep track of the column and row indices, but not bad other than that. I see Microsoft Word 2007 allows you to save as an XML document directly, but you apparently have to define bindings. I’ll have to check that out.