Início / dba / Perguntas / 278684
Accepted
McNets
Asked: 2020-10-27 05:57:03 +0800 CST

Erro ao converter VARCHAR(MAX) para XML devido ao atributo "UTF-8"

  • 772

Eu preciso cavar em uma tabela de logs com um esquema semelhante a este:

CREATE TABLE t (
  id int PRIMARY KEY,
  data varchar(max)
);

A coluna dataarmazena um texto XML recebido de um serviço da web neste formato:

Esta é uma versão reduzida

<?xml version="1.0" encoding="UTF-8"?>
<PARAM>
  <TAB DIM="30" ID="ZC3D2_1" SIZE="5">
    <LIN NUM = "1">
      <FLD NAME = "ZDOC" TYPE = "Char">Ferran López</FLD>
    </LIN>
  </TAB>
</PARAM>

Quando tento converter este texto para XML, recebo o próximo erro:

Análise XML: linha xx, caractere 48, caractere xml ilegal

Isso pode ser resolvido removendo a <xml>tag, ou pelo menos, o encodingatributo.

NOTA: Funciona bem se não houver caracteres especiais como ó, mesmo se eu não remover a <xml>tag.

Pergunta

Existe uma maneira de convertê-lo em XML sem substituir ou remover a <xml>tag?

CAST(REPLACE(data, 'encoding="UTF-8"', '') as XML)

db<>fique aqui

ATUALIZAR

O agrupamento do servidor é: Latin1_General_BIN

Mas mesmo se eu tentar alterar o agrupamento para o agrupamento usual de servidores, ele não funcionará.

SELECT
  id, 
  CAST((data COLLATE Latin1_General_CI_AS) as XML)
FROM
  t;
sql-server sql-server-2014

4 respostas

  1. Seu XML armazenado em uma coluna varchar(max) deve ficar assim.

    <?xml version="1.0" encoding="UTF-8"?>
<PARAM>
  <TAB DIM="30" ID="ZC3D2_1" SIZE="5">
    <LIN NUM = "1">
      <FLD NAME = "ZDOC" TYPE = "Char">Ferran López</FLD>
    </LIN>
  </TAB>
</PARAM>

    O ódeve ser representado com um valor de byte duplo ó.

    Se você não tiver uma string codificada em UTF-8 armazenada em sua coluna, a maneira correta de fazer isso é remover a codificação do XML antes de converter o valor para o tipo de dados XML.

    • 7

  2. Acho que você tem um problema mais profundo. O UTF-8 permite mais caracteres do que os agrupamentos não Unicode regulares no SQL Server. Portanto, para estar seguro, você deve usar o SQL Server 2019, que possui agrupamentos UTF-8 (e eu entendo se isso não for viável / desejável por muitos motivos) ou use (tente) nvarchar em vez de varchar.

    Se você tem medo do aumento de armazenamento indo de varchar para nvarchar, você pode usar a compactação de linha. Mas isso requer Enterprise Edition anterior ao SQL Server 2016.

    • 6
  3. Best Answer

    O que está acontecendo aqui é:

    1. O XMLtipo armazena dados internamente como UTF-16 Little Endian (na maioria das vezes, pelo menos). Não importa qual seja a codificação de origem, o resultado final será UTF-16 LE (e sem <xml>tag, portanto, nãoencoding="..." ).
    2. Ao converter uma string para XML:
      1. São os bytes da string que são convertidos, não os caracteres (explicaremos a diferença em um momento)
      2. NVARCHARos dados são assumidos como UTF-16 LE. Se houver uma <xml>tag e ela contiver o encodingatributo, o único valor válido será"UTF-16" .
      3. VARCHARos dados são considerados na página de código de 8 bits associada ao agrupamento dos dados quando não há <xml>tag, ou se existe, mas não há encodingatributo. Caso contrário, os dados serão interpretados como codificados na página de código especificada no encodingatributo (mesmo que estejam codificados na página de código associada ao agrupamento dos dados).
    3. Seus dados provavelmente estão codificados como página de código 1252 do Windows (isso é determinado pelo agrupamento da coluna em que os dados residem, não pelo agrupamento da instância ou mesmo do banco de dados, mas como você menciona que a instância está usando Latin1_General_BIN, é seguro -suficiente para assumir no momento que a coluna está usando o mesmo agrupamento).
    4. O ponto de código para o ócaractere na página de código Windows-1252 é: 0xF3 .
    5. A <xml>tag, no entanto, está declarando que os dados XML estão codificados como UTF-8.
    6. Em UTF-8, 0xF3 deve ser seguido por três bytes, cada um entre 0x80 e 0xBF , mas em seus dados é seguido por um p, que tem um valor de 0x70 . Portanto, você obtém o erro "caractere xml ilegal" (porque encoding="UTF-8"informa à função de conversão que os bytes são bytes UTF-8 válidos; a conversão não vê o ócaractere).

    Suas opções são:

    1. Idealmente, a coluna seria convertida XML e o encodingatributo da <xml>tag, ou a tag inteira <xml>, seria removida no caminho. E, o tipo de XMLdados pode economizar espaço se houver nomes de elemento e/ou atributo repetidos, pois cria uma dicionário (lista de pesquisa) de nomes internamente e registra a estrutura usando os valores de ID.

    2. Defina a [data]coluna para usar um agrupamento UTF-8 (novo no SQL Server 2019, portanto, não é uma opção para você)

    3. Defina a [data]coluna como eNVARCHAR remova o atributo da tag ou a tag inteira .encoding<xml><xml>

    4. Converta a string de entrada em bytes UTF-8. Portanto, o ócaractere é de dois bytes em UTF-8: 0xC3B3 , que aparece como óno Windows-1252.

      DECLARE @Good VARCHAR(MAX) = '<?xml version="1.0" encoding="UTF-8"?><a>hell'
        + CONVERT(VARCHAR(MAX), 0xC3B3)
        + '</a>';
