sql - 包含特殊字符的 XML 路径表达式

Question

我正在尝试此 SQL 使用 XML 路径表达式从 SQL Server 2008 表中获取名字和姓氏。数据包含特殊字符。当我尝试 sql 时，出现以下错误:

FOR XML could not serialize the data for node 'LastName' because it contains a character (0x001B) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive

我如何重写 SQL 以在 xml 中包含这些字符(可能是 CDATA？)

SELECT  (
    SELECT  A1.FirstName
        ,   A1.LastName
    FROM    dbo.kc_consumer AS A1
    FOR  XML PATH('Consumer') , TYPE) 
     AS ConsumerData
FOR XML PATH('Element'), ROOT('Elements') 

Answer 1

我用 ASCII 字符 0-255 对此进行了测试，发现字符出现此错误:0x0000, 0x0001, 0x0002, 0x0003, 0x0004, 0x0005, 0x0006, 0x0007, 0x0008, 0x000B, 0x000C, 0x000E, 0x000F, 0x0010, 0x0011, 0x0012, 0x0013, 0x0014, 0x0015, 0x0016, 0x0017, 0x0018, 0x0019, 0x001A, 0x001B, 0x001C, 0x001D, 0x001E, 0x001F .

一种解决方法是删除 , TYPE来自您的 XML 语句。

另一种方法是删除 select 语句中的那些字符:

REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
REPLACE( REPLACE( REPLACE( REPLACE( 
    < YOUR EXPRESSION TO BE CLEANED >
,char(0x0000),'') ,char(0x0001),'') ,char(0x0002),'') ,char(0x0003),'') ,char(0x0004),'') 
,char(0x0005),'') ,char(0x0006),'') ,char(0x0007),'') ,char(0x0008),'') ,char(0x000B),'') 
,char(0x000C),'') ,char(0x000E),'') ,char(0x000F),'') ,char(0x0010),'') ,char(0x0011),'') 
,char(0x0012),'') ,char(0x0013),'') ,char(0x0014),'') ,char(0x0015),'') ,char(0x0016),'') 
,char(0x0017),'') ,char(0x0018),'') ,char(0x0019),'') ,char(0x001A),'') ,char(0x001B),'') 
,char(0x001C),'') ,char(0x001D),'') ,char(0x001E),'') ,char(0x001F),'')

您还可以使用这些替换语句创建一个函数。