Solved

Help search XML Files

Posted on 2013-06-17
8
259 Views
Last Modified: 2013-08-08
Hello,

In my program I call a function which executes a query (with parameter) on the database.  This can be called thousands of times.
This is not very efficient.  I though a better approach might to be download all the data once to XML file, save that on the server and then query that each time.

To be able to try this I’m looking for suggestions as to how I can query an XML file and return an XMLNodeList containing the results?

I'd need to return records/record

<records xmlns:dt="urn:schemas-microsoft-com:datatypes">
<record>
  <prefix>1234</prefix>
  <title>Document 1</title>
  <document_id>946</document_id>
  <revision>1.1</revision>
  <class_id>24</class_id>
  <name>Type 01</name>
  <description />
</record>
</records>

TIA
0
Comment
Question by:andyw27
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 36

Expert Comment

by:Miguel Oz
ID: 39252838
Use XmlNode.SelectNodes  Method as follows:
XmlNodeList nodelist = doc.SelectNodes("//record");

Open in new window

where doc is your XmlDocument instance. For further details check link above.
0
 
LVL 14

Expert Comment

by:Daniel Junges
ID: 39252841
MyDataSet.MyDataRow[] drArray = (MyDataSet.MyDataRow[])dataSet.MyTable.Select( "class_id=24", "SEQ asc" );
0
 
LVL 13

Expert Comment

by:jonnidip
ID: 39253057
As suggested by #junges, it would be better to load your XML into a DataSet and query the DataSet.
You may also take the advantage of using a sql-like syntax (linq to dataset) by adding System.Data.DataSetExtensions dll to your references.

Please note that if your goal is performance, you may create a Class ("Entity") that reflects your query and serialize it to disk. You may then access it whenever you want (maybe until you want it to "expire", based on the date of the file).
I personally use this approach and find it very useful (but it needs a bit more initial developing).


Regards.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39253127
@jonnidip

it would be better to load your XML into a DataSet and query the DataSet.
Why is that?
0
 
LVL 13

Expert Comment

by:jonnidip
ID: 39253134
it would be better
stands for "I prefer...". Sorry.
0
 

Author Comment

by:andyw27
ID: 39256733
Hi - thanks for the ideas.  Do you have some examples of how you go about creating a Class ("Entity") that reflects your query and serialize it to disk ?
0
 
LVL 36

Expert Comment

by:Miguel Oz
ID: 39257907
Loading to the dataset is an expensive operation, unless you require read/write operations of the record as a whole. (From your question you are using this as a cache to avoid expensive sql calls)
Your question only asks for "query an XML file and return an XMLNodeList containing the results?"
My previous post address that question, more complete code using the sample from link should be:
    XmlDocument doc = new XmlDocument();
    doc.Load("YourFilePathHere.xml");
    XmlNodeList nodeList=root.SelectNodes(("//record");

Open in new window

where "//record"  is an XPath expression that allows to limit the nodeList object to the record objects.
0
 
LVL 13

Accepted Solution

by:
jonnidip earned 500 total points
ID: 39258610
This is a simplified example of the pattern I use to get classes from db, serialize them on disk and get them back to classes.

The classes (record and records, where records is a List of record):
[Serializable]
public class records : List<record>
{
    public records() { }

    public records(IDataReader dr)
    {
        while (dr.Read())
            Add(new record(dr));
    }
}

[Serializable]
public class record
{
    public record() { }

    public record(IDataReader dr)
    {
        prefix = dr.GetString(dr.GetOrdinal("prefix"));
        title = dr.GetString(dr.GetOrdinal("title"));
        document_id = dr.GetInt32(dr.GetOrdinal("document_id"));
        revision = dr.GetString(dr.GetOrdinal("revision"));
        class_id = dr.GetInt32(dr.GetOrdinal("class_id"));
        name = dr.GetString(dr.GetOrdinal("name"));
        description = dr.IsDBNull(dr.GetOrdinal("description")) ? null : dr.GetString(dr.GetOrdinal("description"));
    }

    public String prefix { get; set; }
    public String title { get; set; }
    public Int32 document_id { get; set; }
    public String revision { get; set; }
    public Int32 class_id { get; set; }
    public String name { get; set; }
    public String description { get; set; }
}

Open in new window

Please note that I put "dr.IsDBNull" only on the "description" field. You better analyze your environment and use it anywhere you need.

These are the methods to serialize/deserialize (to/from a XML string):
private String SerializeEntityXML<T>(T Entity)
{
    using (MemoryStream ms = new MemoryStream())
    {
        new System.Xml.Serialization.XmlSerializer(typeof(T)).Serialize(ms, Entity);
        ms.Position = 0;

        using (StreamReader sr = new StreamReader(ms))
            return sr.ReadToEnd();
    }
}

private T DeserializeEntityXML<T>(String SerializedEntity)
{
    using (MemoryStream memoryStream = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(SerializedEntity)))
    {
        T retValue = default(T);
        memoryStream.Position = 0;

        retValue = (T)new System.Xml.Serialization.XmlSerializer(typeof(T)).Deserialize(memoryStream);
        return retValue;
    }
}

Open in new window

There are also other ways to serialize/deserialize, some are more efficient than these.
You may find that (for example) a BinarySerializer may work better for you.

And this is the test method:
private void Test()
{
    records r = null;
    using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["sqlConnectionString"].ConnectionString))
    {
        conn.Open();
        using (System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand()
                                                                    {
                                                                        Connection = conn,
                                                                        CommandText = "select '1234' as prefix,'Document 1' as title,946 as document_id,'1.1' as revision,24 as class_id,'Type 01' as name,null as description union select '1234' as prefix,'Document 2' as title,947 as document_id,'1.1' as revision,24 as class_id,'Type 01' as name,null as description",
                                                                        CommandType = CommandType.Text
                                                                    })
        {
            r = new records(comm.ExecuteReader());
        }
    }

    String serialized = SerializeEntityXML<records>(r);
    File.WriteAllText(@"c:\temp\records.xml", serialized, System.Text.Encoding.UTF8);
    records r_deserialized = DeserializeEntityXML<records>(File.ReadAllText(@"c:\temp\records.xml", System.Text.Encoding.UTF8));
}

Open in new window


Please, treat this as a mere example.
There is much to do about optimization, but I wanted to share with you a simple way to work.

I hope this helps.


Regards.
0

Featured Post

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question