I mentioned in an earlier weblog that Access supported XSLT transformations on import and export through the GUI, but not through VBA. Here, extracted from something I wrote for Access Hacks, is a solution, if an imperfect one.

Since the ImportXML and ExportXML routines don’t offer an XSLT transformation along the way, you have to take an extra step, reparsing an XML file, transforming it, and saving the transformation out to disk. Here’s what the key Transform function looks like in VBA:

Private Sub Transform(sourceFile, stylesheetFile, resultFile)

Dim source As New MSXML2.DOMDocument30
Dim stylesheet As New MSXML2.DOMDocument30
Dim result As New MSXML2.DOMDocument30

' Load data.
source.async = False
source.Load sourceFile

' Load style sheet.
stylesheet.async = False
stylesheet.Load stylesheetFile

If (source.parseError.errorCode <> 0) Then
   MsgBox ("Error loading source document: " & source.parseError.reason)
   Else
If (stylesheet.parseError.errorCode <> 0) Then
      MsgBox ("Error loading stylesheet document: " & stylesheet.parseError.reason)
   Else
      ' Do the transform.
      source.transformNodeToObject stylesheet, result
      result.Save resultFile
End If
End If

End Sub

To use it on an import, you could replace this:

Application.ImportXML "http://simonstl.com/ora/updateBook.xml", acAppendData

with this:

Transform "http://simonstl.com/ora/updateBook.xml", _
    "C:xsltattsToElem.xsl", _
    "C:temptempImport.xml"
Application.ImportXML "C:temptempImport.xml", acAppendData

On an export, you add a transformation step after the export:

Application.ExportXML acExportTable, "books", "C:temptempExport.xml"
Transform "C:temptempExport.xml", _
    "C:xsltbooksToHTML.xsl", _
    "C:exportexportedBooks.html"

Maybe Microsoft will get around to adding this functionality to future versions of Access so we can drop the overhead of saving to disk and reading it right back out, but for now, this opens up a lot more possibilities for using XML with Access. It also works in Word and Excel, but isn’t as necessary there.

(And there’s lots, lots more in Access Hacks that’s far cooler than this little workaround.)

How many wheels have you reinvented in VBA?

Related link: http://blogs.msdn.com/richardt/archive/2005/03/23/401146.aspx

Richard Turner, a Program Manager at Microsoft, had recently posted some questions regarding the usefulness of an ESB once Indigo is fully baked, and the WS-* stack becomes fully mature. Unfortunately his blogging system doesn’t allow any response to be posted after a certain amount of time has passed, so he and I thought it would be best for me to post my comments here and he could point to it from over there….

Hi Rich,

I see that others have jumped in on your blog to help answer your questions regarding ESBs. I have a few more clarifications to add -

<Rich> “David Chappell of Sonic Software has written a good, albeit very Java centric book on ESB which I guess not surprisingly leans heavily towards discussing the notion of ESB from the context of the technologies available from Sonic and the Java platform.” </Rich>

Thank you for the complement on the book being well written.

Regarding your comments on the book being Java-centric… It also happens that all ESB implementations that I know of to date are built on some kind of Java technology somewhere. That happens to be an implementation detail. The book outlines the architectural components that make up an ESB. In the intro of the book I state that any vendor may use this as a guide to build their own implementation. And that implementation can be built using Java, C#, Perl, or Python for all I care provided that it conforms to the architectural principals as outlined in the book. Even when using an ESB that is based on Java, it doesn’t mean that you need to be a Java programmer to use it, because an ESB is based on a philosophy that a SOA should be configured rather than coded, and when you do need to write code there are a variety of non-Java based technologies to use (more on this in a bit). An ESB can be used in an all-Microsoft environment if the associated deployment tools and management framework makes the presence of a JVM a byproduct of the installation process that is hidden from daily operations and use.

