SQL Server 2012 XML Type Preserve Significant Whitespace

Question

I am trying to store some data in an XML-Typed Column which, when the data is a string, will preserve the exact text and not replace or adjust embedded line-endings specifically CR & LF.

So a C# string like "A\r\nB\rC\nD" needs to come back exactly so but it would appear that the XML conversion insists on replacing those line-endings with LF which is effectively corrupting the data. I am aware why this normalization happens when reading Xml files etc. but this isn't a file and the whitespace is significant.

I have tried CONVERT(xml, N'', 1);

I have tried adding xml:space="preserve" as an attribute to the element containing the data.

I have tried using & # x D ; inplace of CR

For background:

I am checking the contents of string by using CONVERT(varbinary(max), Value) then copying the output into TextPad so I can find and see exactly what chars are stored.

I am reasonable sure that the data is getting to SQL Server with the endings unchanged (I am using XmlWriter settings with NewLineHandling = NewLineHandling.None and LLBLGen treats the data as a string) but not certain since copying and pasting changes the endings so I can't inspect them.

The Xml serialization code is basically wrapping a Dictionary

    void IXmlSerializable.WriteXml(XmlWriter writer)
    {
        var list = new List<Entry>(Values.Count);

        foreach (var entry in Values)
        {
            list.Add(new Entry(entry.Key, entry.Value));
        }

        MementoXmlSerializer.Serialize(writer, list);
    }

    [XmlType("Entry")]
    public struct Entry
    {
        public Entry(string key, object value): this()
        {
            Key = key;
            Value = value;
        }

        [XmlAttribute("key")]
        public string Key { get; set; }

        [XmlElement("Value")]
        public object Value { get; set; }
    }

Where MementoSerializer is defined as:-

static readonly Type[] AdditionalTypes =
{
    typeof(int[]),
    typeof(string[])
};

static readonly XmlSerializer MementoXmlSerializer = new XmlSerializer(typeof(List<Entry>), null, AdditionalTypes, new XmlRootAttribute("Entries"), null);

The pattern used by LLBLGen and seen in Sql Server Profiler looks like this:-

declare @p3 xml
set @p3=convert(xml,N'<Entries xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><Entry key="FirmName"><Value xsi:type="xsd:string">A
B
C
D</Value></Entry></Entries>')
exec sp_executesql N'UPDATE [TIPS].[dbo].[Memento] SET [Value]=@p1 WHERE ( [TIPS].[dbo].[Memento].[ID] = @p2)',N'@p1 xml,@p2 int',@p1=@p3,@p2=4

Any help appreciated.

Answer 1

It would appear that SqlXml and SqlXmlStreamWrapper instances created by SqlDataReader (in .Net 4.0 at least) have no way of changing any settings on how they decode the binary Xml data returned by Sql Server.

The binary data is definitely received correctly on the client side as a hex dump of the buffer shows:

4100 0D00 0A00 4200 0D00 4300 0A00 4400

I've given up on the idea of having xpath-searchable mementoes for now. It would have been a nice-to-have but I can't have my data not roundtripping reliably.

I changed the Value column type back to NVARCHAR(MAX) and everything is now working fine.