Consider a column in an MS SQL database which will house either potentially large chunks or XML or pipe-delimited HL7v2 data.
Currently (due to not using forward-thinking) it's currently typed as XML because originally we were only ever accepting XML data. While technically this could work, it means that all the XML special characters in the HL7v2 messages are being encoded (& --> & etc.).
This is not ideal for what we are doing. If I were to convert this column to a different datatype, what would be recommended? I was thinking nvarchar(max) as it seems like it would handle it, but I'm not well-versed in SQL datatypes and the implications of using different types for such data.
There really isn't much of a choice other than nvarchar(max).
The other options are either varchar(max) or varbinary(max). You might need Unicode so you can't use varchar. It would work to store it as varbinary, but it would just be annoying to work with.