That being said – It’s a heterogeneous world out there, and it’s a fact of life that Java technology exists in many IT environments. Java represents a set of standard APIs and interfaces, many of which have reached popular adoption. If you want to communicate with things that are built in Java then it’s a darn good idea to support it if you are able to. I hope I get that across well in the book (I actually say it somewhere in there). As an example, JMS is a well established standard for messaging, with broad industry support. However, it is only one of the MANY ways to integrate applications together through an ESB. However in the ESB book I don’t mention it any more or any less than other standard technologies like XPath or XSLT. In fact, I have an entire chapter which generically discusses messaging concepts such as store-and-forward messaging. At the end of the chapter is a small section on JMS and another equal amount of ink devoted to WS-Reliability and WS-ReliableMessaging. WS-Rel* is a perfectly valid way of linking applications together through an ESB, and will be much more relevant as it becomes commercially available in other platforms (like yours).

While you mention the book (thanks!), I think your comments are more directed at the whole notion of ESB’s, so I’ll focus on addressing that. Gartner defines what is referred to as a “multi-protocol” ESB. In a multi-protocol ESB, interfaces that are implemented in Java, C++, COM+, or C# are also equally valid ways of getting on and off the bus. In fact, such an ESB can mediate between different protocols, API conventions, and invocation styles. An application written to use the .NET stack can participate in a SOA across the ESB by sending or receiving a message using WS-ReliableMessaging, or by calling an ESB-supplied C# client. Somewhere on another node of the ESB, a J2EE appserver may be plugged into the bus using WS-Rel*, or through a JMS interface using a MessageDrivenBean. Meanwhile, in another part of an organization, applications that are accustomed to doing nightly batch processing based on bulk dump, load, and transfer of data may be doing so through the ESB using an FTP or file based interface, and taking advantage of the ESB’s mediation capabilities to break up the bulk data into its constituent parts for consumption and use by other endpoints on the ESB (using the aforementioned protocols and interfaces.)

Also, I agree with your assertion’s that ESB’s should embrace the WS-* specifications. An ESB should be considered a highly sophisticated, intelligent implementation backbone for the WS-* stack of specs (more on this later.) BTW, the final chapter of the ESB book is about how ESB’s and the Web Services stack of specifications are destined to evolve together.

You also brought up the question of “How, for example, would I add mainframe nodes, COM+ nodes, Web Service nodes etc. running on different platforms and technologies on top of JBI/Sonic/ without the use of adaptors and translators?”

How indeed? I’m guessing that your question lies in a misconception that an ESB does not provide adapters. In Gartner’s definition of a “single-protocol ESB”, I suppose that’s true. However in my opinion I think an ESB that only supports one interface style is severely limited. In Sonic’s case (just as an example), we provide over 200 adapters for packaged apps such as SAP and PeopleSoft, and also for protocols such as FIX and SWIFT.

<Rich>To my mind, an ESB is smart-plumbing to which to attach dumb nodes.</Rich>

One of the benefits of using an ESB is that it can connect, mediate, and control the interaction between a variety of systems without forcing an organization to adopt a single interface style or technology. Applications may participate in a SOA through an ESB using a variety of interface technologies such as Web services and application adapters, and also bridge to not-so-modern transports such as WebsphereMQ, TIBCO RV, FTP, and SMTP. The applications that are brought into the ESB using the FTP or file based interfaces are blissfully unaware that they are now participating in a newer infrastructure, because they just continue to dump and load their files every night. That is a great example of your “Smart ESB, and dumb nodes” observation.

An ESB provides an abstraction layer which separates the business process definition from the underlying physical messaging layer, protocol transports, and service endpoint bindings. Because this abstraction layer is based on process definitions built using modeling tools and configuration via a centrally accessible remote management layer, an application may be brought into the bus using FTP or file based interface today, and migrated over to a more direct interface style such as Web services or specialized packaged application adapter tomorrow, without having to go back and re-tool the surrounding application endpoints that interact with that application, or having to redo the process definition, or in most cases without the need to recompile or redistribute any code changes. This allows applications to be brought into a broad-scale SOA at their own pace, in a phased approach that allows the flexible scheduling of the IT resources responsible for doing it. This is one of the many ways that an ESB supports incremental adoption of a SOA.

