When bulk loading XML from a file that contains an encoding declaration that you want to apply, specify the SINGLE_BLOB in OPENROWSET. This option ensures that the XML parser in SQL Server imports the data according to the encoding scheme specified in the XML declaration. For example, the following INSERT statement inserts an XML instance in a single column table.
USE tempdb
CREATE TABLE T (IntCol int, XmlCol xml)
GO
INSERT INTO T(XmlCol)
SELECT * FROM OPENROWSET(
BULK 'c:\SampleFolder\SampleData3.txt',
SINGLE_BLOB) AS x
To run this bulk load, create a utf-8 encoded file (c:\SampleFolder\SampleData3.txt) with the following sample instance that specifies the UTF-8 encoding scheme and then execute the INSERT statement.
<?xml version="1.0" encoding="UTF-8"?>
<Root>
<ProductDescription ProductModelID="5">
<Summary>Some Text</Summary>
</ProductDescription>
</Root>
By using SINGLE_BLOB in this way, you can avoid a mismatch between the encoding of the XML document (as specified by the XML encoding declaration) and the string codepage implied by the server.