Разбор XML недопустимого символа на сервере sql
Меня встречает ошибка illegal xml character при разборе записи таблицы в xml.
SELECT
mb.ProductTitle,mb.ProductDescription,
CAST((
SELECT
Id,
ProductDescription,
FROM ProductsManagement AS mpm
WHERE mpm.MattressId = 6
FOR XML PATH('ProductItemListModel'),
ROOT('MattressBarndProductItemList'))as XML)
FROM Brands AS mb
WHERE mb.Id = 6
FOR XML PATH(''), ROOT('ProductModel')
или
SELECT CONVERT(XML,'lift')
Запись для описания выглядит следующим образом:
Ease™ by Sealy adjustable base is the simple way to turn your bed into the perfect place to relax. The wireless remote controls the head and leg lift, for virtually unlimited range of ergonomic positions."
Вышеприведенное не анализируется в xml.
Все вопросы SO об агрегации строк и разделении строк прямо или косвенно используют методы из Статьи Аарона Бертрана. Использование функции SQLCLR — это самый быстрый метод, который не вызывает проблем с недопустимыми символами.
@PanagiotisKanavos, возможно, OP хочет сгенерировать экспорт XML :)
Ответы 1
Это связано с тем, что существует список известных символов незаконный в стандарте(ах) XML. В основном эти символы даже не видны, например, "терминальный звонок" или CHAR(7). Такой символ и другие из списка вызовут ту ошибку, с которой вы сейчас столкнетесь.
Доступно несколько обходных путей, но все они касаются удаления недопустимых символов.
Следующий пример основан на подходе скалярной функции, поэтому предупреждение: может работать медленно на больших объемах данных:
CREATE FUNCTION [dbo].RemoveInvalidXMLCharacters (@InputString VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
IF @InputString IS NOT NULL
BEGIN
DECLARE @Counter INT, @TestString NVARCHAR(40)
SET @TestString = '%[' + NCHAR(0) + NCHAR(1) + NCHAR(2) + NCHAR(3) + NCHAR(4) + NCHAR(5) + NCHAR(6) + NCHAR(7) + NCHAR(8) + NCHAR(11) + NCHAR(12) + NCHAR(14) + NCHAR(15) + NCHAR(16) + NCHAR(17) + NCHAR(18) + NCHAR(19) + NCHAR(20) + NCHAR(21) + NCHAR(22) + NCHAR(23) + NCHAR(24) + NCHAR(25) + NCHAR(26) + NCHAR(27) + NCHAR(28) + NCHAR(29) + NCHAR(30) + NCHAR(31) + ']%'
SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)
WHILE @Counter <> 0
BEGIN
SELECT @InputString = STUFF(@InputString, @Counter, 1, ' ')
SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)
END
END
RETURN(@InputString)
END
Итак, скорректированный запрос будет похож на:
SELECT
[dbo].RemoveInvalidXMLCharacter(smb.ProductTitle) as ProductTitle
, [dbo].RemoveInvalidXMLCharacter(mb.ProductDescription) as ProductDescription
, CAST((
SELECT
Id,
[dbo].RemoveInvalidXMLCharacter(ProductDescription) ProductDescription
FROM ProductsManagement AS mpm
WHERE mpm.MattressId = 6
FOR XML PATH('ProductItemListModel'), ROOT('MattressBarndProductItemList'))as XML)
FROM Brands AS mb
WHERE mb.Id = 6
FOR XML PATH(''), ROOT('ProductModel')
Другой метод — это разговор с VARBINARY и обратно, описанный также в этой связанной теме: TSQL «Недопустимый символ XML» при преобразовании Varbinary в XML
Вы пытаетесь преобразовать строки агрегат с помощью этого кода. В SQL Server 2017 вы можете использовать STRING_AGG. В более ранних версиях эта техника использовалась для того же. Недопустимые символы XML — известная проблема этого метода.