Parse xml using Linq to xml when there's no tag name

Question

I have xml. I'm retrieving information using this code:

 XNamespace ns = "urn:schemas-microsoft-com:office:excel";
            var xdoc = XDocument.Load(@"path");

            IEnumerable<string> ss = xdoc.Descendants(ns + "Crn")
                                         .Elements(ns + "Text")
                                         .Select(x => (string)x);




            using (System.IO.StreamWriter file =
            new System.IO.StreamWriter(@"path", true))
            {
                foreach (var x in ss)
                {
                    file.WriteLine(x);
                }
            }

But I want to retrieve only last - 1 tag text. I've added there text "this text". How can I do that? If I'll add .LastOrDefault() after .Element It throws an error..

And here's my XML

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<SupBook>
<Xct>
<Crn>
          <Row>9</Row>
          <ColFirst>1</ColFirst>
          <ColLast>3</ColLast>
          <Text>02</Text>
          <Text>this text</Text>
          <Text>asd</Text>
        </Crn>
        <Crn>
          <Row>10</Row>
          <ColFirst>1</ColFirst>
          <ColLast>3</ColLast>
          <Number>25</Number>
          <Text>this text</Text>
          <Text>asd</Text>
        </Crn>
        <Crn>
          <Row>11</Row>
          <ColFirst>1</ColFirst>
          <ColLast>3</ColLast>
          <Number>62</Number>
          <Text>this text</Text>
          <Text>asd</Text>
        </Crn>
 </Xct>
    </SupBook>
  </ExcelWorkbook>
</Workbook>

I can't change that xml file. I know there must be column names instead of text tag but it's not written by me

Answer 1

var ss = xml.Descendants(ns + "Crn")
    .Select(x =>
    {
        var a = x.Elements(ns + "Text").ToArray();
        return a.Length > 1 ? a[a.Length - 2].Value : "";
    });

That should do it :)

Gets the second last now ;)

Answer 2

I guess you are looking for last but 1 tag (guessed it by this statement I've added there text "this text".).

This should give you the expected result:-

IEnumerable<string> ss = xdoc.Descendants(ns + "Crn")
          .Select(x => x.Elements(ns + "Text").Reverse().Skip(1).Take(1).DefaultIfEmpty())
          .SelectMany(x => x)
          .Select(x => (string)x);

Explanation:-

You can first project all the Text nodes using Select. After this you can reverse the sequence skip 1 element and take 1 element. I have added DefaultIfEmpty just for safety in case there is just 1 element. Finally flatten the sequence using SelectMany and fetch the data.