Solved

Help search XML Files

Posted on 2013-06-17
8
258 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to add extra IF statement to a loop in MS Access VBA 3 61
asp.net repeater 2 35
Building an object from several other objects 4 37
How does this modal work? 3 33
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
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…
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…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

732 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