Fetching an XML using LINQ

Question

Below is the XML structure that I am using to pick the nodes.

<?xml version="1.0" encoding="utf-8"?>
<rowset xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
  <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:saw-sql="urn:saw-sql" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset">
    <xsd:complexType name="Row">
      <xsd:sequence>
        <xsd:element name="Column0" type="xsd:date" minOccurs="1" maxOccurs="1" saw-sql:type="date" saw-sql:sqlFormula="&quot;F&amp;R Sales, Balances, Exposures and Limits&quot;.&quot;Financial Calendar&quot;.&quot;Date&quot;" saw-sql:displayFormula="&quot;Financial Calendar&quot;.&quot;Date&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Financial Calendar" saw-sql:columnHeading="Daily" saw-sql:isDoubleColumn="false" saw-sql:columnID="c1f8d3c16da4d2766" />
        <xsd:element name="Column1" type="xsd:string" minOccurs="1" maxOccurs="1" saw-sql:type="char" saw-sql:sqlFormula="&quot;F&amp;R Sales, Balances, Exposures and Limits&quot;.&quot;Customer Details&quot;.&quot;Customer Title&quot;" saw-sql:displayFormula="&quot;Customer Details&quot;.&quot;Customer Title&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Customer Details" saw-sql:columnHeading="Channel" saw-sql:isDoubleColumn="false" saw-sql:columnID="c7e5b7e356cc63ae3" />
        <xsd:element name="Column2" type="xsd:double" minOccurs="0" maxOccurs="1" saw-sql:type="double" saw-sql:sqlFormula="&quot;F&amp;R Sales, Balances, Exposures and Limits&quot;.&quot;Balances - Spot&quot;.&quot;Closing Balance&quot;" saw-sql:displayFormula="&quot;Balances - Spot&quot;.&quot;Closing Balance&quot;" saw-sql:aggregationRule="dimAggr" saw-sql:aggregationType="agg" saw-sql:tableHeading="Balances - Spot" saw-sql:columnHeading="Closing Balance" saw-sql:isDoubleColumn="false" saw-sql:columnID="c5d3ad4e6d8e6cc66" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:schema>
      <Row>
        <Column0>2013-08-30</Column0>
        <Column1>Acquire</Column1>
        <Column2>182629341.8</Column2>
      </Row>
      <Row>
        <Column0>2013-08-30</Column0>
        <Column1>Cross-sell</Column1>
        <Column2>139272587.24</Column2>
      </Row>
      <Row>
        <Column0>2013-08-30</Column0>
        <Column1>Deepen</Column1>
        <Column2>182862985.56</Column2>
      </Row>
      <Row>
        <Column0>2013-08-31</Column0>
        <Column1>Acquire</Column1>
        <Column2>182629341.8</Column2>
      </Row>
      <Row>
        <Column0>2013-08-31</Column0>
        <Column1>Cross-sell</Column1>
        <Column2>139272587.24</Column2>
      </Row>
      <Row>
        <Column0>2013-08-31</Column0>
        <Column1>Deepen</Column1>
        <Column2>182862985.56</Column2>
      </Row>
      <Row>
        <Column0>2013-09-01</Column0>
        <Column1>Acquire</Column1>
        <Column2>182742974.13</Column2>
      </Row>
      <Row>
        <Column0>2013-09-01</Column0>
        <Column1>Cross-sell</Column1>
        <Column2>139423172.28</Column2>
      </Row>
      <Row>
        <Column0>2013-09-01</Column0>
        <Column1>Deepen</Column1>
        <Column2>183136223.84</Column2>
      </Row>
      <Row>
        <Column0>2013-09-02</Column0>
        <Column1>Acquire</Column1>
        <Column2>177144002.21</Column2>
      </Row>
      <Row>
        <Column0>2013-09-02</Column0>
        <Column1>Cross-sell</Column1>
        <Column2>134426394.95</Column2>
      </Row>
      <Row>
        <Column0>2013-09-02</Column0>
        <Column1>Deepen</Column1>
        <Column2>182969892.02</Column2>
      </Row>
      <Row>
        <Column0>2013-09-03</Column0>
        <Column1>Acquire</Column1>
        <Column2>177120781.08</Column2>
      </Row>
              </rowSet>

Basically what I am trying to do is pick all the elements under "Row", in my case being "Column0","Column1" and "Column2".

Below is the code that I am using to fetch the results. Somehow, it aint working. Any help would be helpful.

private static IEnumerable GenerateDataSet(XDocument xmlDoc)
{
    XNamespace ns = @"urn:schemas-microsoft-com:xml-analysis:rowset";
    IEnumerable resultSet = (from result in xmlDoc.Root.Descendants(ns + "Row")
                             select new
                             {
                                 Date = DateTime.Parse(result.Element("Column0").Value),
                                 KPI = (result.Attribute("Column1").Value),
                                 Value = int.Parse(result.Attribute("Column2").Value)
                             });
    return resultSet;
}

MarcinJuraszek · Accepted Answer · 2014-03-16 17:29:32Z

3

XML is case-sensitive, so you have to exactly match element name to make it work. ROW and row are two different elements.
Columns1 and Column2 are elements, not attributes.
You have to use namespace for Column0/Column1/Column2 as well.
Use explicit cast to DateTime/string/int instead of Value property + Parse method calls. It's just better.

Update

Column2 contains decimal/double value, not ints:

XNamespace ns = @"urn:schemas-microsoft-com:xml-analysis:rowset";
IEnumerable resultSet = (from result in xmlDoc.Root.Descendants(ns + "ROW")
                         select new
                         {
                             Date = (DateTime)result.Element(ns + "Column0")),
                             KPI = (string)result.Element(ns + "Column1")),
                             Value = (decimal)result.Element(ns + "Column2"))
                         });

edited Mar 16, 2014 at 17:29

answered Mar 16, 2014 at 17:24

MarcinJuraszek

126k15 gold badges200 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Navyseal Over a year ago

Hey Thanks for the help. When I try the code. I get the below error Message = "Value cannot be null.\r\nParameter name: element"

MarcinJuraszek Over a year ago

@Navyseal Try it again, I've missed one ns + .

Navyseal Over a year ago

Hmm, when I try with the added namespace. I get a "base {System.SystemException} = {"Input string was not in a correct format."}" error.

Selman Genç · Accepted Answer · 2014-03-16 17:22:26Z

2

ROW and Row are not the same. Xml is case-sensitive. You need to use Row with lower-case because that is your element's name.

answered Mar 16, 2014 at 17:22

Selman Genç

102k15 gold badges126 silver badges193 bronze badges

Comments

koes · Accepted Answer · 2016-09-01 09:15:11Z

0

Finally I'm using this code:

returnValue = (from rows in doc.Root.Descendants(ns + "Row")
select (from columns in rows.Elements() select object)columns.Value).ToArray()
).ToList();

answered Sep 1, 2016 at 9:15

koes

1

1 Comment

AgataB Over a year ago

Can you expand your answer a little?

Collectives™ on Stack Overflow

Fetching an XML using LINQ

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related