Is there a standard XML query encoding for SQL?

Question

Is there a standard way of encoding SQL queries as XML? I mean something like

select name from users where name like 'P%' group by name order by name desc

might encode as (my 5-minute mockup, probably bobbins)...

<?xml version="1.0" encoding="UTF-8"?>
<query>
    <select>
        <table name="users">
            <column name="name"/>
        </table>
    </select>
    <from>
        <table name="users"/>
    </from>
    <where>
        <operator name="like">
            <column name="name"/>
            <value>P%</value>
        </operator>
    </where>
    <aggregation>
        <groupby>
            <column name="name"/>
        </groupby>
    </aggregation>
    <order>
        <order-by>
            <column name="name" order="desc"/>
        </order-by>
    </order>
</query>

...which would make it easy to build, store, validate structure and content (by producing a schema based on the database schema) etc.

Answer 1

I'm unaware of any such standard. What you have so far looks pretty workable. I question why you want to do this, though. I think this is a inner platform (an anti-pattern). Also, it's specifically re-inventing SQL, which is a well-known instance of that anti-pattern. To top it all off, it's programming in XML, which is widely regarded as a bad idea.

The SQL grammar is simple enough that you can probably build a parser for it in short order using normal parser-generator tools (there are likely some already existing on the web that are open source). That would be a much cleaner way of verifying your SQL syntax.

Answer 2

I have been trying to do the same thing.

To answer the question in the comments as to why I would want to. Well, I want to define a base query with the set of available columns and filter conditions and I want to allow a user to select the columns they want to display and enable and disable certain expressions in the where clause.

I have played around with a few variations of an XML schema and got some decent results. I was even able to apply an XSLT to extract the SQL text based on the users' preferences.

I have also gone looking for SQL parsers. http://www.sqlparser.com has one but its commercial and it's API is heavily Delphi styled and not very accessible in my opinion.

As others have said it is feasible to use something like ANTLR to generate C# code that will parse SQL. You can find a few SQL grammars here. The most recent MS SQL Gramar listed there is MS SQL 2000. I haven't had time to play around with this stuff.

I was hoping that there would be a decent M Grammer in the Oslo SDK that would be able to parse queries, but I have not found one yet.