Solved

Help search XML Files

Posted on 2013-06-17
8
255 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
8 Comments
 
LVL 35

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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 35

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
2 questions 10 25
MediaHelp 4 23
Adding Existing Item to vb.net Project causes Errors 4 32
jQuery - following an example but can I save data server side? 20 50
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!
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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