If you use XML string as parameter to update your SQL database table as I discussed in my last post, if the server is SQL Server 2000 and the XML string contains a datetime string, you will get a SQL error as this:
Syntax error converting datetime from character string
Cause:
When generating XML string from DataTable object, all DateTime values are converted into strings in ISO 8601 format. However, ISO 8601 format is support only in SQL Server 2005 or higher.
Resolution:
If upgrading SQL Server 2000 to 2005 or higher is not an option, then you will have to manually convert the ISO 8601 formatted datetime strings into the regular datetime strings before passing on to your SQL Server 2000.
Here is the code snippet I used to convert ISO 8601 datetime string to the regular datetime string:
- protected string FormatXMLDateTimeString(string xmlString, string nodeName)
- {
- XmlDocument doc = new XmlDocument();
- doc.LoadXml(xmlString);
- XmlNodeList nodes = doc.SelectNodes(nodeName);
- foreach (XmlNode node in nodes[0].ChildNodes)
- {
- if (node.Name.IndexOf("DateTime") != -1) // best way is to use regular expression
- {
- node.InnerXml = DateTime.Parse(node.InnerXml).ToString();
- }
- if (node.InnerXml == "false") // SQL Server 2000 cannot convert true/false to bit
- {
- node.InnerXml = "0";
- }
- if (node.InnerXml == "true")
- {
- node.InnerXml = "1";
- }
- }
- StringWriter sw = new StringWriter();
- XmlTextWriter xtw = new XmlTextWriter(sw);
- doc.WriteTo(xtw);
- xtw.Close();
- return sw.ToString();
- }
Also, as mentioned in the comments in the code snippet, SQL Server 2000 cannot convert true/false to database bit type, so you will need to manually convert them to 1 (true) and 0 (false), otherwise, you will get a SQL error as follows:
Syntax error converting the nvarchar value 'false' to a column of data type bit.
Hope this will help someone.
