CODEWITHSUNDEEP
c#xmlxpath
TRobinsonEXR
TRobinsonEXR

Reputation: 65

XPath Expression BETWEEN 2 dates

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.

Upvotes: 1

Views: 717

Answers (1)

har07
har07

Reputation: 89285

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.

Upvotes: 3

Related Questions