Sort XML using XPath in .Net

Question

I'm trying to retrieve the most recent "final" record from a list like:

<StatusRecords>
<Status>
<Name>final</Name>
<Date>1/1/2006</Date>
</Status>
<Status>
<Name>final</Name>
<Date>1/1/2010</Date>
</Status>
<Status>
<Name>interim</Name>
<Date>1/1/2005</Date>
</Status>
</StatusRecords>

The data in question is supposed to have only one "final" status entry, and I wrote the code to simply find that one entry. But we've found the actual data is filled with multiple "final"s like this. So I need to get the one with the highest date.

I see that this is possible using Xpath, which is how I handle the parsing already, but I do not understand how to translate this into VB.Net code. Does anyone have a snippet?

Are there any constraints, such as the XML file is too large to hold in RAM? — Andrew Morton, Oct 10 '18 at 22:02

TonyE · Answer 1 · 2018-10-10T22:02:11.167

1

Here's one way to do it

    Dim myxml As String = "<StatusRecords><Status><Name>final</Name><Date>1/1/2006</Date></Status><Status><Name>final</Name><Date>1/1/2010</Date></Status><Status><Name>interim</Name><Date>1/1/2005</Date></Status></StatusRecords>"
    Dim xp As XPathDocument = New XPathDocument(New StringReader(myxml.ToString))
    Dim xn As XPathNavigator = xp.CreateNavigator
    Dim xi As XPathNodeIterator = xn.Select("//StatusRecords/Status")
    Dim thedate As Date = "1/1/1900" 
    Dim loopdate As Date
    Dim maxfinal As String = "?"
    Do While xi.MoveNext
        loopdate = CDate(xi.Current.SelectSingleNode("Date").InnerXml)
        If loopdate > thedate Then
            thedate = loopdate
            maxfinal = xi.Current.SelectSingleNode("Name").InnerXml
        End If
    Loop
    Response.Write(maxfinal & ":" & thedate.ToShortDateString)

Output is

final:1/1/2010

edited Oct 10 '18 at 22:02

answered Oct 10 '18 at 21:46

TonyE

319
2
4

This is non-elegant because dates are viewed as strings in xml. If you can get your dates into YYYYMMDD format, then you could run a more efficient like xn.Select("//StatusRecords/Status[Date = max(../Status/Date)]") – TonyE Oct 10 '18 at 21:57
that sounds like the solution I am looking for. Looking through the file, the dates are in several formats, but the one I'm looking at is "2018-06-02". Will that work? – Maury Markowitz Oct 11 '18 at 13:02
I played around with Tony's solution in the XPath tester, and the problem is that it cannot parse the dates because of the formatting. I have not used XPath in this way, is there some way to reformat the dates from YYYY-MM-DD to YYYYMMDD? If so, I think that's what I'm looking for. – Maury Markowitz Oct 11 '18 at 13:11

score 0 · Answer 2 · answered Oct 10 '18 at 21:58

If it doesn't have to be via XSL, then you can use LINQ-to-XML to get the data, like this:

Module Module1

    Sub Main()
        Dim x = <StatusRecords>
                    <Status>
                        <Name>final</Name>
                        <Date>1/1/2006</Date>
                    </Status>
                    <Status>
                        <Name>final</Name>
                        <Date>1/1/2010</Date>
                    </Status>
                    <Status>
                        <Name>interim</Name>
                        <Date>1/1/2005</Date>
                    </Status>
                </StatusRecords>

        Dim ci = Globalization.CultureInfo.GetCultureInfo("en-US")

        Dim y = x...<Status>.<Date>.Max(Function(d) DateTime.Parse(d.Value, ci))

        Dim z = x...<Status>.First(Function(s) DateTime.Parse(s.<Date>.Value, ci) = y)

        Console.WriteLine("Latest date: " & y.ToString("yyyy-MM-dd"))
        Console.WriteLine(z)

        Console.ReadLine()

    End Sub

End Module

which outputs:

Latest date: 2010-01-01
<Status>
  <Name>final</Name>
  <Date>1/1/2010</Date>
</Status>

Maury Markowitz · Answer 3 · 2018-10-11T14:10:27.417

TonyE's comment above was ultimately the key to solving this problem. The issue is that you need to convert the date to number and then use max on that number. The result is messy, but appears to be working:

/StatusRecords/Status[number(concat(substring(Date, 1, 4),substring(Date, 6,2),substring(Date, 9, 2),substring(Date, 12, 2),substring(Date, 15, 2),substring(Date, 18, 2)))=max(/StatusRecords/Status/number(concat(substring(Date, 1, 4),substring(Date, 6,2),substring(Date, 9, 2),substring(Date, 12, 2),substring(Date, 15, 2),substring(Date, 18, 2))))]

I suspect that there is a simpler and more compact format, but working is a feature. Thanks Tony!

UPDATE: turns out the hms portion was needed too.

Sort XML using XPath in .Net

3 Answers3