Drawing Blanks

Premature Optimization is a Prerequisite for Success

Updating SQL XML DateTimes in a XmlSerializer-friendly way

leave a comment »

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())[1] with
      sql:variable("@ExportedDateXmlValue")
  ')

 

Where @ExportedDateXmlValue is a varchar parameter that is passed from the application in the following format:

XmlConvert.ToString(DateTime.Now, XmlDateTimeSerializationMode.Local)

 

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.

Advertisements

Written by bbzippo

04/03/2010 at 8:47 am

Posted in programming

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: