Getting the current datetime in XQuery for Transact-SQL

Transact-SQL has a very handy data type: xml. You can use this to store pieces of XML in your database. The database will force this XML to be valid. It’ll make it valid if there is no large issue found or it’ll throw an error:

You can also perform XQuery statements on xml-fields of a record from your SQL-query. So you can also query into the XML from your query:

And recently I had to be able to alter the xml in a record with the current datetime. I had to insert a cancel-date in an empty node that could also not exist yet. I had to refresh my XQuery for starters, but then I didn’t find a direct solution for this. Regular XQuery can use the XPath function ‘fn:current-dateTime()‘, which will return the current datetime as I required. But T-SQL doens’t support this function… So I cooked up this solution by creating a T-SQL variable and using that variable as text in the XQuery statement.

And like so, I managed to insert the canceldate via the ‘sql:variable‘-function. I also found this page to be quite interesting to get me quickly up to speed on using XQuery in T-SQL.

Leave a Reply

Your email address will not be published. Required fields are marked *