Недопустимый символ XML TSQL при преобразовании Varbinary в XML

Я пытаюсь создать хранимую процедуру в SQL Server 2016, которая преобразует XML, который ранее был преобразован в Varbinary, обратно в XML, но при преобразовании получаю ошибку «Недопустимый символ XML». Я нашел обходной путь, который, кажется, работает, но я не могу понять, почему он работает, и мне неудобно.

Хранимая процедура берет данные, которые были преобразованы в двоичные данные в службах SSIS и вставляются в столбец varbinary(MAX) в таблице, и выполняет простую операцию.

CAST(Column AS XML)

Он работал нормально в течение долгого времени, и я начал замечать проблему только тогда, когда первоначальный XML начал содержать символ ® (зарегистрированный товарный знак).

Теперь, когда я пытаюсь преобразовать двоичный файл в XML, я получаю эту ошибку

Сообщение 9420, уровень 16, состояние 1, строка 23
Анализ XML: строка 1, символ 7, недопустимый символ xml

Однако если я сначала преобразую двоичный файл в 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

xml casting sql-server tsql varbinary
person Evan Prickett    schedule 02.11.2018    source источник
comment
varchar означает ASCII или, по крайней мере, текст с однобайтовой кодировкой. ® находится за пределами диапазона 0–127, на который не влияют кодовые страницы. Попробуйте с nvarchar и CAST( N'<Test>®</Test>' as varbinary(max)). nvarchar означает UTF16, т.е. два байта, поэтому преобразование из varchar в varbinary в nvarchar не удалось.   -  person Panagiotis Kanavos    schedule 02.11.2018
comment
Почему вы все равно делаете любые из этих преобразований? Какую бы проблему вы ни хотели решить, смешение типов не поможет. Если у вас есть ошибки кодирования, убедитесь, что вы всегда используете nvarchar поля, параметры и строковые литералы.   -  person Panagiotis Kanavos    schedule 02.11.2018
comment
Кстати, это означает, что сработавшие преобразования на самом деле неверны - они зависят от использования одной и той же кодировки как при преобразовании в varbinary, так и обратно в текст.   -  person Panagiotis Kanavos    schedule 02.11.2018
comment
Наконец, почему пакет SSIS сохранил text в столбце varbinary? Это просто напрашивается на проблемы с преобразованием. Я подозреваю, что первоначальный автор использовал varchar, столкнулся с проблемами преобразования и вместо того, чтобы использовать правильные сопоставления столбцов или переключиться на nvarchar, просто скрыл проблему, используя varbinary. Это ничего не исправило, просто перенесло проблемы с кодировкой в ​​ридер. Это работало, пока не было нелатинских символов (что также не вызвало бы проблем с varchar). Когда был добавлен первый нелатинский символ, бум, программа чтения не удалась.   -  person Panagiotis Kanavos    schedule 02.11.2018
comment
Встроенные преобразования из вопроса: CAST(CAST( '<Test>®</Test>' AS VARBINARY(MAX)) AS NVARCHAR(MAX)) Не делайте этого. Фундаментальное правило кодировок символов — читать с той кодировкой, которая использовалась для записи.   -  person Tom Blodget    schedule 03.11.2018
comment
@TomBlodget Хотя ваше утверждение совершенно верно, я предполагаю, что ОП имеет дело с мусором, созданным кем-то другим. SQL-сервер не очень силен в работе с кодировками. Возможно, что чтение с той кодировкой, которая использовалась для записи, в этом месте невозможна...   -  person Shnugo    schedule 04.11.2018
comment
@Shnugo Как только он выяснит, как данные были искажены, возможно, их можно будет отменить, при необходимости используя SQL CLR.   -  person Tom Blodget    schedule 04.11.2018
comment
@TomBlodget Вот что я имею в виду ... Во многих моих проектах нельзя было бы использовать функции CLR ... Иногда нам приходится заниматься мусором. Я думаю, что многоадресная передача из varbin в varchar, затем в nvarchar и, наконец, в xml может решить эту проблему, как показано в моем ответе...   -  person Shnugo    schedule 04.11.2018
comment
Я думаю, что первоначальный автор использовал varbinary как способ обойти ограничение строковых символов в SSIS. Прочитав все комментарии, я попытался быстро переработать пакет + таблицу SQL для использования строки юникода + nvarchar и столкнулся с этой ошибкой Description: "Unicode data is odd byte size for column 11. Should be even byte size.". Из того, что я прочитал, вы можете решить эту проблему, явно указав четную длину строки, но тогда SSIS ограничивает вас до 4000 символов.   -  person Evan Prickett    schedule 13.11.2018
comment
Я думаю, что моим решением будет загрузка исходного XML непосредственно в столбец SQL XML с использованием OPENROWSET и массового импорта SQL в задаче сценария.   -  person Evan Prickett    schedule 13.11.2018

Ответы (1)


arrow_upward
0
arrow_downward

Есть несколько вещей, которые нужно знать:

  • SQL-сервер довольно ограничен кодировками символов. Есть 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

Это сработает, но не быстро...

person Shnugo    schedule 03.11.2018