包含特殊字符的 XML 路径表达式 [英] XML Path expression to include Special Characters

问题描述

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

I am trying this SQL to get the firstname and lastname from SQL Server 2008 tables using XML Path expression. The data contains special characters. When I try the sql, I get an error the following error:

FOR XML 无法序列化节点LastName"的数据，因为它包含 XML 中不允许的字符 (0x001B).要使用 FOR XML 检索此数据，请将其转换为 binary、varbinary 或 image 数据类型并使用 BINARY BASE64 指令

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?)

How can I rewrite the SQL to include these characters in the xml ( maybe as 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') 

推荐答案

将 TYPE 指令拉入外部查询.使用它可以绕过 SQL Server 在普通 FOR XML 语句中进行的字符转义，但是一旦您的结果被转义(使用没有 TYPE 的 FOR XML)，您的结果就可以包含在 XML TYPE 指令语句中.原来的小提琴不知何故已经死了.它不稳定.相反，这里有一段有效的代码.

Pull the TYPE directive into the outer query. Using it bypasses the character escaping that SQL Server does in a normal FOR XML statement, but once your results are escaped (using FOR XML without TYPE), your results can be included in an XML TYPE directive statement. The original fiddle has died somehow. It's unstable. Instead, here's a block of code that works.

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;

这篇关于包含特殊字符的 XML 路径表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！