Lastly, you asked this really important question – “What value, therefore, is an ESB [When web services smarts are built into the endpoints]?

I think there has been enough already said here by me and others on this thread, but just in case its not clear let me summarize the main points –

- An ESB is configuration-driven. Process definitions, service configurations, endpoint bindings, routing channel definitions can be configured and re-configured without any coding, and without any downtime. This provides post-deployment adaptability with no disruption to operations, no need to take services off-line, and no need to reboot servers.
- Even when the endpoints being connected are “Smart Nodes”, an ESB provides value to the service invocations and message processing by enabling scalability in all directions. An ESB provides high-throughput, low-latency message based communication channels. Through transparent message server clustering, you can achieve increased capacity to handle any type of load of message based application data sharing and service invocation traffic where necessary. An ESB provides a scalable set of cooperating message servers which can be deployed across multiple platforms and managed through a single common management layer. These message servers, which act in cooperating software-based clusters, can be securely locked down in the DMZ and seamlessly route message traffic and service invocations to other message servers (using SSL and HTTPS tunneling with mutual authentication) and direct services both internally and externally. This creates a series of managed routes throughout your many firewall boundaries. As WS-RM becomes the prevailing standard for messaging, these message servers will become a scalable set of WS-RM SOAP message routers. Also, the independently scalable service hosting and invocation layer in the ESB architecture lets you dynamically deploy additional service instances to handle increases in processing load by the application and service endpoints. This lets you deploy and scale what you need for services independently of one another where and when you need them (see Ed Daniel’s link to my “ESB MythBusters” article excerpt). If the scope of you SOA scales up to hundreds or thousands of application and service endpoints, would you want them all to open up their own holes in the firewall and talk to each other directly, or would you want them to communicate through a set of well-defined tunnels? (See Figure below)

- If done well, the messaging layer of an ESB can provide continuous availability of the communication channels by providing automatic failover on server and communications channels, without disrupting operations or enduring manual recovery processes, and without requiring hardware-based clustering techniques.
- Stateful orchestrations can be combined with stateless itinerary based routing to provide a flexible model for implementing and managing self-directed business processes that can be highly distributed and capable of operating independently of other parts of the ESB, without the need for centralized servers that could introduce processing bottlenecks and act as a single point of failure.
- Secure access with global reach, where distributed processes and services may be defined and deployed to any location, from any location. An ESB allows the local autonomy of control over service configurations and their interactions, including access rights using local security domains. Because the ESB provides a purposed well defined conduit between services, yet provides loose coupling across all applications and services that are globally accessible across the bus, IT organizations can build SOA domains at a departmental or business unit level, yet selectively open up channels with other domains which include other business units, and extending into B2B collaborations with business partners. This can work seamlessly and reliably across Internet, satellite or other WAN links.

As web services smarts are built into the endpoints, then this will make ESBs even more practical for use. As Indigo is rolled out, for example, applications built on that platform will be by their nature message driven and better suited for loose coupling into an ESB. An ESB will still provide the mediation between disparate data formats and invocation styles, and provide intermediary services such as data transformation and content based routing in an independently scalable service model

I hope this clears a few things up. Feel free to give me a shout if you have any further questions.

Comments are welcome here from others as well. Please keep the Java vs. MS bashing stuff to a minimum. This is really about architecture.
Dave

In February 2005 OASIS formed the SOA-RM TC (Service Oriented Architecture Reference Model Technical Committee) and assigned it the responsibility of developing an SOA Reference Model. The reference model will not be tied to any particular implementation or set of standards but will define a shared vocabulary and identify the common elements of a service oriented architecture (i.e Service, Service Description, Service Advertising, Data Model, Contract etc).

The benefits of the reference model is that it offers a guide to people developing SOAs and provides a context for discussing and comparing SOA implementations. All good things in my opinion, especially with so many companies starting enterprise-wide SOA initiatives.

