XML in SQL Server - Query datetime

I have XML stored in a SQl database like this:



<myrootelement xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <product>something</product>
  <deliverydate>2024-12-26T21:39:24</deliverydate>
</myrootelement>



Let's say I have 10,000 records like the above, and want to get the count of all records with a deliverydate greater than the current date. How can we do that?


First make sure there's a correct schema attached to the column in which the xml is stored. Then XQuery can be used to select the records. The fastest way to query XML in SQL Server is to use the 'exist' function:

SELECT count(*) FROM [table]

WHERE [column].exist('/ myrootelement[deliverydate > xs:dateTime(''2012-07-15T00:00:00'')]') = 1



The function xs:dateTime() is used to parse a string to a datetime value.  Please note that two single quote signs are written, and not a double quote sign.


Using parameters
What if we don't want to use a hardcoded value, but a parameter? I thought that would be pretty straightforward, so I wrote this query:

declare @rcurrentdate datetime; 

set @currentdate = getdate()

SELECT  count(*)  FROM [table]

WHERE [column].exist('/ myrootelement[deliverydate > xs:dateTime('' + @currentdate + '')]') = 1



But this resulted in an error:



Msg 9319, Level 16, State 1, Line 16
XQuery [Documenten.XmlStuurinformatie.exist()]: Static simple type validation: Invalid simple type value ' + @stringdate + '.

Changing the number of single quote signs didn't matter:

SELECT  count(*)  FROM [table]

WHERE [column].exist('/ myrootelement[deliverydate > xs:dateTime(' + @currentdate + ')]') = 1

Msg 8172, Level 16, State 1, Line 15

The argument 1 of the XML data type method "exist" must be a string literal.

or

SELECT  count(*)  FROM [table]

WHERE [column].exist('/ myrootelement[deliverydate > xs:dateTime(''' + @currentdate + ''')]') = 1

Msg 8172, Level 16, State 1, Line 15

The argument 1 of the XML data type method "exist" must be a string literal.

The right way
So how can we use a parameter in an XQuery? By telling the query to use a SQL parameter. For example:

declare @rcurrentdate datetime; 

set @currentdate = getdate()

SELECT  count(*)  FROM [table]

WHERE [column].exist('/ myrootelement[deliverydate > xs:dateTime(sql:variable("@currentdate"))]') = 1