Re: XML from postgreSQL tables

----- Original Message -----
From: "Karel Zak"


>  Maybe you can try search some application server
>  that support it.
>

a search on sourceforge.net for [xml sql] will return a bunch of projects
which may or may not be useful.

The one that seems interesting is "xmlQuery" at
http://sourceforge.net/projects/xmlquery/ which is described as a "Java
library to execute SQL Query to database and convert result into XML"

>  Is there some good and standard XML for SQL data
>  description?
>

none I am aware of, but the Oracle XML-SQL Utility may be a good start.
Their "canonical mapping" just wraps the resutset in  <ROWSET> and <ROW>
tags, so that (example quoted) "select * from dept" for a table defined as:

CREATE TABLE dept
(
  DEPTNO NUMBER,
  DEPTNAME VARCHAR2(20),
  DEPTADDR AddressType,
  EMPLIST  EmployeeListType
);


...will return something like

<?xml version='1.0'?>
<ROWSET>
 <ROW num="1">
    <DEPTNO>100</DEPTNO>
    <DEPTNAME>Sports</DEPTNAME>
    <DEPTADDR>
      <STREET>100 Redwood Shores Pkwy</STREET>
      <CITY>Redwood Shores</CITY>
      <STATE>CA</STATE>
      <ZIP>94065</ZIP>
    </DEPTADDR>
    <EMPLIST>
      <EMPLIST_ITEM num="1">
         <EMPNO>7369</EMPNO>
         <ENAME>John</ENAME>
         <SALARY>10000</SALARY>
         <EMPADDR>
           <STREET>300 Embarcadero</STREET>
           <CITY>Palo Alto</CITY>
           <STATE>CA</STATE>
           <ZIP>94056</ZIP>
         </EMPADDR>
      </EMPLIST_ITEM>
       <!-- additional employee types within the employee list -->
    </EMPLIST>
  </ROW>
  <!-- additional rows ... -->
</ROWSET>


and BTW it is not mandatory that this transformation be performed on the
server - it may as well be done on the middle or the client tier.
M
ore info about Oracle XSU is available at
http://otn.oracle.com/docs/tech/xml/xdk_java/doc_library/Production9i/index.
html (requires free TechNet account)

hth,

    Marin

----
"...what you brought from your past, is of no use in your present. When
you must choose a new path, do not bring old experiences with you.
Those who strike out afresh, but who attempt to retain a little of the
old life, end up torn apart by their own memories. "