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

Question

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

"FOR XML无法序列化节点“LastName”的数据，因为它包含一个不允许在XML中的字符（0x001B）。要使用FOR XML检索此数据，请将其转换为二进制、varbinary或图像数据类型，并使用BINARY BASE64指令"

我该如何重写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') 

- Anitha

---

你的@@VERSION是什么？我问这个问题是因为当我尝试使用FOR XML查询来查询一个包含该特定字符的常量字符串时，转义工作得非常好。(使用SP1 - 10.50.2550.0) 另外，这是2008 R2。 - Jaaz Cole

@Jaaz Cole - 我的 MySQL 版本是 Microsoft SQL Server 2008 R2（SP2）- 10.50.4286.0（X64）。我认为名字中可能有一些特殊字符导致出现问题。我正在尝试找出是什么字符。 - Anitha

你确定需要在你的数据中包含这些字符吗？在我的数据集中，我注意到这些奇怪的字符是针对我实际上不想包含的数据（例如垃圾邮件或尝试注入恶意软件等）。 - devinbost

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。

一种解决方法是从XML语句中删除, TYPE。

另一种方法是在选择语句中删除这些字符：

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),'')

你也可以创建一个包含这些替换语句的函数。

- jumxozizi

Answer 2

将TYPE指令移至外部查询。使用它可以绕过SQL Server在常规FOR XML语句中执行的字符转义，但一旦结果被转义（使用不带TYPE的FOR XML），则可以将结果包含在XML TYPE指令语句中。编辑：原始代码块出现问题，无法使用。取而代之，这里提供一个可用的代码块。

DECLARE @kc_consumer table (FirstName VARCHAR(20), LastName VARCHAR(20))
INSERT INTO @kc_consumer VALUES
    ('John','Smith' + NCHAR(27))
  , ('Jane','123ú♂
2⌂¶2<PZdûá╚' + NCHAR(27))
SELECT
    (
        SELECT
              (SELECT A1.FirstName + '' FOR XML PATH('')) FirstName
            , (SELECT A1.LastName  + '' FOR XML PATH('')) LastName
        FROM @kc_consumer AS A1
        FOR XML PATH('Consumer'), TYPE
    )
FOR XML PATH('Element'), ROOT('Elements'), TYPE;

- Jaaz Cole

---

@jaaz-cole，那个小工具不再起作用了。我正在尝试弄清楚是否可以通过在我的一个查询周围包装一个额外的FOR XML PATH查询来使用您的解决方案，因为这个查询出现了问题。 - devinbost

这解决了错误，但又打印出不想要的字符，如 >> 'Deliberate'。我不想在这里使用REPLACE。 - meekash55