Incorrect encoding name syntax

Question

Code:

declare @filedata xml 
select @filedata = BulkColumn from openrowset(bulk 'E:\Scripts\apc.xml',single_blob) x;

the above is my code for which I got the error:

Msg 9442, Level 16, State 1, Line 2 XML parsing: line 1, character 38, incorrect encoding name syntax

could anyone help me with how I can resolve this? I can't understand where the syntax is wrong.

Answer 1

SQL-Server cannot import any kind of encoding...

You are importing this file as BLOB, this is just a bunch of bytes. This must be interpreted as a data type SQL Server knows.

You can either try to declare your variable as VARCHAR(MAX) or as NVARCHAR(MAX) and hope, that the content of this variable is what you expect.

In this case you can cut off the entire xml-declaration (<?xml blah?>) using CHARINDEX and SUBSTRING or STUFF. Alternatively you can use REPLACE to set a different value to your encoding="blah". Use UTF-8 if VARCHAR worked for you, or UTF-16 in case of NVARCHAR.

If this does not help, you must change the file on disc before you read it. Best is utf-16, which can be read into XML directly. But don't forget to write this into the encoding or to strip off the declaration.