TSQL «Недопустимый XML-символ» при преобразовании Varbinary в XML
Я пытаюсь создать хранимую процедуру в SQL Server 2016, которая преобразует XML, который ранее был преобразован в Varbinary, обратно в XML, но при преобразовании выдает ошибку «Недопустимый символ XML». Я нашел обходной путь, который, кажется, работает, но я не могу понять, что Почему работает, что меня не устраивает.
Хранимая процедура принимает данные, которые были преобразованы в двоичные в SSIS и вставлены в столбец varbinary(MAX) в таблице, и выполняет простой
CAST(Column AS XML)
Долгое время он работал нормально, и я начал замечать проблему только тогда, когда исходный XML начинал содержать символ ® (зарегистрированная торговая марка).
Теперь, когда я пытаюсь преобразовать двоичный файл в XML, я получаю эту ошибку
Msg 9420, Level 16, State 1, Line 23
XML parsing: line 1, character 7, illegal xml character
тем не мение, если я сначала конвертирую двоичный файл в varchar(MAX), а затем конвертирую его в XML, похоже, он работает нормально. Я не понимаю, что происходит, когда я выполняю этот промежуточный CAST, который отличается от прямого преобразования в XML. Меня больше всего беспокоит то, что я не хочу добавлять его, чтобы учесть этот сценарий, и это приведет к непредвиденным последствиям.
Код теста:
DECLARE @foo VARBINARY(MAX)
DECLARE @bar VARCHAR(MAX)
DECLARE @Nbar NVARCHAR(MAX)
--SELECT Varbinary
SET @foo = CAST( '<Test>®</Test>' AS VARBINARY(MAX))
SELECT @foo AsBinary
--select as binary as varchar
SET @bar = CAST(@foo AS VARCHAR(MAX))
SELECT @bar BinaryAsVarchar -- Correct string output
--select binary as nvarchar
SET @nbar = CAST(@foo AS NVARCHAR(MAX))
SELECT @nbar BinaryAsNvarchar -- Chinese characters
--select binary as XML
SELECT TRY_CAST(@foo AS XML) BinaryAsXML -- ILLEGAL XML character
-- SELECT CONVERT(xml, @obfoo) BinaryAsXML --ILLEGAL XML Character
--select BinaryAsVarcharAsXML
SELECT TRY_CAST(@bar AS XML) BinaryAsVarcharAsXML -- Correct Output
--select BinaryAsNVarcharAsXML
SELECT TRY_CAST(@nbar AS XML) BinaryAsNvarcharAsXML -- Chinese Characters
Почему вы вообще делаете любой этих преобразований? Какую бы проблему вы ни хотели решить, смешение типов не поможет. Если у вас есть ошибки кодирования, убедитесь, что вы всегда используете поля nvarchar, строковые литералы параметров а также.
Кстати, это означает, что преобразования, которые сработали, на самом деле неверны - они зависят от использования одной и той же кодировки как при преобразовании в varbinary, так и обратно в текст
Наконец, почему пакет SSIS сохранил текст в столбце варбинарный? Это просто просит для проблем с конвертацией. Я подозреваю, что первоначальный автор использовал varchar, столкнулся с проблемами преобразования и вместо того, чтобы использовать правильные сопоставления столбцов или переключиться на nvarchar, просто прикрыл проблему с помощью varbinary. Это ничего не исправило, просто перенесло проблемы с кодировкой в ридер. Это работало до тех пор, пока не было нелатинских символов (что также не вызвало бы проблем с varchar). Когда был добавлен первый нелатинский символ, бум, считыватель не смог
Встроенные преобразования из вопроса: CAST(CAST( '<Test>®</Test>' AS VARBINARY(MAX)) AS NVARCHAR(MAX)) Э, не делайте этого. Основное правило кодировки символов - читать с той кодировкой, которая использовалась для записи.
@TomBlodget Хотя ваше утверждение совершенно верно, я предполагаю, что OP должен иметь дело с мусором, созданным кем-то другим. SQL-Server не очень силен в работе с кодировками. Может быть, в этом месте невозможно читать с кодировкой, которая использовалась для записи ...
@Shnugo Как только он выяснил, как данные были искажены, возможно, его можно будет отменить, используя SQL CLR, если это необходимо.
@TomBlodget Вот что я имею в виду ... Если бы мои проекты не могли использовать функции CLR ... Иногда нам приходится иметь дело с мусором. Я думаю, что многократное приведение от varbin к varchar, затем к nvarchar и, наконец, к xml может решить эту проблему, как показано в моем ответе ...
Я думаю, что первоначальный автор использовал varbinary как способ обойти ограничение строковых символов в SSIS. Прочитав все комментарии, я попытался быстро переработать таблицу package + SQL, чтобы использовать строку Unicode + nvarchar, и столкнулся с этой ошибкой Description: "Unicode data is odd byte size for column 11. Should be even byte size.". Из того, что я прочитал, вы можете решить эту проблему, явно указав четную длину строки, но тогда SSIS ограничивает вас до 4000 символов.
Я думаю, что моим решением будет загрузка исходного XML непосредственно в столбец SQL XML с использованием OPENROWSET и массового импорта SQL в задаче сценария.
Ответы 1
Необходимо знать несколько вещей:
- SQL-Server довольно ограничен кодировками символов. Есть
VARCHAR, это 1-байтовый расширенный ASCII, иNVARCHAR, этоUCS-2(почти то же самое, что иutf-16). VARCHARиспользует простой латынь для первого набора символов и отображение кодовых страниц, обеспечиваемое сопоставлением, используемым для второго набора.VARCHARэто не utf-8.utf-8работает сVARCHAR, если все символы закодированы в 1 байт. Ноutf-8знает много 2-байтовых (до 4-байтовых) символов, которые нарушили бы внутреннюю память строкиVARCHAR.NVARCHARбудет работать практически с любым 2-байтовым закодированным символом изначально (то есть почти с любым существующим символом). Но это не совсемutf-16(есть 3-байтовые символы, которые могут нарушить внутреннюю память SQL-серверов).- XML хранится не в виде XML-строки, которую вы видите, а в виде иерархически организованной физической таблицы, основанной на значениях
NVARCHAR. - Собственно хранимый XML работает очень быстро, в то время как любое текстовое хранилище потребует очень дорогостоящей операции синтаксического анализа заранее (снова и снова ...).
- Хранить XML как строку - плохо, хранить XML как строку
VARCHAR- еще хуже. - Хранение
VARCHAR-string-XML какVARBINARY- это совокупность вещей, которые вам не следует делать.
Попробуй это:
DECLARE @text1Byte VARCHAR(100)='<test>blah</test>';
DECLARE @text2Byte NVARCHAR(100)=N'<test>blah</test>';
SELECT CAST(@text1Byte AS VARBINARY(MAX)) AS text1Byte_Binary
,CAST(@text2Byte AS VARBINARY(MAX)) AS text2Byte_Binary
,CAST(@text1Byte AS XML) AS text1Byte_XML
,CAST(@text2Byte AS XML) AS text2Byte_XML
,CAST(CAST(@text1Byte AS VARBINARY(MAX)) AS XML) AS text1Byte_XML_via_Binary
,CAST(CAST(@text2Byte AS VARBINARY(MAX)) AS XML) AS text2Byte_XML_via_Binary
Единственное отличие, которое вы увидите, - это множество нулей в 0x3C0074006500730074003E0062006C00610068003C002F0074006500730074003E00. Это связано с 2-байтовое кодированиеnvarchar, каждый второй байт в этом примере не нужен. Но если бы вам понадобились персонажи с Дальнего Востока, картина была бы совсем другой.
Причина, по которой это работает: SQL-сервер очень умен. Преобразование переменной в XML довольно просто, поскольку движок знает, что базовая переменная - это varchar или nvarchar. Но последние два слепка разные. Движок должен проверить двоичный файл, является ли он действительным nvarchar, и в случае неудачи даст ему вторую попытку с varchar.
Теперь попробуйте добавить свой зарегистрированная торговая марка к данному примеру. Сначала добавьте его ко второй переменной DECLARE @text2Byte NVARCHAR(100)=N'<test>blah®</test>'; и попробуйте запустить это. Затем добавьте его к первой переменной и попробуйте снова.
То, что вы можете попробовать:
Загрузите свой двоичный файл в varchar(max), затем в nvarchar(max) и, наконец, в xml.
,CAST(CAST(CAST(CAST(@text1Byte AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS NVARCHAR(MAX)) AS XML) AS text1Byte_XML_via_Binary
Это сработает, но не быстро ...
varcharозначает ASCII или, по крайней мере, однобайтовый текст. ® находится за пределами диапазона 0–127, на который не влияют кодовые страницы. Попробуйте с nvarchar иCAST( N'<Test>®</Test>' as varbinary(max)).nvarcharозначает UTF16, т.е. два байта, поэтому преобразование из varchar в varbinary в nvarchar не удалось.