Bienvenido a proyecto online, en este lugar encontrarás la respuesta que buscas.
Solución:
Su XML almacenado en una columna varchar (max) debería verse así.
Ferran López
los ó
debe representarse con un valor de doble byte ó
.
Si no tiene una codificación UTF-8 string almacenado en su columna, la forma correcta de hacerlo es eliminar la codificación del XML antes de convertir el valor al tipo de datos XML.
Creo que tienes un problema más profundo. UTF-8 permite más caracteres que las intercalaciones normales que no son Unicode en el servidor SQL. Por lo tanto, para estar seguro, debe usar SQL Server 2019 que tiene intercalaciones UTF-8 (y entiendo si eso no es factible / deseable por muchas razones) o use (pruebe) nvarchar en lugar de varchar.
Si tiene miedo de que el almacenamiento aumente de varchar a nvarchar, posiblemente pueda usar la compresión de filas. Pero eso requiere Enterprise Edition antes de SQL Server 2016.
Lo que está sucediendo aquí es:
- los
XML
type almacena datos internamente como UTF-16 Little Endian (la mayoría de las veces, al menos). No importa cuál sea la codificación de origen, el resultado final será UTF-16 LE (y no
etiqueta, por lo tanto, noencoding="..."
). - Al convertir un string para
XML
:- Es el bytes de El string que se convierten, no los personajes (explicarán la diferencia en un momento)
NVARCHAR
se supone que los datos son UTF-16 LE. Si hay un
etiqueta y contiene elencoding
attribute, el único valor válido es"UTF-16"
.VARCHAR
Se supone que los datos están en la página de códigos de 8 bits asociada con la clasificación de los datos cuando no hay
etiqueta, o si existe una pero no hayencoding
attribute. De lo contrario, los datos serán interpretado como codificado en la página de códigos especificada en elencoding
attribute (a pesar de que es codificado en la página de códigos asociada con la intercalación de los datos).
- Es muy probable que sus datos estén codificados como la página de códigos de Windows 1252 (esto está determinado por la intercalación de la columna en la que residen los datos, no la intercalación de la instancia o incluso la base de datos, pero dado que menciona que la instancia está usando
Latin1_General_BIN
, es lo suficientemente seguro asumir por el momento que la columna está usando la misma colación). - El punto de código para el
ó
El carácter en la página de códigos Windows-1252 es: 0xF3. - los
Sin embargo, la etiqueta declara que los datos XML están codificados como UTF-8. - En UTF-8, 0xF3debe ir seguido de tres bytes, cada uno entre 0x80 y 0xBF, sin embargo, en sus datos va seguido de un
p
, que tiene un valor de 0x70. Por lo tanto, obtiene el error “carácter xml ilegal” (porque elencoding="UTF-8"
le dice a la función de conversión que el bytes son bytes UTF-8 válidos; la conversión no ve eló
personaje).
Tus opciones son:
-
Idealmente, la columna se convertiría a
XML
y losencoding
attribute de El
etiqueta, o toda la
etiqueta en sí, se eliminará en el camino de entrada. Y, laXML
tipo de datos pueden Ahorre espacio si hay elementos repetidos y / o attribute nombres, ya que crea un diccionario (lista de búsqueda) de nombres internamente y registra la estructura utilizando los valores de ID. -
Selecciona el
[data]
columna para usar una intercalación UTF-8 (nuevo en SQL Server 2019, por lo que no es una opción para usted) -
Selecciona el
[data]
columna para serNVARCHAR
, y eliminar elencoding
attribute de El
etiqueta, o toda la
etiqueta. -
Convierte lo entrante string en bytes UTF-8. Entonces el
ó
El carácter es de dos bytes en UTF-8: 0xC3B3, que aparecen comoó
en Windows-1252.DECLARE @Good VARCHAR(MAX) = 'hell' + CONVERT(VARCHAR(MAX), 0xC3B3) + ''; SELECT @Good, CONVERT(XML, @Good) -- helló -- -- helló
NOTAS:
- Simplemente quitando el
encoding
attribute de El
etiqueta, o toda la
etiqueta, es no una opción. Seguro, funcionará en este caso particular, pero no funcionará en todos los casos debido a que la columna estáVARCHAR
y Las intercalaciones UTF-8 no están disponibles en SQL Server 2014. Por lo tanto, cualquier carácter Unicode que no esté disponible en la página de códigos de Windows 1252 se convertirá a?
o??
(según el carácter BMP o el carácter complementario):DECLARE @Test VARCHAR(MAX) = '
ó - ☢ - '; SELECT @Test, CONVERT(XML, @Test); --ó - ? - ?? -- --ó - ? - ?? - Hacer NO simplemente cambie la intercalación de la columna a una ubicación / cultura diferente. Si bien eso podría eliminar el error, solo lo lograría eliminando silenciosamente los datos que estaban causando el error. Por ejemplo:
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” utiliza una página de códigos diferente a “Latin1_General”, y el
ó
El carácter no está disponible en la página de códigos cirílicos. Sin embargo, existe un mapeo de “Mejor ajuste”, por lo que terminamos con uno
en lugar de un?
. - Usted, y cualquier persona que trabaje en SQL Server 2008 o más reciente, realmente debería utilizar el
_100_
intercalaciones de nivel. Además, cualquier persona que trabaje en SQL Server 2012 o más reciente debería idealmente utilizar el_100_
colación de nivel que termina con_SC
(para caracteres suplementarios). Finalmente, cuando necesite una intercalación binaria en SQL Server 2005 o más reciente, use una que termine en_BIN2
(vea mi publicación aquí para saber por qué). - Este problema no tiene nada que ver con si la consulta es ad hoc o en un procedimiento almacenado (T-SQL o SQLCLR).
Calificaciones y reseñas
Recuerda dar visibilidad a esta noticia si te fue útil.