XML Path expression to include Special Characters

Question

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

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

Answer 1

I tested this with ASCII characters 0-255 and found out that you get this error for characters: 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.

One workaround is to remove , TYPE from your XML statement.

Another way is to remove those characters in the select statement:

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

You could also create a function with these replace statements.

Answer 2

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. Edit: 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;