Is it possible to query a XML file with SQL?

Currently I'm working on a case where we don't want to change to much on a c#/wpf program, but like to add a feature. Currently we allow certain users to add sql queries to a database to retrieve customer data, hereby a custom connection string/ provider name must be specified. With this information it's possible to create the connection and obtain the data with c#.

However we like to add the possibility to allow that user group to query XML files too, with a certain connection string/ provider name. I just had a 开发者_C百科look for possibities in .net to do that, but can't seem to find a decent way... Is something like this possible? (OleDb/ODBC way maybe?)

edit: For clarity I'd like to state that the solution must be able to fit into the pattern of connecting the datasource with the specified connection string, with the specified provider and execute the SQL Query.

edit2: After reviewing the first three answers I decided to have a look beyond XML. This post seems to illustrate the above case the best (only difference is that a XLS is used in stead of a XML): How to query excel file in C# using a detailed query. Possible solutions with XML still welcome however...

Thanks in advance.

Yes. use Linq2Xml

http://www.hookedonlinq.com/LINQtoXML5MinuteOverview.ashx

http://www.liquidcognition.com/tech-tidbits/linq2xml-example.aspx

// Loading from a file, you can also load from a stream
XDocument loaded = XDocument.Load(@"C:\contacts.xml");


// Query the data and write out a subset of contacts
var q = from c in loaded.Descendants("contact")
        where (int)c.Attribute("contactId") < 4
        select (string)c.Element("firstName") + “ “ +
      (string)c.Element("lastName");


foreach (string name in q)
    Console.WriteLine("Customer name = {0}", name);

AFAIK, you cannot use standard sql statements for XML. But what you can use is XQuery. It's a query language for xml documents.

http://en.wikipedia.org/wiki/XQuery
http://www.w3schools.com/xquery/default.asp

hth

Many XML libraries allow XPath queries to be issued against an XML document, but the syntax is very different from SQL and the semantics are very different. Additionally, XPath doesn't really produce result sets in the way that SQL does - it returns parts of an XML document or the contents of fields. I'd say that you will probably encounter a significant impedance mismatch if the rest of the application is geared towards SQL result sets.

XPath is also much dumber than SQL, although there is another language (XQuery) that is much cleverer. However, good XQuery support is much less common in XML parsing libraries. XQuery works quite differently to SQL, so your users may also have trouble understanding it.

Many DBMS platforms (including SQL Server) also have a native XML data type that supports embedding Xpath expressions in SQL queries. Using CROSS APPLY you can do join operations to flatten hierarchical data structures into a SQL result set. However, this is quite fiddly and your users may have trouble getting it to work properly.

In short, I think that adding this sort of facility to query XML documents will probably not work very well.

One option might be to build a facility that shreds the XML documents and populates the contents into a database with the same structure as your application. This is reasonably straightforward to implement and would not require your users to learn a new paradigm.

Linq is an SQL like language for .NET that allows you to write SQL style statements for querying lots of things. It specifically allows you to do it for XML documents.

This article has a pretty good overview of Linq to XML.

Here is an example of how it looks / works

var q = from c in xmlSource.contact
        where c.contactId < 4
        select c.firstName + " " + c.lastName;