Updating SQL XML DateTimes in a XmlSerializer-friendly way
Storing XmlSerialized objects in a table may save you tons of time in certain scenarios. But sometimes it may be tricky to modify particular nodes of the XML. The latest challenge that I ran into was how to modify DateTime nodes. In SQL Server 2005 XQuery doesn’t directly support DateTimes, so you need to update it as string. And you must watch the format so that the value can be correctly deserialized. Here is the method that worked for me:
UPDATE MyReports SET ReportXml.modify(' replace value of (/ReportInfo/ExportedDate/text()) with sql:variable("@ExportedDateXmlValue") ')
Where @ExportedDateXmlValue is a varchar parameter that is passed from the application in the following format:
Formatting the value in the app ensures compatibility with XmlSerializer.
Note that some XQuery operations require SET Arithabort ON on your session, and ADO.Net still doesn’t send this option by default, so you may need to send it before sending the SQL.