How to make a SQL query on a XML data source with ADO?

Hi all.

I often used ADO & XML...
The job of persisting a recordset on a XML file (pr string or stream) and reading back it into a recordset object is pretty simple.
The read operation is the subject of this question.
I used a call like this, to read XML file:
   Rs.Open XMLFilePath, "Provider=MSPersist;", ... ...
or i used to read using an ADODB.Recordset object with a MSXML2.DomDocument.
Up to here... no problem.
The problem borns when, instead of getting the whole table (recordset) stored into the XML file, I would like to perform a sql quuery on this file. I tryed various approaches like a connectionstring that can handle XML files (none found on the net; I tried with "Provider=MSPersist; DataSource=C:\MyFolder\" and than "SELECT * FROM [MyFile.XML]" doing a thing similar to the method for using text files; I get an error sayng something on ISAM)... and other experiments... but all had fail.
I found nothing useful on the net.
I hope your help and thank you all in advance. Bye.                                                                                                                                                                                            
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
As a workaround, have you tried using the Filter method on the Recordset?  In other words, open the entire Xml document and then use the Filter method to get the part(s) you need.  If I am misunderstanding you consider posting your Xml and your desired result.

here is what you can use (I tried it and it works perfectly): load your XML file into DataSet and Select() method to select from your XML.
Here is one example:

1. XML file you will need to put in working directory and name it DSMenu.xml:

      <DSMenu xmlns="">
            <Name>Mobile Devices</Name>
            <Description />
            <Description />
            <Description />

2. Create a .Net console application, put "using System.Data" on the top of Class1.cs (the class where Main method is) and put next code in the body of Main() method:

                  DataRow drMenuItem = null;
                  DataSet ds = new DataSet();
                  DataRow[] menuItems = ds.Tables[0].Select("MenuItemID = 3");
                  if(menuItems != null && menuItems.Length == 1)
                        drMenuItem = menuItems[0];

Try it and you will see that "Select" statement works fine. You can do a lot of thing this way. You can also make strongly typed datasets and use them in the same situation but with more possibilities.

I hope this will help you.

Anthony PerkinsCommented:
>>here is what you can use<<
I am not sure if you noticed but the questioner is talking about ADO not ADO.NET
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!


You are right, I missed that. Should I tell someone to delete my post?

Sorry for the inconvenience.
Anthony PerkinsCommented:
I would not bother.  Who knows it may actually convince the questioner to updgrade :)
Yes, that would be a good idea :-)
Thx acperkins for noticing that and correcting me.
IguanoideAuthor Commented:
Yes... I'm using VB6 + ADO + ASP (serverside) in this application. Without dotNET.

I know how to load a XML file into a Recordset...  The recordset allow to do Filters but is limitative: using Filter property I'm unable to do complex queries. The problem is that loading the XML file into the recordset using the Open method, the ActiveConnection property of Recordset remain Nothing... so I can't use Execute method.

Just for curiosity..
you (Sasapopovic [nice nick ;)])  showed me how to load XML into a Dataset, wich in ADO.NET is similar to the old Recordset.
So in ADO.NET DataSets have a execute method  (or its correspondent)? Or I would find the same problem in ADO.NET?

A last thing. Now I give my question in a way that someone could find cleaner...
Being Execute method of ADODB.Connection class the only way for making SQL queries (or its equivalent in ADODB.Command, but a Connection object is required too)... so, I would like to know if someone of you know a ConnectionString that could connect to a XML database.
Or maybe is there a way for make a SQL query without an ActiveConnection... using only a Recordset object??

My problem is still here!! Help me!
Bye all... and thank you in advance.

In ADO .Net, DataSets have a Select() method that can be used to filter data that was loaded into DataSet. That means that you would load your XML file into DataSet and then use the DataSet.Select() method to filter rows you need.

Here is one more idea: there few products on the net that allow you to make an ODBC connection to XML document. One of them is " DataDirect Connect® for ODBC XML driver" ( Basically, you would install such driver on your machine, make ODBC connection to XML file (like you would make ODBC for any database), connect to ODBC from your application (adjust connection string so you would connect to ODBC XML driver as you would connect to any other database) and make SELECTs like you do on any database. I think that would be good solution for you but I'm not sure if there is such product that is free (you should maybe post such question in some other area).

I hope this will help you.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IguanoideAuthor Commented:
This is not free... :(
Anyhow I think this is a good chance to learn the new version of VB (maybe I should say "new programming language": VB6 seems to be very different) cause I can't find any other driver for XML... (and a free one should alse be more undiscoverable) :(

Another little question (that don't want to be a new post... but only a little delucidation on what you said me about Select() method of DataSet. Don't be afraid :)
-> So using that method it's impossible to make a query on more than one table?

Bye all and thank you so much for your help !!!

The Select() method is DataTable's method so you can not make selects on more than one table. There are other mechanisms like data relations that can be set between DataTables in one DataSet. Using that mechanism I think you can filter on more then one table.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.