sql - sql server中XML解析非法字符

Question

将表记录解析为 xml 时，出现非法 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。

Answer 1

这是因为有一个已知的 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 的对话，并在此链接主题中进行了描述: TSQL "Illegal XML Character" When Converting Varbinary to XML