XML column in SSIS has byte-order-mark

Question

I'm using an oledb data source in an SSIS package to pull a column from a database. The column is XML data type. In SSIS, it is automatically recognized as data type DT_NTEXT. It's going to a script component where I'm trying to load it into a System.Xml.XmlDocument. This is the code that I'm using to get the xml data into a string:

System.Text.Encoding.Default.GetString(Row.Data.GetBlobData(0, Row.Data.Length))

Is this the correct way?

One odd thing that I'm seeing is that on one server, I get a byte-order-mark in the resulting string, and another server I don't. I wouldn't mind knowing why that is the case, but my real desire is how to get this string without the BOM.

Help me, Stack Overflow, you're my only hope...

Answer 1

This is the only way I was able to get it to work:

System.Text.UnicodeEncoding.Unicode.GetString(...).Trim()

The .Trim() removes the BOM. I'm not sure if this is the "right" way, but it's the only thing that's worked so far.