CODEWITHSUNDEEP
c#asp.netxml
Swquenzer
Swquenzer

Reputation: 35

ASP: Filtering and Parsing many XML records from SQL Server

I apologize if this question has been asked, but I have not yet found a solution here that works for me. I am new to working with XML.

I have an SQL Server table (called "Answers") that contains a column of type XML (called "XmlColumn"). The rows look something like this (each line being a distinct record). 

<value type="option" />
<value type="numeric" value="250" />
<value type="string" value="Sample Text" />
<value type="numeric" value="" />

My question is, from my ASP project, how can I:

  1. Select all records of type="numeric"
  2. Parse all "value"s as int

Currently, I have something that works, but I'm not convinced it's an ideal solution:

     var allAnswers = MyModel.Context.Answers.Select(a => a.XmlColumn);
     List<int> allNumericAnswers = new List<int>();
     foreach (var answer in allAnswers)
     {
        string type = (string)XElement.Parse(answer).Attribute("type");
        string value = (string)XElement.Parse(answer).Attribute("value");
        if (type == "numeric")
        {
           int numericVal;
           bool res = int.TryParse(value, numericVal);
           if (res)
           {
              allNumericAnswers.Add(numericVal);
           }
        }
     }
     //Now allNumericAnswers is holding all integer values of type "numeric"
     //and I can manipulate them as necessary.

My goal is to perform queries such as "Find the average of all numeric values" or "Select all numeric values > 50", etc. So far, all searches have lead me to solutions for traversing, filtering, parsing, etc within a single XML document, however I want to parse and manipulate thousands of these records at a time, and store them in something like an array for further calculations.

Is there a more elegant solution to what I'm doing above?

Upvotes: 0

Views: 46

Answers (1)

Clint B
Clint B

Reputation: 4710

Doing this in SQL would be much more efficient. You wouldn't have to send the entire table contents over the wire and SQL is optimized to handle this type of data interrogation.

Here is a way to get the values where (type="numeric" and value != "") and return the values as an integer.

select convert(int, XmlColumn.value('(/value[@type="numeric" and @value!=""]/@value)[1]', 'varchar(20)')) as NumericValue
from Answers
where XmlColumn.value('(/value[@type="numeric" and @value!=""]/@value)[1]', 'varchar(20)') is not null

Throw an avg on that to get the average

select avg(convert(int, XmlColumn.value('(/value[@type="numeric" and @value!=""]/@value)[1]', 'varchar(20)'))) as NumericValue
from Answers
where XmlColumn.value('(/value[@type="numeric" and @value!=""]/@value)[1]', 'varchar(20)') is not null

Make it a sub-query to get all the ones that have a value > 50 

select NumericValue from 
(
    select convert(int, XmlColumn.value('(/value[@type="numeric" and @value!=""]/@value)[1]', 'varchar(20)')) as NumericValue
    from Answers
    where XmlColumn.value('(/value[@type="numeric" and @value!=""]/@value)[1]', 'varchar(20)') is not null 
) a
where NumericValue > 50

Upvotes: 2

Related Questions