This almost certainly isn't an LLBLGen Pro issue, but I thought I'd mention it here just in case you guys have come across it before, or LLBLGen is actually doing something with an xml-bound string.

I want to store some data such as "A\r\nB\rC\nD" in an Xml Element within a Xml-Typed column. No matter what I do, somewhere in the process, the line endings get changed to '\n' (although multiple spaces are not so this isn't a full normalization), which is effectively corrupting the data.

I am using SQL Server 2012 with LLBLGEn v4.0 Final (July 18th).

I have posted also on Stack Overflow at http://stackoverflow.com/questions/18822677/sql-server-2012-xml-type-preserve-significant-whitespace since it is more likely to be in Sql Server.

If you enable DQE tracing, are the \r's in the parameter value of the insert statement?

I added the DQE tracing as you suggested.

If I open the app log file with TextPad as a binary file (so it doesn't get a chance to change the line endings) I see a hex dump and it has 0D in the expected places. The log file is either UTF-8 or ANSI (either way not UTF-16).

The trouble is that I can't confirm if that is what arrives at the other end since as soon as I copy text in SQL Server profiler and paste it into Textpad and save it (without altering line endings) it _appears _that all three ending types are now "\r\n" but I still suspect the copy/paste is doing that.

Is there a chance the problem is in the retrieval process?

How did you find out it has been altered?

Walaa wrote:

Is there a chance the problem is in the retrieval process?

That might be the problem.

In the database, I've now got the Xml Value column as something which should work. This query returns 3 views of the data:

SELECT Value, convert(varchar(max), Value), convert(varbinary(max), Value) FROM Memento WHERE ID=4

Raw Xml (shows in blue in MSSMS): <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>

Text (note the entitization for the '\r' chars) <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>

Binary version (I've highlighed the "A \n": 0xFFFE3C0045006E0074007200690065007300200078006D006C006E0073003A007800730069003D0022006800740074 0070003A002F002F007700770077002E00770033002E006F00720067002F0032003000300031002F0058004D004C0053 006300680065006D0061002D0069006E007300740061006E00630065002200200078006D006C006E0073003A00780073 0064003D00220068007400740070003A002F002F007700770077002E00770033002E006F00720067002F003200300030 0031002F0058004D004C0053006300680065006D00610022003E003C0045006E0074007200790020006B00650079003D 0022004600690072006D004E0061006D00650022003E003C00560061006C007500650020007800730069003A00740079 00700065003D0022007800730064003A0073007400720069006E00670022003E004100260023007800300044003B0 00A004200260023007800300044003B0043000A0044003C002F00560061006C00750065003E003C002F0045006E0 07400720079003E003C002F0045006E00740072006900650073003E00

So the database side looks correct but what I actually see back on the entity Value property is "A\r\nB\r\nC\r\nD". I am guessing that it is just '\n's being returned and something local is converting them to '\r\n' and that may be a different issue again though.

So if LLBLGEN wants a string on the App side but knows it is an Xml column, in what format is it asking for data to be retrieved? And what happens to it after that?

(I have ORMProfiler if that can show a lower-level of data. I've not used it however so would need a bit of instruction if that would help)

You can check the field's .NET type in the Designer. (Project Explorer -> Edit Entity). By default it should be just a string.

I bet it's something in the presentation layer.

Could you please debug and break the execution just after the entity is fetched and check the field's value?

So the database side looks correct but what I actually see back on the entity Value property is "A\r\nB\r\nC\r\nD"

This is just after the entity is fetched and is as close as I can get.

ORMProfiler 1.5 alpha/beta doesn't reveal anything either. It shows the result set as "...<Value xsi.type="xsd.string">A B C D</Value>..."

We don't do any conversion on the data. XML Types are returned as strings by SqlClient, so there's some conversion there, but that's on the SqlClient side: inside the entity it's a string, the one you set the property to, and when we read the value from the datareader, it's a string too, not xml data in any xml shape or form, just a string, and we don't do any conversion on that.

I.o.w.: if \r\n or \n are converted somewhere, it's inside SqlClient and/or the DB, not our code.

Unless you're doing xpath queries inside the DB on the field, IMHO it's better to simply store the data in an nvarchar field, so you don't get any conversions done by sqlclient

Yeah, I thought that would be the case.

I've done some poking and found that the client-side is actually getting the correct binary data.

However the SqlXml class (SqlDataReader directs all Xml access through that) makes it impossible to change any settings on the internal XmlReader/XmlWriter it uses.

So I give up. Xml columns with a search-facility was a nice to have but its back to nvarchar for now.