FOR XML could not serialize

Question

FOR XML could not serialize

bnordberg

SSCertifiable

Points: 5379
More actions
March 17, 2009 at 8:58 am

#207022

I am working on a query to concatenate 3.5 billion rows of data that contain up to 255 characters (one line). I am attempting to use the FOR XML PATH method and put a line feed in between each row.
So the data look something like
RecordID TIUDocumentReportText
1 Hi I am line 1 of this record, there will be lots of me
1 I am record 2 of the same document
2 Line 1 of record 2
2 Line 2 of record 2
...
There appears to be special characters in the data, so I am doing a cast and replace as:
CONVERT(varchar(max),(SELECT Replace(Replace(TIUDocumentReportText,CAST(0x0020 as varchar(16)), ''),CAST(0x001A as varchar(16)), '') + CHAR(10)
But I keep getting:
Msg 6841, Level 16, State 1, Line 2
FOR XML could not serialize the data for node 'NoName' because it contains a character (0x001A) 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.
I was hoping my REPLACE would take care of the problem, but it is not. Any ideas?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Try using NVarchar instead. I suspect you will get better results.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

bnordberg SSCertifiable Points: 5379 More actions · Answer 2

tried nvarchar already - that did not solve. Thanks though

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Try this instead then. The 0x001a refers to an ASCII code, represented in binary, so - convert it to INT first, then convert it to a character.

CONVERT(varchar(max),(SELECT Replace(Replace(TIUDocumentReportText,char(CAST(0x0020 as int)), ''),char(CAST(0x001A as int)), '') + CHAR(10)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

bnordberg SSCertifiable Points: 5379 More actions · Answer 4

Thanks, I may have figured it out, The binary exist in rows containing spaces (and the binary). So if I add where (PATINDEX('%[A,Z,0-9]%[A,Z,0-9]%[A,Z,0-9]%',TIUDocumentReportText) >0)

I don't get the error, but I think the replace will be the better options.

thanks