Using XML to serialize a SQL Query

Let me state up front that I know very little about XML. My experience is 
pretty much limited using the XML Serializer to serialize a user preferences 
class to a file and back again.

I'm writing a rules engine for an application that basically involves 
allowing the user to enter the the WHERE clause for a SQL Server query that 
becomes the rule. The rules will be stored in a SQL Server database and the 
user will be able to make changes to the rules. The rules are entered through 
a non-freeform interface, so I don't have to try and parse the rule after the 
user enters it. However, when I retrieve the rule from the database I will 
need to identify the parts of the rule to properly repopulate the interface.

My first thought was that i would have to create my own syntax to represent 
the rule and write code to parse the result but then I thought XML was 
supposed to be good at that sort of thing. So I started reading about XSD 
schemas hoping to create something that I could use to create an string 
containing the rule that I could write into the database and the opposite.

I started trying to create an XSD schema for the rules and then I looked at 
the recursive definition of a SQL WHERE clause and figured I'd better ask for 
advice.

I did a Google search for an XSD schema for a SQL Server query and came up 
with nothing.

Is XML the correct tool for my problem?

Does anyone know where I can get an XSD schema for either a SQL query or the 
WHERE clause?

How do I handle the recursive definition of the WHERE clause in an XSD schema?

Is there a better way to approach my problem?

Thanks,

Bob

0
10/6/2004 1:21:04 PM
dotnet.xml 7266 articles. 0 followers. Follow

5 Replies
567 Views

Similar Articles

[PageSpeed] 54

Hi Bob,

First of all, I would like to confirm my understanding of your issue. From 
your description, I understand that you need to parse the WHERE clause 
yourself. If there is any misunderstanding, please feel free to let me know.

As far as I know, XML and XSD are not the right choice for us to achieve 
this. Instead, I recommend you use Regular Expressions.

Regular expressions provide a powerful, flexible, and efficient method for 
processing text. The extensive pattern-matching notation of regular 
expressions allows you to quickly parse large amounts of text to find 
specific character patterns; to extract, edit, replace, or delete text 
substrings; or to add the extracted strings to a collection in order to 
generate a report. For many applications that deal with strings (such as 
HTML processing, log file parsing, and HTTP header parsing), regular 
expressions are an indispensable tool.

In .NET framework, we have classes that can manipulate on Regular 
Expressions such as Regex under namespace System.Text.RegularExpressions.

For more information, please check the following links:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconCOMRegularExpressions.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfSystemTextRegularExpressionsRegexClassTopic.asp

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no 
rights."

0
v-kevy (347)
10/7/2004 3:42:35 AM

"Kevin Yu [MSFT]" wrote:

> Hi Bob,
> 
> First of all, I would like to confirm my understanding of your issue. From 
> your description, I understand that you need to parse the WHERE clause 
> yourself. If there is any misunderstanding, please feel free to let me know.
> 

We may be coming at the same thing from different angles. I have an ASP.NET 
page that will assist the user in creating the WHERE clause, in a manner 
similar to the way it works in DTS when the user is specifying a query to 
select the data to be imported/exported.

This expression becomes a rule. I will combine this rule with other rules to 
create a SQL query. Each rule is stored in the database and the user can edit 
them. When I retrieve the rule from the database, I need some way to populate 
the controls on the page with the components of the rule.

Let's take a simple rule: Loan.Amount > 500000

If I simply store it in the database as "Loan.Amount greater than 500000", I 
will have to parse the string to populate the the page with the components of 
the rule.

My thought was that if I could store the rule in the database is some sort 
of pre-parsed form, it would simplify the work I need to do to populate the 
page with the components of the rule.

So, if I had some sort of schema that allowed me to store the rule something 
like:

<coumnReference>Loan.Amount</columnReference>
<operator>greater than</operator>
<value>500000</value>

it would be easier to populate the page properly.

Does this make sense, or am I trying to use the wrong tool for the job?

Bob

0
10/7/2004 12:37:06 PM
Seems like there are two problems here:  one is representing the WHERE 
clause in XML.  This is not too hard.  The other is parsing the WHERE 
clause.  This is trickier.

For the former, something like this would work:
    <where>
      <greater>
        <ci>Loan.Amount</ci>
        <ci>50000</ci>
      </greater>
    </where>

which might be read as "WHERE Loan.Amount > 50000".

More complex queries might be like
    <where>
      <and>
      <greater>
        <ci>Loan.Amount</ci>
        <ci>50000</ci>
      </greater>
      <not-equal>
         <ci>Loan.State</ci>
         <ci>Arizona</ci>
      </not-equal>
      </and>
    </where>

You would have to have a separate element to represent each logical 
relationship (equals, not equals, greater, lesser, like, and, or, etc). 
Converting from the XML to the query string should be a fairly 
straightforward XSL transform.

