XPath Expression BETWEEN 2 dates

Question

I have xml loaded into a DataTable as a string.

Here is the code I'm currently using to loop through the rows in my DataTable:

foreach (DataTable table in ds.Tables)
        {
            foreach (DataRow row in table.Rows)
            {
                XmlDocument auction_31_xml = new XmlDocument();
                if(!string.IsNullOrEmpty(row[auctions_31].ToString()))
                {
                    auction_31_xml.LoadXml(row[auctions_31].ToString());
                    var x = auction_31_xml.SelectNodes("//AUCTION[AUCTION_DATE>20070111 and AUCTION_DATE<2013930]");
                    auction_count_31 += x.Count;
                }
             }
         }

Here is the structure of the XML:

<AUCTIONS>
<AUCTION AUCTION_ID="235342">
    <AUCTION_DATE>2007-06-29</AUCTION_DATE>
    <AUCTION_TIME>12:00P</AUCTION_TIME>
    <RENTAL_AUCTION_STATUS>1</RENTAL_AUCTION_STATUS>
    <UNIT_CONTENTS/>
    <SOLD_AMOUNT/>
    <TAX_COLLECTED/>
    <BUYER_TAX_EXEMPT/>
    <BUYER_TAX_EXEMPT_NUM/>
    <SOLD_TO>Unavailable</SOLD_TO>
    <NOTES/>
</AUCTION>
<AUCTION AUCTION_ID="228371">
    <AUCTION_DATE>2006-11-30</AUCTION_DATE>
    <AUCTION_TIME>04:30PM</AUCTION_TIME>
    <RENTAL_AUCTION_STATUS>1</RENTAL_AUCTION_STATUS>
    <UNIT_CONTENTS/>
    <SOLD_AMOUNT/>
    <TAX_COLLECTED/>
    <BUYER_TAX_EXEMPT/>
    <BUYER_TAX_EXEMPT_NUM/>
    <SOLD_TO>Unavailable</SOLD_TO>
    <NOTES/>
</AUCTION>

What I'm trying to do is get a count of all the AUCTION elements that have an AUCTION_DATE between January 11 2007 and September 30th 2013.

The XPATH expression I have is:

var x = auction_31_xml.SelectNodes("//AUCTION[AUCTION_DATE>20070111 and AUCTION_DATE<2013930]");

Any help would be greatly appreciated.

What isn't working? Also look up LINQ to XML

reggaeguitar
– reggaeguitar

2014-08-01 22:14:24 +00:00
Commented Aug 1, 2014 at 22:14 — reggaeguitar
– reggaeguitar, Commented Aug 1, 2014 at 22:14
the result of the SelectNodes is always coming back with 0

TRobinsonEXR
– TRobinsonEXR

2014-08-01 22:25:57 +00:00
Commented Aug 1, 2014 at 22:25 — TRobinsonEXR
– TRobinsonEXR, Commented Aug 1, 2014 at 22:25

Community · Accepted Answer · 2017-05-23 12:07:25Z

3

You need to transform date in the XML into a number with the same format as the compared number by using number(translate(the_date,'-','')) :

//AUCTION
    [
        number(translate(AUCTION_DATE,'-',''))>20070111 
            and 
        number(translate(AUCTION_DATE,'-',''))<20130930
    ]

Given input XML in this question, above XPath (formatted just for the sake of readability) able to return the first <AUCTION> node in XPath tester.

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Aug 1, 2014 at 23:07

har07

89.5k12 gold badges87 silver badges143 bronze badges

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

Collectives™ on Stack Overflow

XPath Expression BETWEEN 2 dates

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related