[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 570
  • Last Modified:

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

Hi all.
I'M SORRY FOR MY BAD ENGLISH!

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.                                                                                                                                                                                            
0
Iguanoide
Asked:
Iguanoide
  • 5
  • 3
  • 2
1 Solution
 
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.
0
 
sasapopovicCommented:
Hi,

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">
      <MenuItem>
            <MenuItemID>1</MenuItemID>
            <WebPage>mobiledevices.aspx</WebPage>
            <Name>Mobile Devices</Name>
            <Label>MIMobileDevices</Label>
            <Description />
            <Visible>true</Visible>
      </MenuItem>
      <MenuItem>
            <MenuItemID>2</MenuItemID>
            <ListPageID>1</ListPageID>
            <WebPage>dotnet.aspx</WebPage>
            <Name>.Net</Name>
            <Label>MIDotNet</Label>
            <Description />
            <Visible>true</Visible>
      </MenuItem>
      <MenuItem>
            <MenuItemID>3</MenuItemID>
            <WebPage>databases.aspx</WebPage>
            <Name>Databases</Name>
            <Label>MIDatabases</Label>
            <Description />
            <Visible>true</Visible>
      </MenuItem>
      </DSMenu>

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();
                  ds.ReadXml("DSMenu.xml");
                  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.

Regards,
Sasa
0
 
Anthony PerkinsCommented:
sasapopovic,
>>here is what you can use<<
I am not sure if you noticed but the questioner is talking about ADO not ADO.NET
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
sasapopovicCommented:
acperkins,

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

Sorry for the inconvenience.
0
 
Anthony PerkinsCommented:
I would not bother.  Who knows it may actually convince the questioner to updgrade :)
0
 
sasapopovicCommented:
Yes, that would be a good idea :-)
Thx acperkins for noticing that and correcting me.
Regards,
Sasa
0
 
IguanoideAuthor Commented:
Hi.
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.
Iguanoide.
0
 
sasapopovicCommented:
Hi,

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.

Regards,
Sasa
0
 
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 !!!
0
 
sasapopovicCommented:
Hi,

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.

Regards,
Sasa
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now