Now, for the 2nd problem, parsing.  Think of that as going in the other 
direction: converting a WHERE Expression to some in-memory representation, 
(and then to XML).  This is Non Trivial.  But there are lots of tools.

Try www.antlr.org , which is a tool that can help you build a parser. There 
are even examples of how to use Antlr to build a SQL parser, eg
http://www.antlr.org/grammar/ORACLE-7-SQL
http://www.antlr.org/grammar/1062280680642/MS_SQL_SELECT.html

Antlr was originally written in Java, but is now available for .NET.
http://www.antlr.org/doc/csharp-runtime.html

So you could use one of the existing SQL grammars as a starting point, and 
go from there.

Also see
http://weblogs.asp.net/autocrat/archive/2004/02/18/75496.aspx


Other SQL Parsers that may be of interest:
http://www.wangz.net/gsqlparser/index.html (Win32 DLL)
http://www.mathematik.uni-ulm.de/help/perl5/doc-5.005_03/SQL/Statement.html 
(Perl)
http://backpedal.sourceforge.net/sqlparser.html  (Java, compilable or 
convertible to .NET
http://www.thecodeproject.com/database/oledb_example.asp (C++)
http://www.epsilon-logic.net/ELSQBFAQ.asp (ActiveX Component)
http://sqlfairy.sourceforge.net/ (perl)
http://www.experlog.com/gibello/zql/ (Java)


-D


"Bob" <notrainsley@worldsavings.com> wrote in message 
news:FE11414C-F3A9-4BFF-8424-C53C0D5FC777@microsoft.com...
>
>
> "Kevin Yu [MSFT]" wrote:
>
>> Hi Bob,
>>
>> First of all, I would like to confirm my understanding of your issue. 
>> From
>> your description, I understand that you need to parse the WHERE clause
>> yourself. If there is any misunderstanding, please feel free to let me 
>> know.
>>
>
> We may be coming at the same thing from different angles. I have an 
> ASP.NET
> page that will assist the user in creating the WHERE clause, in a manner
> similar to the way it works in DTS when the user is specifying a query to
> select the data to be imported/exported.
>
> This expression becomes a rule. I will combine this rule with other rules 
> to
> create a SQL query. Each rule is stored in the database and the user can 
> edit
> them. When I retrieve the rule from the database, I need some way to 
> populate
> the controls on the page with the components of the rule.
>
> Let's take a simple rule: Loan.Amount > 500000
>
> If I simply store it in the database as "Loan.Amount greater than 500000", 
> I
> will have to parse the string to populate the the page with the components 
> of
> the rule.
>
> My thought was that if I could store the rule in the database is some sort
> of pre-parsed form, it would simplify the work I need to do to populate 
> the
> page with the components of the rule.
>
> So, if I had some sort of schema that allowed me to store the rule 
> something
> like:
>
> <coumnReference>Loan.Amount</columnReference>
> <operator>greater than</operator>
> <value>500000</value>
>
> it would be easier to populate the page properly.
>
> Does this make sense, or am I trying to use the wrong tool for the job?
>
> Bob
> 


0
dinoch1 (221)
10/7/2004 2:40:19 PM
Dino,

You've identified what I'm trying to do.

I'll go check out the links you've posted.

Thanks,

Bob

"Dino Chiesa [Microsoft]" wrote:

> Seems like there are two problems here:  one is representing the WHERE 
> clause in XML.  This is not too hard.  The other is parsing the WHERE 
> clause.  This is trickier.
> 
> For the former, something like this would work:
>     <where>
>       <greater>
>         <ci>Loan.Amount</ci>
>         <ci>50000</ci>
>       </greater>
>     </where>
> 
> which might be read as "WHERE Loan.Amount > 50000".
> 
> More complex queries might be like
>     <where>
>       <and>
>       <greater>
>         <ci>Loan.Amount</ci>
>         <ci>50000</ci>
>       </greater>
>       <not-equal>
>          <ci>Loan.State</ci>
>          <ci>Arizona</ci>
>       </not-equal>
>       </and>
>     </where>
> 
> You would have to have a separate element to represent each logical 
> relationship (equals, not equals, greater, lesser, like, and, or, etc). 
> Converting from the XML to the query string should be a fairly 
> straightforward XSL transform.
> 
> Now, for the 2nd problem, parsing.  Think of that as going in the other 
> direction: converting a WHERE Expression to some in-memory representation, 
> (and then to XML).  This is Non Trivial.  But there are lots of tools.
> 
> Try www.antlr.org , which is a tool that can help you build a parser. There 
> are even examples of how to use Antlr to build a SQL parser, eg
> http://www.antlr.org/grammar/ORACLE-7-SQL
> http://www.antlr.org/grammar/1062280680642/MS_SQL_SELECT.html
> 
> Antlr was originally written in Java, but is now available for .NET.
> http://www.antlr.org/doc/csharp-runtime.html
> 
> So you could use one of the existing SQL grammars as a starting point, and 
> go from there.
> 
> Also see
> http://weblogs.asp.net/autocrat/archive/2004/02/18/75496.aspx
> 
> 
> Other SQL Parsers that may be of interest:
> http://www.wangz.net/gsqlparser/index.html (Win32 DLL)
> http://www.mathematik.uni-ulm.de/help/perl5/doc-5.005_03/SQL/Statement.html 
> (Perl)
> http://backpedal.sourceforge.net/sqlparser.html  (Java, compilable or 
> convertible to .NET
> http://www.thecodeproject.com/database/oledb_example.asp (C++)
> http://www.epsilon-logic.net/ELSQBFAQ.asp (ActiveX Component)
> http://sqlfairy.sourceforge.net/ (perl)
> http://www.experlog.com/gibello/zql/ (Java)
> 
> 
> -D

