当只有一个元素使用linq to xml时返回一个整数数组

Question

我有一个需求，我需要为只有一个值的选择返回一个数组(在xml中)，因为将使用数据的数据结构在其输入中期望一个整数数组(一个具有一个元素的数组)。

这就是我的XML文件的样子

<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:string" minOccurs="0" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;F&amp;R Sales, Balances, Exposures and Limits&quot;.&quot;Financial Calendar&quot;.&quot;Month Year Short Name&quot;" saw-sql:displayFormula="&quot;Financial Calendar&quot;.&quot;Month Year Short Name&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Financial Calendar" saw-sql:columnHeading="Month Year Short Name" saw-sql:isDoubleColumn="false" saw-sql:columnID="c29e370c0db340ad7" />
        <xsd:element name="Column1" type="xsd:string" minOccurs="0" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula=" CASE WHEN &quot;F&amp;R Sales, Balances, Exposures and Limits&quot;.&quot;GRD Product Current&quot;.&quot;GRD Product Level 5 Name&quot; ='Bill financing' THEN 'Deposits' WHEN &quot;F&amp;R Sales, Balances, Exposures and Limits&quot;.&quot;GRD Product Current&quot;.&quot;GRD Product Level 5 Name&quot; IN ('Operating lease','Finance lease') THEN 'Lending' WHEN &quot;F&amp;R Sales, Balances, Exposures and Limits&quot;.&quot;GRD Product Current&quot;.&quot;GRD Product Level 5 Name&quot; ='Hire purchase and equipment loans' THEN 'Fee Based' ELSE &quot;F&amp;R Sales, Balances, Exposures and Limits&quot;.&quot;GRD Product Current&quot;.&quot;GRD Product Level 5 Name&quot; END" saw-sql:displayFormula=" CASE  WHEN &quot;GRD Product Current&quot;.&quot;GRD Product Level 5 Name&quot; = 'Bill financing' THEN 'Deposits' WHEN &quot;GRD Product Current&quot;.&quot;GRD Product Level 5 Name&quot; IN ('Operating lease', 'Finance lease') THEN 'Lending' WHEN &quot;GRD Product Current&quot;.&quot;GRD Product Level 5 Nam" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="GRD Product Current" saw-sql:columnHeading="Product" saw-sql:isDoubleColumn="false" saw-sql:columnID="c062984f028c318df" />
        <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="Actual" saw-sql:isDoubleColumn="false" saw-sql:columnID="ca1e1d4f511a8bf93" />
        <xsd:element name="Column3" 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;)*1.5" saw-sql:displayFormula="&quot;Balances - Spot&quot;.&quot;Closing Balance&quot; * 1.5" saw-sql:aggregationRule="complex" saw-sql:aggregationType="agg" saw-sql:tableHeading="Balances - Spot" saw-sql:columnHeading="Forecast" saw-sql:isDoubleColumn="false" saw-sql:columnID="ca00747c911de130f" />
        <xsd:element name="Column4" 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;Limits&quot;.&quot;Limit Amount&quot;*2" saw-sql:displayFormula="&quot;Limits&quot;.&quot;Limit Amount&quot; * 2" saw-sql:aggregationRule="complex" saw-sql:aggregationType="agg" saw-sql:tableHeading="Balances - Spot" saw-sql:columnHeading="Target" saw-sql:isDoubleColumn="false" saw-sql:columnID="c88758b2e449f89d0" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:schema>
  <Row>
    <Column0>Sep-13</Column0>
    <Column1>Deposits</Column1>
    <Column2>330393232.5</Column2>
    <Column3>495589848.75</Column3>
    <Column4>703232974</Column4>
  </Row>
  <Row>
    <Column0>Sep-13</Column0>
    <Column1>Fee Based</Column1>
    <Column2>111868709.42</Column2>
    <Column3>167803064.13</Column3>
    <Column4>703232974</Column4>
  </Row>
  <Row>
    <Column0>Sep-13</Column0>
    <Column1>Lending</Column1>
    <Column2>18146873.33</Column2>
    <Column3>27220309.995</Column3>
    <Column4>703232974</Column4>
  </Row>
</rowset>

我正在尝试做这样的事情来将值转换为整数数组。

  var  resultSet = (xmlDoc.Root.Descendants(ns + "Row").Select(result => new
            {
                MonthYearShortName = (DateTime) result.Element(ns + "Column0"),
                Product = (String) result.Element(ns + "Column1"),
                Actual = (decimal) result.Element(ns + "Column2"),
                Forecast = (decimal) result.Element(ns + "Column3"),
                Target = result.Elements(ns + "Column4").Select(s => Convert.ToDecimal(s.FirstNode)).ToArray()
            }));

我得到一个异常，由于转换基础{System.SystemException} = {"Unable to cast object of type 'System.Xml.Linq.XText' to type 'System.IConvertible'."}

Answer 1

转换为decimal而不是在Select方法中使用Convert.ToDecimal:

var resultSet = (xmlDoc.Root.Descendants(ns + "Row").Select(result => new
{
    MonthYearShortName = (DateTime)result.Element(ns + "Column0"),
    Product = (String)result.Element(ns + "Column1"),
    Actual = (decimal)result.Element(ns + "Column2"),
    Forecast = (decimal)result.Element(ns + "Column3"),
    Target = result.Elements(ns + "Column4").Select(s => (decimal)s).ToArray()
}));

但是，这只有在您期望存在多个Column4元素时才有意义。否则，您只需执行以下操作:

    Target = (decimal)result.Element(ns + "Column4")

或获取decimal[]只有一个值:

    Target = new []  { (decimal)result.Element(ns + "Column4") }

Answer 2

根据您的编辑，您是否期望多个Column4条目?这个定义并没有让它看起来像这样，所以这个方法应该可以工作:

Target = (decimal)result.Element(ns + "Column4")

否则，如果您确实需要一个数组，则在FirstNode调用中添加一个ToString。

Target = result.Elements(ns + "Column4").Select(s => Convert.ToDecimal(s.FirstNode.ToString())).ToArray()

也就是说，不清楚为什么你用FirstNode方法采取了不同的路线。这应该可以工作，对我来说似乎更清晰:

Target = result.Elements(ns + "Column4").Select(s => (decimal)s).ToArray()