Select child nodes until attribute value in xpath (SQL Server)

Question

I have an XML column in SQL Server database (SQL Server 2012) with the following structure:

<history>
    <status updatedAt="2013-11-30" active="true" />
    <status updatedAt="2013-11-15" active="false" />
    <status updatedAt="2012-05-10" active="true" />
    <status updatedAt="2012-01-30" active="true" />
</history>

Newer statuses are added to the column as top nodes.

I need to select a subset of <status> nodes which includes first node which has updatedAt attribute less then or equal to given date AND all preceding nodes (or, all <status> nodes until updatedAt attribute is less then or equal to the given date).

How can I achieve this using XPath?

DECLARE @date DATE = '2012-30-10';
SELECT Statuses = Statuses.query('what should be there?')

For now I ended up with this query:

SELECT Statuses = Statuses.query'((/history/pricing[@updatedAt <= sql:variable("@date")])[1])')

but it returns the first node only, how can I include all its preceding siblings too?

Answer 1

For fetching all preceding siblings, use the preceding-siblings axis and select all siblings before the last matching <pricing/> tag.

SELECT Statuses = Statuses.query('
  /history/pricing[
      @updatedAt <= sql:variable("@date") and last()
  ]/preceding-sibling::pricing')

By the way, while your example data has <status/> tags, your query expects <pricing/> tags?

---

I forgot MS SQL Server doesn't support that axis, but it supports the node order operator << which you can use to work around that. This query says "select all pricing nodes which occur before the last pricing node with this value".

/history/pricing[. << (/history/pricing[@updatedAt <= "2012-30-10"])[last()]]

Have a try on SQL Fiddle.