0
10/7/2004 3:17:04 PM
Sorry, Bob, I may not understand your problem quite well. Thanks to Dino, 
for providing such useful information.

Also thanks to Bob for sharing your experience with all the people here. If 
you have any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no 
rights."

0
v-kevy (347)
10/8/2004 6:51:40 AM
Reply:

Similar Artilces:

Dynamic Xml editor
Hi, I am writing a user control that will help a user that know (almost) nothing about Xml to build a Xml file based on a Xsd schema that is given to the user control. The idea is to have a treeview on the left and a property grid on the right. The treeview is loaded with the root elements from the schema. When the user right click on a node, a context menu allow him to add subnodes if schema allow it. This way, the user can create items, attributes, ... The property grid is filled by the user with data. At the end, the Xml file is generated and validated. My problem is about the design....

Changing SQL Server for CRM 1.2
As my SQL server is currently on its last breath I am in the process of moving our CRM 1.2 databases to a new dedicated serever. I have detatched the databases from the old server, copied the files to the server and attached them. I have then gone into CRM deployment manager and changed to the SQL server to the new server. That all appears to work fine, no error messages. But when users try to access the CRM system they get an error and the CRM server event log fills with error messages. A few are listed below: dmLog: Failed to execute the SQL batch in the file sqlbatch.sql. MSCRM P...

Business Portal Error-SQL server does not exist or access denied
Hi, We are running business portal 4.0 for one of our customer. It was running correctly, however, they have changed the SQL server port (previously it was set as default 1433). After that the business portal becomes very slow and while creating a new request (purchase requisition) if we open the item pop up; it is showing exception "SQL Server does not exist or access denied...." Can any body tell me how can I provide the new port number to business portal connection to the database. Thanks and Regards, Waliullah, Thanks for using the newsgroups. I have a...

Can't insert Char(1) into SQL Server from Stored Procedure
Hello I am trying to insert a char(1) field into a table from an ASP.Net/C# application. At first I was inserting rows into the table but none of the columns that I was inserting the rows into were char(1) columns, they were mostly int and varchar columns. Then I found out that there was one column in the list that was not supposed to be an int column but instead it was supposed to be one of the char(1) columns into the table. So I replaced the int column in the list with the char(1) column and since the value of the char(1) column resulted from a CheckBox ('Y...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

Web query timeout setting.
Dear Group, I fill an Excel table using data that I take from an Internet site. Unfortunately, this site is very slow and so I often get a "query did not provide any data" error message. How can I increment the default web query timeout limit? Thanks in advance, Enrico. ...

SpreadSheetML (XML for Excel)
Hello All, A client of mine has Office X for Mac. I am wondering if this edition of Excel supports SpreadSheetML. I have heard that it also support the new Open XML formats (same as Office 11). Does anyone know if either of these statements is accurate? Thanks in advance, -KJ Hi KJ: I believe that both statements are wrong. The XML converter for Office Next on the Mac has not yet been developed. It's not likely to come until after the PC Office team ships their product to retail -- until they stop CHANGING the thing :-) My guess is that the converter won't appear until sometime...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

SQL View with Smartlist Builder
I am having to recreate Anyviews (GP8) into Smartlists (GP10SP3). Not very happy. Many of our Smartlist require the use of SQL views do to the limitations of Anyview and now Smartlist. It appears that I must create a separate Smartlist for each company I wish to use my sql view with. Is there not a way to include a custom SQL view in Smartlist, and have Smartlist use the company the user is logged into? The view exist in each company database. Can this be done with SQL Reporting server and DynamicsGP? Can I pull a report based on the company db the user is logged into? Thanks for the...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

Outlook to SQL
The problem is this I'm trying to get my Contact list that was created on a Exchange Server to a SQL table. How do I do this with keeping the Database created in SQL up-to-date via Outlook. See http://www.outlookcode.com/d/database.htm and = http://www.slipstick.com/addins/groupcontacts.htm for tools to help with = this.=20 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "ErikSQLConfuser" <ErikSQLConfuser@discussions.microsoft.com...

