使用LINQ获取XML

Question

下面是我用来选择节点的XML结构。

<?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>

基本上我要做的就是选择"Row"下的所有元素，在我的例子中是"Column0"，"Column1"one_answers"Column2"。

下面是我用来获取结果的代码。不知何故，它不起作用。任何帮助都是有帮助的。

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;
}

Answer 1

1. XML是区分大小写的，因此必须精确匹配元素名才能使其工作。ROW和row是两个不同的元素。
2. Columns1和Column2是元素，不是属性。
3. Column0/Column1/Column2也必须使用命名空间
4. 使用显式强制转换为DateTime/string/int，而不是Value属性+ Parse方法调用。

1. Column2包含decimal/double的值，不包含int的值s:

   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"))
                            });
   

Answer 2

ROW和ROW不一样。Xml是区分大小写的。您需要使用小写的Row，因为这是您的元素名称。

Answer 3

最后我使用以下代码:

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