Can I force System.Data.Linq.DataContext to store XML into a SQL Server table's XML column preserving whitespace or is there any other way?
My test code is as follows:
Guid MyNewQid = Guid.NewGuid();
using (DataClassesDataContext context = DataClassesDataContext.CreateDataContext())
{
Guid myQID = Guid.Parse("{28da4eca-2c1a-4647-xxx-b398d1xxx013}");
FromSwiftBck t2sData = context.GetTable<FromSwiftBck>().FirstOrDefault(o => o.QID == myQID);
string messageLoaded = t2sData.CompleteMessage;
int appHeaderLenght = messageLoaded.IndexOf("</AppHdr>") + 9;
string strMsgHeader = messageLoaded.Substring(0, appHeaderLenght);
string strMsgDocument = messageLoaded.Substring(appHeaderLenght);
XElement serv = XElement.Parse(strMsgDocument, LoadOptions.PreserveWhitespace);
SwiftOut swOut = new SwiftOut();
swOut.QID = MyNewQid;
swOut.InsertTime = DateTime.Now;
swOut.Message = serv;
swOut.Status = -100;
swOut.Namespace = swOut.Message.GetDefaultNamespace().NamespaceName;
swOut.MessageName = swOut.Message.Descendants().First().Name.LocalName;
context.SwiftOuts.InsertOnSubmit(swOut);
context.SubmitChanges();
}
using (DataClassesDataContext context = DataClassesDataContext.CreateDataContext())
{
SwiftOut swOutStored = context.GetTable<SwiftOut>().FirstOrDefault(o => o.QID == MyNewQid);
XElement storedXdoc = swOutStored.Message;
MessageBox.Show(storedXdoc.ToString());
context.SwiftOuts.DeleteOnSubmit(swOutStored);
context.SubmitChanges();
}
}
When I read the data from the new context, I get the xml with whitespaces removed.
The XML datatype does NOT preserve your exact textual representation of the XML - it parses and tokenizes the XML for more optimal storage.
SQL Server does not guarantee that the XML returned is exactly the same (in terms of formatting, whitespaces etc.) as the input.
There's also no option or config setting to my knowledge to change this behavior.
See here for more details:
Native storage as xml data type
The data is stored in an internal representation that preserves the XML content of the data. ..... The InfoSet content may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.
(emphasis was added by me)