Database-generated XML offers an excellent abstraction layer, allowing the business language to vary independently of the data format. (Swapping Java for C would require rewriting the entire XML construction code.) Coupling it to the database makes sense: swap rows for XML content. For example:
SELECT xml_user_roster( 1 );
Given the extensive use of JSON in jQuery (Ajax) web applications, integrating JSON with PostgreSQL is smart. It allows the application code to vary independently of the business code that relies on JSON as a data interchange format.
That said, it would be great if JSON and XML were pluggable modules (similar to PL/R) that could be installed when needed.
Pluggable modules would be fine. But surely rewriting marshaling code is a relatively small part of swapping out your application code... And what if you can't or don't want to work with the particular flavor of XML it generates? JSON is better in that regard, I suppose, since there are fewer options to begin with, but you still have to deal with things like date/time formats.
The XML is generated manually through statements such as:
SELECT
xmlroot(
xmlelement( name root,
xmlelement( name description,
xmlelement( name title, table.title ),
xmlelement( name diet, table.diet ) ), etc.
If the format of the XML was out of the developer's control, XSL is a relatively easy way to convert XML from one format to another. If XSL won't handle it, then any number of ETL tools would be more than sufficient. Unless you mean something else by "flavour of XML"?
To get the same document using a language external to the database requires the following steps:
1. Write the SQL statement (a stored procedure, view, or string).
2. Instantiate an XML document library (e.g., PHP's DOM).
3. Iterate over the result set(s).
4. Build the XML document from the results.
Note that the first step is always required in both situations (whether the query is internal or external to the database).
Steps 2 to 4 effectively echo the first step: they tightly couple the XML format to the expected result set(s) from the database query. There is no abstraction to the application, there is no visible gain.
Also, with XML you can add meta information to the element: <date format="dd-MMM-yyyy">02-FEB-2012</date>. In PostgreSQL, you would use the xmlattribute function.
Comments
Database-generated XML offers an excellent abstraction layer, allowing the business language to vary independently of the data format. (Swapping Java for C would require rewriting the entire XML construction code.) Coupling it to the database makes sense: swap rows for XML content. For example:
Given the extensive use of JSON in jQuery (Ajax) web applications, integrating JSON with PostgreSQL is smart. It allows the application code to vary independently of the business code that relies on JSON as a data interchange format.That said, it would be great if JSON and XML were pluggable modules (similar to PL/R) that could be installed when needed.
Pluggable modules would be fine. But surely rewriting marshaling code is a relatively small part of swapping out your application code... And what if you can't or don't want to work with the particular flavor of XML it generates? JSON is better in that regard, I suppose, since there are fewer options to begin with, but you still have to deal with things like date/time formats.
The XML is generated manually through statements such as:
If the format of the XML was out of the developer's control, XSL is a relatively easy way to convert XML from one format to another. If XSL won't handle it, then any number of ETL tools would be more than sufficient. Unless you mean something else by "flavour of XML"?To get the same document using a language external to the database requires the following steps:
Note that the first step is always required in both situations (whether the query is internal or external to the database).Steps 2 to 4 effectively echo the first step: they tightly couple the XML format to the expected result set(s) from the database query. There is no abstraction to the application, there is no visible gain.
Also, with XML you can add meta information to the element: <date format="dd-MMM-yyyy">02-FEB-2012</date>. In PostgreSQL, you would use the xmlattribute function.