[XML-DEV Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message]

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

  • From: John Hicks <cerium@i...>
  • To: prasadm@c...,xml-dev@i...
  • Date: Tue, 18 Jan 2000 04:22:06 -0500

sql results as xml
At 09:44 AM 01/13/2000 -0600, prasadm@c... 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@c...
http://ceriumworks.com
"Software as a conversation with a community."


xml-dev: A list for W3C XML Developers. To post, mailto:xml-dev@i...
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.



PURCHASE STYLUS STUDIO ONLINE TODAY!

Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced!

Buy Stylus Studio Now

Download The World's Best XML IDE!

Accelerate XML development with our award-winning XML IDE - Download a free trial today!

Don't miss another message! Subscribe to this list today.
Email
First Name
Last Name
Company
Subscribe in XML format
RSS 2.0
Atom 0.3
 

Stylus Studio has published XML-DEV in RSS and ATOM formats, enabling users to easily subcribe to the list from their preferred news reader application.


Stylus Studio Sponsored Links are added links designed to provide related and additional information to the visitors of this website. they were not included by the author in the initial post. To view the content without the Sponsor Links please click here.

Site Map | Privacy Policy | Terms of Use | Trademarks
Free Stylus Studio XML Training:
W3C Member
Stylus Studio® and DataDirect XQuery ™are products from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2013 All Rights Reserved.