XML and Database Content

I was experimenting with various development tools to present end-user database information, via a Java Web App and really struggled to get consistent results. Some queries would return data correctly, while others simply failed miserably. Usually in situations like this, you need to make sure that database access is correct, field specifications and SQL correctly formatted - you know the deal.

However, best as I might try, I simply could not get some queries to work. Admittedly my environment is quite complex, with various databases and servers, including a GlassFish App Server and a few web servers involved. In the end, the answer was very simple, and I really should have picked this up earlier. The app needs to generate the 'answer' of the query as XML output. Eventually it dawned on me - special XML characters!! Some of the database queries returned field values that contained 'special' XML characters, such as < or > or &. These are used in XML (obviously) to start and end tags (as an example). To solve the problem, you need to 'clean' every returned value from the database, before rendering the XML output. Some of the special characters are :

& replace with &amp;
< replace with &lt;
> replace with &gt;
" replace with &quot;
' replace with &apos;


Once this was done, the XML was properly formatted and could then be used by apps further down the chain.