Efficiently comparing results from an XPath query to a resultset returned from a database table
-
Hi, Let's say, I have an XML file that looks like this. 1 abc $50 .... and I have a table that looks like this: Relation Name ------------- Donor Columns ------- DonorID - bigint DonorName - nvarchar(100) DonationCeiling - float I want to get all of the donor names and amounts from the xml and compare them against the maximum amount allowed. I want to remove all donors from the xml whose donation has exceed the ceiling. I can do this. XmlNamespaceManager nsmanager = new XmlNamespaceManager(reader.NameTable); nsmanager.AddNamespace("donor", "www.mydomain.com/xmlns/donor"); nsmanager.PushScope(); XmlDocument doc = new XmlDocument(); doc.Load("donors.xml"); //Select and display the value of all the ISBN attributes. XmlNodeList donorIds; XmlNodeList donationAmount ; XmlElement root = doc.DocumentElement; nodeList = root.SelectNodes("/donors/donorid", nsmanager); Is there a way to retrieve all xml node values from two different xml nodes using one xml path expression into one data structure. For instance, get all donor Ids an donation amount as a key-value list in one data-structure making a single call to root.SelectNodes Once the donation ids and donations amounts are retrieved, even if they are retrieved as two separate XmlNodeLists, what is the most efficient way to compare these values to the DonationCeiling values returned from the Donor table. The select query to the Donor table would look like this: string sql = "Select DonorID, DonationCeiling from Donor where DonorID in ('" + (why can't we just pass the whole XmlNodeList as one data-structure here, without having to loop-through and build the list of DonorId's from the XMLNodeList + "') ; recordset.ExecuteSQL(sql) ; After the sql returns the recordset, is there an efficient way to compare the DonationAmounts in the XmlNodeList to the DonationCeiling amounts returned from the Donor table without having two nested loops. int donorId = Convert.ToInt32(donorIds[0].InnerText) ; while (!recordset.EOF) { for (int i=0; i < donorIds.Count;i++) { if (Convert.ToInt32(donationAmount[i].InnerText) > recordSet.Fields("donationCeiling").Value (should not have to cast to string and then to an integer - the compiler should automatically determine the type based on the returned value from the database column) {
-
Hi, Let's say, I have an XML file that looks like this. 1 abc $50 .... and I have a table that looks like this: Relation Name ------------- Donor Columns ------- DonorID - bigint DonorName - nvarchar(100) DonationCeiling - float I want to get all of the donor names and amounts from the xml and compare them against the maximum amount allowed. I want to remove all donors from the xml whose donation has exceed the ceiling. I can do this. XmlNamespaceManager nsmanager = new XmlNamespaceManager(reader.NameTable); nsmanager.AddNamespace("donor", "www.mydomain.com/xmlns/donor"); nsmanager.PushScope(); XmlDocument doc = new XmlDocument(); doc.Load("donors.xml"); //Select and display the value of all the ISBN attributes. XmlNodeList donorIds; XmlNodeList donationAmount ; XmlElement root = doc.DocumentElement; nodeList = root.SelectNodes("/donors/donorid", nsmanager); Is there a way to retrieve all xml node values from two different xml nodes using one xml path expression into one data structure. For instance, get all donor Ids an donation amount as a key-value list in one data-structure making a single call to root.SelectNodes Once the donation ids and donations amounts are retrieved, even if they are retrieved as two separate XmlNodeLists, what is the most efficient way to compare these values to the DonationCeiling values returned from the Donor table. The select query to the Donor table would look like this: string sql = "Select DonorID, DonationCeiling from Donor where DonorID in ('" + (why can't we just pass the whole XmlNodeList as one data-structure here, without having to loop-through and build the list of DonorId's from the XMLNodeList + "') ; recordset.ExecuteSQL(sql) ; After the sql returns the recordset, is there an efficient way to compare the DonationAmounts in the XmlNodeList to the DonationCeiling amounts returned from the Donor table without having two nested loops. int donorId = Convert.ToInt32(donorIds[0].InnerText) ; while (!recordset.EOF) { for (int i=0; i < donorIds.Count;i++) { if (Convert.ToInt32(donationAmount[i].InnerText) > recordSet.Fields("donationCeiling").Value (should not have to cast to string and then to an integer - the compiler should automatically determine the type based on the returned value from the database column) {