We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now


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

Iguanoide asked
Medium Priority
Last Modified: 2008-02-01
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.                                                                                                                                                                                            
Watch Question

Top Expert 2012

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="http://www.tempuri.org/DSMenu.xsd">
            <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.

Top Expert 2012

>>here is what you can use<<
I am not sure if you noticed but the questioner is talking about ADO not ADO.NET

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

Sorry for the inconvenience.
Top Expert 2012

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.


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" (http://www.datadirect.com/developer/odbc/odbc_xml_driver/index.ssp). 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.


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


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.

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.