É possível usar OPENROWSET para importar arquivos codificados em UTF8 de largura fixa?

Uma solução alternativa é apenas alterar o arquivo de formato para trazer a linha inteira, em massa, e fazer a substring em TSQL

Com arquivo de formato

<?xml version = "1.0"?>  
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
   <RECORD>  
      <FIELD xsi:type="CharTerm" ID="WholeLine" TERMINATOR="\n"/> 
   </RECORD>  
   <ROW>  
      <COLUMN SOURCE="WholeLine" NAME="WholeLine" xsi:type="SQLNVARCHAR"/> 
   </ROW>  
</BCPFORMAT>

O seguinte retorna os resultados desejados

SELECT SUBSTRING(WholeLine, 1,1) AS Col1,
       SUBSTRING(WholeLine, 2,1) AS Col2,
       SUBSTRING(WholeLine, 3,1) AS Col3,
       SUBSTRING(WholeLine, 4,5) AS Col4
FROM OPENROWSET
(
BULK 'mydata.txt',
FORMATFILE = 'myformat_file.xml',
CODEPAGE = '65001'
) AS X

do qual concluo que LENGTHestá contando bytes em vez de caracteres.

Isso está correto e não há como transformá-lo em caracteres.

A situação é análoga ao n em char( n ), varchar( n ), nchar( n ) e nvarchar( n ), onde 'n' denota o número de bytes , não caracteres. Veja a documentação :

Um equívoco comum é pensar que CHAR(n) e VARCHAR(n), o n define o número de caracteres. Mas em CHAR(n) e VARCHAR(n) o n define o comprimento da string em bytes (0-8.000). n nunca define números de caracteres que podem ser armazenados. Isso é semelhante à definição de NCHAR(n) e NVARCHAR(n). O equívoco acontece porque ao usar a codificação de byte único, o tamanho do armazenamento de CHAR e VARCHAR é n bytes e o número de caracteres também é n. No entanto, para codificação de vários bytes, como UTF-8, intervalos Unicode mais altos (128-1.114.111) resultam em um caractere usando dois ou mais bytes.

Isso é uma fonte de confusão para muitos, especialmente desde a introdução do suporte a UTF-8. Era possível antes com n(var)char e caracteres suplementares, mas relativamente raramente encontrado, eu diria.

Seria bom se o SQL Server estendesse seu suporte para caracteres em vez de bytes em várias áreas no futuro (incluindo OPENROWSET).

Enquanto isso, sua solução alternativa é a que eu provavelmente usaria também.

Só para colocar isso como mais uma opção, principalmente para quem não pode modificar o processo dentro do SQL Server:

Você também pode converter a codificação do arquivo de UTF-8 para UTF-16 LE (Little Endian; muitas vezes referido simplesmente como "Unicode" em muitos produtos da Microsoft). Você alteraria o seguinte (do que está postado na pergunta):

No arquivo de formato:

1. Alterar xsi:typede CharFixedparaNCharFixed
2. Dobre o valor para cada LENGTH(ex 2 -> 4, 5 -> 10)
3. Para <FIELD ID="LINE_BREAK" ...>:
   1. Alterar xsi:typede CharTermparaNCharFixed
   2. RemoverTERMINATOR="\r\n
   3. Adicionar LENGTH="4"(use "4" para "\r\n" ou 2 para "\n")

Na chamada para OPENROWSET():

1. Remover , CODEPAGE = '65001'como página de código é ignorado ao usar o "NChar*" xsi:types.

 
NOTAS

1. Precisar dobrar o LENGTHvalor é uma evidência ainda mais (e mais triste) que LENGTHé sempre bytes. Triste que eles não fizeram unidades de código para que um LENGTHde "1" recebesse qualquer caractere BMP, exatamente como você esperaria de NCHAR(1)/ NVARCHAR(1)dentro do T-SQL.

2. Por que mudar CharTermpara NCharFixedem vez de NCharTerm? Porque eu não conseguia NCharTermtrabalhar. Meus dados de teste estavam 100% corretos, mas o uso NCharTermsó importaria a primeira linha. Pode ser um bug.

3. Embora isso trate caracteres que em UTF-8 são 2 ou 3 bytes, nem essa opção nem UTF-8 manipulam caracteres combinados. Ou seja, o önos dados de amostra pode ser um único caractere (como nos dados de amostra na pergunta) ou pode ser uma combinação de um sem acento omais a marca diacrítica (2 caracteres, mas agora 3 bytes em UTF-8 como o diacrítico é de 2 bytes por si só, ou 4 bytes em UTF-16). Por exemplo, eu criei o novo personagem usando:

   SELECT N'o'+NCHAR(0x0308); -- ö
   

   e, em seguida, copie/cole a linha 3 para uma nova linha 4, trocando o primeiro caractere com o que acabei de criar e alterando o "ab" para "cd", apenas para poder distinguir claramente as linhas de entrada. Fazer isso resultou no seguinte erro:

   Msg 4832, Level 16, State 1, Line XXXXX
   Carga em massa: um final de arquivo inesperado foi encontrado no arquivo de dados.

   Eu esperaria que o arquivo UTF-8 produzisse o mesmo erro com o mesmo caractere.

   E para ser justo, esse cenário também quebraria a abordagem de puxar cada linha inteiramente e usar SUBSTRINGpara dividi-la, se não produzindo um erro, pelo menos corrompendo os dados como SUBSTRINGainda veremos oe ̈ como dois caracteres separados (já que eles são).