Does anyone know of such a tool [SQL results as XML]

Tue Jan 18 09:32:39 GMT 2000

At 09:44 AM 01/13/2000 -0600, prasadm at crt.com wrote:

>Hi All,
>
>We are currently in search of a tool on the web which accepts an SQL query
>as the input and gives us back an XML file containing all the results with
>the tags representing the column names of the table being queried.
>
>One such we found is Oracle's XSQL Servlet...
>
>Another such tool we found is IBM's XML Lightweight Extractor...

>If anyone knows of any tool, that takes a SQL query(select) as its
>input and returns an XML document, please share it with me.

Hi Prasad:

Look at our TagServlet.  Free for comment from 
http://ceriumworks.com.  Comes with TagServletPreview if you want to write 
to a file rather than Http Response.

TagServlet extends our XMLServlet, which pools JDBC connections (to any 
JDBC database), and uses your SQL calls much the way you've always written 
them.  Unlike XMLServlet, this first version of TagServlet only does SQL 
queries, not updates.

An excerpt from the ReadMe:

TagServlet is a trial version of XMLServlet
that writes database query results within XML
tags.

TagServlet comes with TagServletPreview.
TagServletPreview, like XMLServletPreview, runs
from a command line so you may test your
TagServlet input and output without a servlet
engine, and with or without a live database
connection.

See installation instructions in the ReadMe.txt
for XMLServlet.

You edit XML instructions for TagServlet as you
would for XMLServlet, by filling in two kinds
of tag.  Because XMLServlet matches the front-end
work of page designers with the back-end work of
database developers, we call the tags BACK and
FRONT:

   <MATCH>
     <BACK>signIn.sql</BACK>
     <FRONT>accountFound.xml</FRONT>
   </MATCH>

XMLServlet would merge query results from
"signIn.sql" into "accountFound.xml".

TagServlet instead writes all query results
tagged with their SQL column names, like so:

   <ROW>
<EMPNO>000130</EMPNO><BR>
<FIRSTNME>DOLORES  </FIRSTNME><BR>
<MIDINIT>M</MIDINIT><BR>
<LASTNAME>QUINTANA  </LASTNAME><BR>
<WORKDEPT>C01</WORKDEPT><BR>
<PHONENO>4578</PHONENO><BR>
<HIREDATE>July 28, 1981</HIREDATE><BR>
<JOB>ANALYST </JOB><BR>
<EDLEVEL>16</EDLEVEL><BR>
<SEX>F</SEX><BR>
<BIRTHDATE>September 15, 1955</BIRTHDATE><BR>
<SALARY>23800.00</SALARY><BR>
<BONUS>500.00</BONUS><BR>
<COMM>1904.00</COMM><BR>
   </ROW>
   <ROW>
<EMPNO>000140</EMPNO><BR>
<FIRSTNME>BARTHOLOMEW  </FIRSTNME><BR>
<MIDINIT>M</MIDINIT><BR>
<LASTNAME>CUBBINS  </LASTNAME><BR>
<WORKDEPT>C01</WORKDEPT><BR>
<PHONENO>2578</PHONENO><BR>
<HIREDATE>July 4, 1991</HIREDATE><BR>
<JOB>UML ARCHITECT </JOB><BR>
<EDLEVEL>10</EDLEVEL><BR>
<SEX>M</SEX><BR>
<BIRTHDATE>October 18, 1965</BIRTHDATE><BR>
<SALARY>17500.00</SALARY><BR>
<BONUS>0</BONUS><BR>
<COMM>0</COMM><BR>
   </ROW>

This raw format can then be passed to another
application (business-to-business), or given a
user-friendly format with XSL (via a browser
with XSL support, or at the server via servlet
chaining).

==================================================
A Complete Example
==================================================

This example includes:

   1) an SQL query
   2) parameters passed to the SQL query
   3) XML instructions for TagServlet
   4) an optional XML header included by TagServlet
   5) XML results written by TagServlet

