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?

Since you have American style dates it would suffice to compare the dates as strings. However, string comparison (<, >) is not available in XPath 1.0 which would require you to break down the string dates into their components. Hence the question: are you allowed to use XPath 2.0? — Marcus Rickert, Dec 01 '13 at 09:40
My XML column is strongly typed, and **updatedAt** attribute is of type **xs:date** according to the schema. SQL Server compares it correctly with an SQL variable of type DATE. Please, see my update. Sql Server supports XPath 2.0, according to MSDN. — nativehr, Dec 01 '13 at 10:17
SQL Server implements large parts of XQuery 1.0 which includes XPath 2.0. — Jens Erat, Dec 01 '13 at 12:31
@nativehr: If you remove `[1]` from the last XQuery then you should receive all nodes not just first node. — Bogdan Sahlean, Dec 01 '13 at 13:33
Bogdan, thanks for your reply. I don't need all nodes, just first matched and all its preceding siblings — nativehr, Dec 01 '13 at 20:01

Jens Erat · Accepted Answer · 2013-12-01T14:40:02.037

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.

edited Dec 01 '13 at 14:40

answered Dec 01 '13 at 12:39

Jens Erat

37,523
16
80
96

SQL Server doesn't support `preceding-sibling` axis. Thanks for your answer, anyway! – nativehr Dec 01 '13 at 13:45
My bad, not the first time I trapped into that. I hope I will be able to remember that _at some time in future_... But it's not a real problem, added a workaround including SQL fiddle for testing. – Jens Erat Dec 01 '13 at 14:40
Jens, it was just a typo, there should be `status` tags instead of `pricing` of course. Your last example is what I was looking for, thank you again! – nativehr Dec 01 '13 at 19:43
You're welcome. SQL Server's lack of some XQuery capabilities is horrible, and I still haven't found a complete list of them. Another missing feature would be that you cannot use variables in element constructors' name fields. – Jens Erat Dec 01 '13 at 19:55

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

1 Answers1