Search code examples
sql-serveropenrowset

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

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:

enter image description here

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.

enter image description here

Solution

  • 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

    enter image description here