1) The SQL query (file
"selectMidSizeDepartments.xml"):

   select d.deptname as department,
   count(e.empno) as employees
   from employee e, department d
   where e.workdept = d.deptno
   group by d.deptname
   having count(*) between zqx01 and zqx02

2) The browser parameters posted in (simulated in file
"browserParmsDepartmentCounts"):

   1
   10

TagServlet substitutes these parameters for cues
zqx01 and zqx02 in the SQL query, giving:

   ...having count(*) between 1 and 10

3) The XML instructions for this example (file
"tagMidSizeDepartments.xml"):

   <SPLICE>
     <STYLESHEET></STYLESHEET>
     <CONTENT-TYPE>text/xml</CONTENT-TYPE>
     <MATCH>
       <BACK>selectMidSizeDepartments.xml</BACK>
       <FRONT REPEATMIN="1"
         REPEATMAX="0">tagMidSizeDepartments.xml
       </FRONT>
     </MATCH>
   </SPLICE>

The <BACK> tag names the SQL query:

    selectMidSizeDepartments.xml

Maybe we should call that .sql rather than .xml?

The <FRONT> tag names file
"tagMidSizeDepartments.xml"...

4) ...which contains:

   <?xml version="1.0"?>

   <!DOCTYPE ROSE[
   <!ELEMENT ROSE (ROW*)>
   <!ELEMENT ROW  (DEPARTMENT,EMPLOYEES)>
   <!ELEMENT DEPARTMENT (#PCDATA)>
   <!ELEMENT EMPLOYEES (#PCDATA)>
   ]>

5) TagServlet returns these results from the SQL query:

   <ROW>
<DEPARTMENT>ADMINISTRATION SYSTEMS      </DEPARTMENT>
<EMPLOYEES>6</EMPLOYEES>
   </ROW>
   <ROW>
<DEPARTMENT>MANUFACTURING SYSTEMS       </DEPARTMENT>
<EMPLOYEES>9</EMPLOYEES>
   </ROW>
   <ROW>
<DEPARTMENT>OPERATIONS                  </DEPARTMENT>
<EMPLOYEES>5</EMPLOYEES>
   </ROW>
   <ROW>
<DEPARTMENT>PLANNING                    </DEPARTMENT>
<EMPLOYEES>1</EMPLOYEES>
   </ROW>
   <ROW>
<DEPARTMENT>SOFTWARE SUPPORT            </DEPARTMENT>
<EMPLOYEES>4</EMPLOYEES>
   </ROW>
   <ROW>
<DEPARTMENT>SPIFFY COMPUTER SERVICE DIV.</DEPARTMENT>
<EMPLOYEES>3</EMPLOYEES>
   </ROW>
   <ROW>
<DEPARTMENT>SUPPORT SERVICES            </DEPARTMENT>
<EMPLOYEES>1</EMPLOYEES>
   </ROW>

Finally, TagServletPreview writes file
"exportMidSizeDepartments.xml" (or a name of your
choosing) where you may preview the results of
your TagServlet instructions.

TagServlet simply writes <FRONT> then <BACK>,
where XMLServlet would merge the two.

=======================================
What Do You Think?
=======================================

What would make this tool more useful to you?  Let
us know, please.

TagServlet extends our XMLServlet tool, not our
XSLServlet tool.  Would you welcome a TagServlet
that includes server-side XSL stylesheet
formatting, as offered by XSLServlet?

Look for the latest discussion, news, FAQs, and
updates at
   http://ceriumworks.com/jackBeans/updates.html

Cerium Component Software
XML Outline | XML DB | XML Servlet
FAX 707-222-7651
support at ceriumworks.com
http://ceriumworks.com
"Software as a conversation with a community."

xml-dev: A list for W3C XML Developers. To post, mailto:xml-dev at ic.ac.uk
Archived as: http://www.lists.ic.ac.uk/hypermail/xml-dev/ or CD-ROM/ISBN 981-02-3594-1
Please note: New list subscriptions now closed in preparation for transfer to OASIS.