I'm considering Altova MapForce (or something similar) to produce either XSLT and/or a Java or C# class to do the translation. Today, we pull data right out of the database and manually build an XML string that we post to a webservice.
Should it be db -> (internal)XML -> XSLT -> (External)XML? What do you folks do out there in the wide world?
I would use one of the out-of-the-box XML serialization classes to do your internal XML generation, and then use XSLT to transform to the external XML. You might generate a schema as well to enforce that the translation code (whatever will drive your XSLT translation) continues to get the XML it is expecting for translation in case of changes to the object breaks things.
There are a number of XSLT editors on the market that will help you do the mappings, but I prefer to just use a regular XML editor.
ya, I think you're heading down the right path with MapForce. If you don't want to write code to preform the actual transformation, MapForce can do that for you also. THis may be better long term b/c it's less code to maintain.
Steer clear of more expensive options (e.g. BizTalk) unless you really need to B2B integration and orchestration.
What database are you using? Oracle has some nice XML mapping tools. There are some Java binding tools (one is http://java.sun.com/developer/technicalArticles/WebServices/jaxb). However, if you have the luxory consider using Ruby which has nice built-in "to_xml" methods.
Tip #1: Avoid all use of XSLT.
The tool support sucks. The resulting solution will be unmaintainable.
Tip #2: Eliminate all unnecessary steps.
Just translate your resultset (assuming you're using JDBC or equiv) to the outbound XML.
Tip #3: Assume all use of a schema-based tool to be incorrect and plan accordingly.
In other words, just fake it. If you have to squirt out some mutant SOAP (redundant, I know) payload just mock up a working SOAP message and then turn it into a template. Velocity doesn't suck.
That said, the best/correct answer, is to use an "XML Writer" style solution. There's a few.
The best is the one I wrote, LOX (Lightweight Objects for XML).
The public API uses a Builder design pattern. Due to some magic under the hood, it's impossible to create malformed XML.
Please note: If XML is the answer, you've asked the wrong question. Sometimes, we're forced against our will to use it in some way. When that happens, it's crucial to use tools which minimize developer effort and improve code maintainability.