SELECT @Good, CONVERT(XML, @Good)
-- <?xml version="1.0" encoding="UTF-8"?><a>helló</a>
--
-- <a>helló</a>

    NOTAS:

    • Simplesmente remover o encodingatributo da tag, ou a tag <xml>inteira , não é uma opção. Claro, ele funcionará neste caso específico, mas não funcionará em todos os casos devido à coluna e aos agrupamentos UTF-8 não estarem disponíveis no SQL Server 2014. Portanto, quaisquer caracteres Unicode não disponíveis na página de código 1252 do Windows ser convertido para ou (dependendo do caractere BMP ou do caractere suplementar): <xml>VARCHAR ???
      DECLARE @Test VARCHAR(MAX) = '<test>ó - ☢ - ?</test>';
SELECT @Test, CONVERT(XML, @Test);
-- <test>ó - ? - ??</test>
--
-- <test>ó - ? - ??</test>
    • NÃO _ simplesmente o agrupamento da coluna para uma localidade/cultura diferente. Embora isso possa se livrar do erro, só conseguiria isso eliminando silenciosamente os dados que estavam causando o erro. Por exemplo: 
      DECLARE @Data NVARCHAR(MAX) = N'ó';
SELECT CONVERT(VARCHAR(MAX), @Data COLLATE Latin1_General_BIN) AS [Latin1_General],
    CONVERT(VARCHAR(MAX), @Data COLLATE Latin1_General_BIN) COLLATE
             Cyrillic_General_CI_AS AS [Cyrillic];
/*
Latin1_General    Cyrillic
ó                 o
*/
      "Cirílico" usa uma página de código diferente de "Latin1_General" e o ócaractere não está disponível na página de código cirílico. Mas, há um mapeamento "Best Fit" e é por isso que acabamos com um oem vez de um ?.
    • Você e qualquer pessoa que esteja trabalhando no SQL Server 2008 ou mais recente, realmente deveria estar usando os _100_agrupamentos de nível. Além disso, qualquer pessoa que esteja trabalhando no SQL Server 2012 ou mais recente deve, idealmente, usar a _100_ordenação de nível que termina com _SC(para caracteres suplementares). Finalmente, ao precisar de um agrupamento binário no SQL Server 2005 ou mais recente, use um que termine em _BIN2(veja meu post aqui sobre o motivo).
    • Esse problema não tem nada a ver com o fato de a consulta ser ad hoc ou em um procedimento armazenado (T-SQL ou SQLCLR).
    • 5

  4. use um agrupamento compatível para seu varchar

    CREATE TABLE t (
  id int PRIMARY KEY,
  data varchar(max) COLLATE Latin1_General_100_CI_AI_SC_UTF8
);

INSERT INTO t VALUES
(1, N'<?xml version="1.0" encoding="UTF-8"?>
<PARAM>
  <TAB DIM="30" ID="ZC3D2_1" SIZE="5">
    <LIN NUM = "1">
      <FLD NAME = "ZDOC" TYPE = "Char">Ferran López</FLD>
    </LIN>
  </TAB>
</PARAM>
')
GO

    SELECT
  id, 
  CAST(data as XML)
FROM
  t;
GO

    identificação | (Sem nome de coluna)                                                                                                            
-: | :------------------------------------------------- -------------------------------------------------- ------------------------
 1 | <PARAM><TAB DIM="30" ID="ZC3D2_1" SIZE="5"><LIN NUM="1"><FLD NAME="ZDOC" TYPE="Char">Ferran López</FLD></ LIN></TAB></PARAM>

    db<>fique aqui

    • 4

relate perguntas