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.