Solved

Help search XML Files

Posted on 2013-06-17
8
253 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
 
LVL 74

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PHP and Soap 3 27
work allocation; web development; vba; access; 4 46
C# Update Status Bar.... 1 24
cs.Designer Issue(2) 2 14
The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now