Re: Does anyone know of such a tool [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!
Download The World's Best XML IDE!
Accelerate XML development with our award-winning XML IDE - Download a free trial today!
Subscribe in XML format