raven daniel

raven daniel

  • 1.6k
  • 123
  • 42.5k

Replacing Characters that break Xml? anyhelp would be welcomed

Jul 20 2012 5:11 AM
Hi guys and thank you for the help

Im still at entry level, and need some help on something.
So im processing alot of stuff and im getting alot errors:

"FOR XML could not serialize the data for node 'text' because it contains a character (0x0012) 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 have a insert proc that inserts body text into a table and when i pull data that includes the text from this tblDescriptionText when i generate my xml, it breaks giving error above.

so i can replace this easy using
Replace(ColumnName, '0012' , '') but i dont know what character 0x0012 looks like or character 0x0016 and etc.

I want to change my insert Proc : To replace other funny characters but im looking for maybe a list of funny characters that xml doesnt accept, so i can include it into my proc, because im already eliminating double spaces here.

[code]
CREATE proc [dbo].[PRocNameInsert]     
@TransactionID int, 
@text varchar(800), 
@Subject varchar(26) 
AS     
set @text = REPLACE(REPLACE(REPLACE
(LTRIM(RTRIM(@text)),'  ',' '+CHAR(7)),CHAR(7)+' ',''),CHAR(7),'')

set @Subject = REPLACE(REPLACE(REPLACE
(LTRIM(RTRIM(@Subject)),'  ',' '+CHAR(7)),CHAR(7)+' ',''),CHAR(7),'') 
 
Insert into dbo.description     
(     
transactionID,     
[text],     
[subject]     
)     
Values     
 (     
 @TransactionID ,     
 CASE WHEN LTRIM(RTRIM(@text)) = '' then null else LTRIM(RTRIM(@text)) end,     
 CASE WHEN LTRIM(RTRIM(@Subject)) = '' then null else LTRIM(RTRIM(@Subject)) end   
 )
[/code]

Or would you suggest instead of remove the characters i should encode them which to my understanding is this by what encoding special characters for xml, correct me if im wrong.

Replace(NAME, '<', '&lt;')

But i would still need some sort of list of characters that break xml.

Any suggestions, list, or what my options are solution would be big help, thank you.

p.s if your looking for how i generate my xml:

[code]
DECLARE @XmlOutput xml           
SET @XmlOutput =(           
select (           
select collectionDetails.suite '@suite',        
(           
 select         
    transactions.transactionID '@transactionID',                       
    case when transactions.sender = '' then null else transactions.sender end as sender,           
    case when transactions.senderUser = '' then null else transactions.senderUser end as senderUser,           
    case when transactions.recipient = '' then null else transactions.recipient end as recipient,           
    case when transactions.recipientUser = '' then null else transactions.recipientUser end as recipientUser,                    
    convert(varchar, transactions.transDate,112) as date,                    
    transactions.version,   
    (Select    
  (Select    
    Case when description.subject = '' then null else description.subject end as subject,   
    Case when description.text = '' then null else description.text end as text   
  From description description    
  where description.transactionID = transactions.transactionID   
  for xml path('description'),Type)   
 )   
           
 from transactions transactions           
where transactions.transactionID=@TransctionId           
 for xml  path('transaction'),type)           
 from collectionDetails collectionDetails                        
 for xml  path('collectionDetails'),type)           
 for xml  path('collection'))           
            
  SELECT @XmlOutput as 'RecalXML'
[/code]