Solved

Help search XML Files

Posted on 2013-06-17
8
256 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
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.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

831 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