It will be interesting to read what the SOA-RM technical committee has to say about Web Services, since they are core to most SOA discussions. Personally, I would like them to address Web Services in the context of the WSDL, REST, SOAP, XML and WS-*, but this may be out of scope for the reference model.

There is a presentation about the group and its charter.

Related link: http://dubinko.info/blog/2005/04.html#perm2005-04-20_ublinput

An initial version of the UBL input specifications is now ready for interested parties to begin developing against.

What is this? A Part of OASIS’s continued work on the Universal Business Language. It comprises ‘everything a form author needs to know to become productive with UBL’ and is analogous to the earlier work on XSL output specifications done by the remarkable G. Ken Holman.

What does this mean? If you are in a position to do some forms work, be it XForms, classic HTML forms, PDF, InfoPath, OpenOffice, XML Spy, OpenLaszlo, Flex, Backbase, LiquidOffice, Microsoft Word, whatever, have a look at the spec itself, currently in wiki format.

I am coordinating various implementation efforts here.

Even if you’re not equipped for forms development, you might enjoy reading about this as a data-modeling exercise.

A future revision of these specifications, in a more conventional multiple-page HTML format, will be included as an informative annex to UBL. Now is the time to try these out and provide vital feedback.

Let me know if you have any questions.

Are you able to implement these specs?

While reading some retro computing books and doing some of my own Apple ][ hackery, I stumbled over a common thread between these old systems and the appeal I first saw in XML: users and developers can get up close and personal with the underlying technology.

On the Apple ][, as I’ve written before, programmers and users had direct access to the machine. The various command lines and interpreted BASIC weren’t separated from the hardware by much more than convenience. Everything was accessible, and developers (including the developers at Apple) took advantage of that immediate accessibility to perform strange tricks. (It’s remarkable how rarely my Apple programs crashed, considering the unprotected environment in which they ran.)

In XML, data and data structures are exposed as text. Anyone with a decent text editor and a bit of knowledge can go into an XML document and make whatever changes they like to it. You can break the document, making it not well-formed, but you can also extend it in ways that its original authors didn’t foresee but which are useful to you.

As it turns out, of course, a lot of people only bought Apple ][s to run programs on them, and a lot of people only use XML to trade information between applications, never tinkering with the data.

Still, having that option of tinkering opens up a lot of possibilities, if people care to take advantage of them.

(And yes, I’ve talked about implementing a simplified XML parser for the Apple ][. I hoped to have it done in time for April Fools’ Day, but obviously I didn’t.)

How much do you want to be protected from your own data?

Related link: http://www.oreillynet.com/pub/a/network/2005/04/11/rsdp.html

Every now and then I see a proposal go by for something I’ve wanted for years. Today, it’s the Really Simple Database Protocol.

I am not an enterprise developer. I rarely give a damn about scalability beyond five simultaneous users. I’ve argued in the past that developers need to look beyond scalability, and I’ve argued repeatedly that simpler solutions make a lot more sense for a larger group of people than do highly sophisticated but deeply complicated (think Web Services, EJB, etc.) systems that try to solve every problem.

The Really Simple Database Protocol, from my perspective, is close to perfect. It runs over a verbose but trivial protocol, relying on well-known tools (XML-RPC and SQL) to do simple tasks. The relational database on the other end lets me work with large volumes of highly structured data, and it doesn’t matter (outside of SQL details and features) what database is on the other end of the connection. Nor does the database need to know much about the client.

Yes, there are performance issues, especially if you hit such a server with a few thousand SQL queries simultaneously generating large volumes of returned data. Using HTTPS increases the processing cost, while using HTTP leaves authentication data unguarded. Binary files transferred via XML-RPC become extremely verbose.

Sure, hitting your head against this wall will hurt. If those problems affect you, don’t use this. For those of us who just want to connect database A to client B with a minimum of fuss, this is just the duct tape I’ve been looking for.

Is duct-tape okay, or do we need to solve every problem with titanium alloys adhered with high-strength epoxy adhesives applied using precision robotics?

Related link: http://blog.stayfreemagazine.org/2005/04/google_campaign.html