What SQL Server datatype to use for mixed XML and HL7v2 data?

Question

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.

Answer 1

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.

Answer 2

As Nicks say, converting pipe delimited to XML and then persist in XML is the best option, trying to persist xml and pipe delimited values in a same column for me it make no sense, as on source they are different data types.

Answer 3

Use HAPI to transform the HL7 messages from ER7 (pipe delimited) to XML encoding. That way you can use a single SQL Server XML column for everything. And it will give you the added benefit of being able to query into HL7 message contents using XQuery.