SQL Server OPENROWSET(BULK 'C:\Temp\Test.xml', SINGLE_CLOB) Returns Garbled Leading Characters

Question

When writing the contents of an xml file to a nvarchar(MAX) column using OPENROWSET I end up with garbled leading characters.

Even when doing a select without the insert the result is the same.

SELECT Xml.BulkColumn
FROM OPENROWSET(BULK 'C:\Temp\Test.xml', SINGLE_CLOB) Xml

Result:

I have experimented with the various CODEPAGE options but get the same result.

SELECT Xml.BulkColumn
FROM OPENROWSET(BULK 'C:\Temp\Test.xml', CODEPAGE='RAW', SINGLE_CLOB) Xml

In notepad the file looks fine.

Answer 1

The solution was to change the encoding of the xml file from UTF-8 to Unicode and then use SINGLE_NCLOB instead of SINGLE_CLOB.

SELECT Xml.BulkColumn
FROM OPENROWSET(BULK 'C:\Temp\Test2.xml', SINGLE_NCLOB) Xml