Representing Sql Query As Xml

[behrk2]

Hey everyone,I'm looking for a good way to represent an SQL statement (specifically, for PostgreSQL) in XML. Are there any standards for doing this? I am trying to figure out if XQuery can achieve something like this for me or not.So, for example:<SELECT> </SELECT><FROM> </FROM>...Does anyone have any thoughts or suggestions on this? I need to be able to support joins and grouping as well. I found an open source tool called sql2dtd, but it's not very good.Thanks!

[boen_robot]

There aren't any standard ways AFAIK.What I do is map each clause to an element, and specify further details with attributes or innder nodes. Note that I only ever represent SELECT queries in this fashion, so my dialect is limited. Here's a pseudo schema:

<s:query><!-- database="" -->	<s:from table="" /><!-- as="" -->	<s:column name="" /><!-- fromTable="" as="" -->	<s:innerJoin table="" using="" />	<s:innerJoin table="">expression</s:innerJoin>	<s:outerJoin table="" using="" />	<s:outerJoin table="">expression</s:outerJoin>	<s:leftJoin table="" using="" />	<s:leftJoin table="">expression</s:leftJoin>	<s:rightJoin table="" using="" />	<s:rightJoin table="">expression</s:rightJoin>	<s:fullJoin table="" using="" />	<s:fullJoin table="">expression</s:fullJoin>	<s:crossJoin table="" using="" />	<s:crossJoin table="">expression</s:crossJoin>	<s:naturalJoin table="" using="" />	<s:naturalJoin table="">expression</s:naturalJoin>	<s:group by="" />	<s:where expression="" />	<s:where expression="">		<s:param name="">value</s:param>		<s:param>value</s:param>	</s:where>	<s:orWhere expression="">		<s:param name="">value</s:param>		<s:param>value</s:param>	</s:orWhere></s:query>

(the "s" stands for my own dialect's namespace)Extending this to include all kinds of queries would mean one very verbose language. So verbose that you might as well write the plain SQL statement as a string.Speaking of which, if you were to use this, or any XML dialect, you'd have to manually write some code that would translate it back to plain SQL. What I do instead is use the Zend Framework, and the Zend_Db_Select class to be more precise. Mapping a single element to a single function in this class is relatively easy. The exact way to do it depends on how you parse your XML (XMLReader, DOM, SimpleXML, etc. - I use XMLReader).[edit]Next time you're looking for a way to "represent ... in XML", try Google-ing "... markup language" to see if there's any XML dialect for it*. "SQL markup language" showed ZsqlML as one possible way. Non standard of course, but seems like a complete dialect... errr.... and an old one too - lastly active (as in "updated or talked about at SourceForge.net") in 2002 (7 years ago!!!).* I'm not making this up now... I've wanted to find out myself if some dialects existed, and this is the kind of query that helped me find what dialects existed. All that I looked for has been leading to dead ends though. For example, recently I was looking for something to represent weather data, and found only WeatherML. And it seems WeatherML was never completed, and the specification's original domain is actually now to a different owner even. Oh well, this means that I'll be creating a new dialect in the right conditions (weather... condtions...get it? ).[/edit][edit]Oh wait... stratch the above. I ran the same query again, just to verify, and it seems there's this thing called DWML... I'll be looking into that then... hmm...[/edit]

[behrk2]

boen_robot-Thanks for all of your great advice! I checked out the Zend_DB_Select class, and I think it will do just the trick! Also, thanks for your suggestions on the Google search - you're right - searching for "...markup language" yields ALOT better results! I had spent many hours prior trying to find some good information!Thanks again.