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

There is not exactly a standard. Probably you should not do what you intend to do. But if I were going to, I would look at one of: (1)Bastardize some constructs from the xml query plan. The XSD on a default sql 2005 installation is at C:\Program Files\Microsoft SQL Server\90\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd

(2)Sql 2008 comes with intellisense in the IDE, and it appears to implement this with the help of this .net assembly - C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.SqlServer.SqlParser.dll If you add a reference to this assembly you can parse the source script and pull out the xml definition that the parser assembly provides. E.g.

SqlScript script = Parser.Parse(strSqlScript);
foreach (SqlBatch batch in script.Batches)
{
  foreach (SqlStatement s in batch.Statements)
  {
    Console.Write(s.Xml);
  }
}

You may find that the xml definition it gives could be reused for your needs.

Answer 3

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.

Answer 4

XQuery is an XML query language that supports some of the same ideas as SQL. These are not mutually comprehensible languages, but if you're familiar with SQL, you shouldn't have too terribly much trouble with XQuery either.

As far as i Know, there is only one realistic implementation of XQuery, exist.

I have not used either technology in any more than general, exploratory interest.