3

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 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.

|improve this question
0

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.

|improve this answer
  • I think your solution in shredding the XML Documents would be the best... however it does needs source code implementation. So we're still hoping for a 'connect-to-and-query-xml' solution, but guess this would be the best otherwise! – Herman Cordes Apr 26 '10 at 10:09
4

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);
|improve this answer
  • I don't see how using Linq equates to querying XML with SQL. – spender Apr 26 '10 at 8:59
  • It doesn't equate, but it does achieve much the same end. – Andrew McGregor Apr 26 '10 at 9:01
  • Indeed a nice way, but for this case we hope to solve the case by specifying another connection string/ provider name. This way the source code doesn't have to be altered. FYI: the selected users save their queries/ connection strings/ providers to the database, from where this information is requested again when the queries should be executed in the program. – Herman Cordes Apr 26 '10 at 9:02
  • You should be able to save lambda expression of the query in database. – this. __curious_geek Apr 26 '10 at 9:06
1

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

|improve this answer
  • This way the source has to be changed, for the moment I like to search for solutions whereby the source code can stay the same... – Herman Cordes Apr 26 '10 at 9:10
-1

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;
|improve this answer
  • That's not how it would look for just LINQ to XML, of course... if you've just got an XElement or XDocument etc, you'll need Elements, Descendants etc. – Jon Skeet Apr 26 '10 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.