XML - Using MySQL with AJAX

There has been a few posts on PlanetMySQL recently with regard to an XML, firstly the new XML functions for extracting information from XML stored in standard tables and more recently an XML storage engine.

It got me thinking, I've posted recently about working with AJAX and I've been developing some AJAX functionality for our clients. It seems to me that part of the goal with AJAX is to give a better user experience but also to reduce the amount of traffic to the server by only loading what's needed, when it's needed. Not as in our case a refresh of the page (along with the associated images). There is a balancing act between making the page functional and also keeping the amount of Javascript down, it's easy to shift alot of HTML from the initial page only to replace it with complex JavaScript functionality to manipulate XML which is returned in the xmlHttpRequest calls.

I've used Oracle's HTML DB which is an Apache Module which calls Oracle stored procedures and returns the output to the requesting browser, this allows people who are comfortable with PL/SQL to develop web applications from within Oracle, no need for PHP,PERL etc. In fact this is the very reason I became involved with MySQL, looking for an equivalent technology in MySQL.

What would be great is if we could use an Apache module to return the contents of a MySQL stored procedure, this procedure could accept parameters and return an XML document straight from Apache, no additional PHP layer just an HTTP response directly from the webserver interacting with MySQL.

Another great feature of Oracle is SQL*Plus' ability to export in HTML, using the command SET MARKUP HTML ON any subsequent query is output on HTML table format. Something I think would benefit MySQL would be the possiblity of outputting data in XML format, that way we could even remove the stored procedure layer in the above example.

That would fit fantastically into AJAX. Something like the following cold have uses far and above just AJAX...,

mysql> select * from emps;
+-------+---------+
| empno | ename   |
+-------+---------+
| 1     | Dave    |
+-------+---------+
| 2     | John    |
+-------+---------+
2 rows in set (0.00 sec)

mysql> set output xml 

mysql> select * from emps;

<emps>
  <row>
   <empno>1</empno>
   <ename>Dave</ename>
  </row>
  <row>
   <empno>2</empno>
   <ename>John</ename>
  </row>
</emps>
2 rows in set (0.00 sec)