Get Xml from web .
This is a link to an address where I would like to access xml. http://setiathome2.ssl.berkeley.edu/fcgi-bin/fcgi?cmd=user_xml&email=XXXX The XXXX is the email address of a user. All I want to do is access the xml and view it in a datagrid. I have read several tuturials and articles and have not been able to find something that would explain how to do this. Does anyone know of a sample code or something close I could use as a reference. I am using visualstudio.net. and visual basic. THX Take a look at using the following classes on your app: HttpWebRequest HttpWebResponse DataSe...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

How export all mailbox-enabled users from the GAL using Outlook?
Hello, We have a user which, for administrative reasons, needs to export all mailbox-enabled users in the GAL now and then. They just need all the names. Is there a straightforward way for them to do that? We don't want to give them any special permissions and want to avoid server scripting. Ideally, they should be able to export it to a CSV file or any text file. Thanks, - Alan. Alan wrote: > We have a user which, for administrative reasons, needs to export all > mailbox-enabled users in the GAL now and then. They just need all the > names. Is there a straightforward way fo...

SQL query / Formula for Reorder Point?
Is it possible to issue an SQL command(s) that would: 1. Set the Restock Level for all items to be equal to the current on-hand quantity for each item. 2. Set the Reorder point for all items to be equal to 1/3 of that items Restock Level. I'm currently doing this manually, or using a formula in excel when items are initially imported into the DB, but there are still several thousand that need to be retro-fitted like this... Thanx in advance! -=RFM=- UPDATE Item SET RestockLevel=(Quantity - QuantityCommitted) UPDATE Item SET ReorderPoint=ROUND(RestockLevel/3,0) - Evan Culver Ne...

SQL 2008 running on a VM using all allocated memory
Hi, I've got a sql2008 server running on a VM. There's 9GB of physical RAM, which 7GB have been allocated to SQL Server. But when i look at task manager, i see that the SQL server is actually using all 7GB, which is pegging the memory usage of the overal box at above 90% used. We're mostly a sql2005 shop, and none of those servers are doing this. I have sql2005 running on VM's, someone actual servers as a named or default instance, and some even clustered. None of them have this problem. Is this a normal thing with sql2008 only? Any insight would be greatly appre...

Anyone use Promys CRM software integrated with GP Dynamics?
Our company is considering using Promys as a CRM to create quotes and sales orders for the sales team. I am concerned about the integration with GP and what the pitfalls may be. Is anyone here currently using Promys with GP Dynamics? ...

Loading Text File to TextBox using LoadFromFile
Hi All, I'm creating a form that allows the user to pick a txt file (dialog) and then display the path and contents on the form. The code has been cobbled together as I found the pieces that worked, so bear with. I got the file picker working and displaying the file name on the form, but the file contents won't display. I had a feeling the problem had to do with importing a namespace (see the error in the code when I tried "Imports System.IO") or with a missing reference. Using Access 2003. References: VB for Apps, MS Access 11 Obj Lib, OLE Auto, MS V...

Can't use openURL or access internet on some locations
I have previously raised a question where openUrl threw an exception in a specific office location. However the problem I have now is that the application just freeze when I call openURL. It seems as it is waiting for something. This only happens at one company so far the company does have a proxy but so do I at work and I have no problems. I have also tried the Microsoft TEAR sample and it behaves the exact same way, it says "Opening internet...Connection made" and then it just stops. If I run it on my computer it gets the webpage and everything finishes ok. I have tried to set t...

XML Mapping: Creating a "non-repeating schema element" in VS2005
Question regarding XML mapping in Excel. XML Mapping in Excel allows you to map "non-repeating schema elements" to an individual cell while "repeating schema elements" automatically get handled as an Excel "list". I want a table(XML/XSD) exported from a VS2005 dataset to be CELL mappable rather than LIST mappable. Is there a way to make Excel interpret the native dataset XML/XSD as non-repeating? Said differently, what makes a schema element "repeating" versus "non-repeating"? Is it a unique XPATH statement? If my underlying table has...

Query involving Strings : How To Return Matching Data From Both Ta
Good afternoon, I have tried my best on this but am unable to figure it out. --------------------------------------------------------------------- Scenario: Table A contains only one column titled [District]. Let's assume that there are three rows here: 1) Bay Area Rapid Transit 2) San Diego Zoo 3) San Mateo Table B contains other data with these fields: [GeoCode] , [CountOfHourlyEmployees], [CountOfSalaryEmployees]. Let's assume that there are three rows here: 1) West Coast | CA | Bay Area Rapid Transit , 154, 205 2) CA | Southern | Bay Area Rapid Transit , 105, 206 3) Southw...