这是因为
illegal
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的对话,并在本链接主题中进行了反向描述:
Invalid Characters in XML