Msg 9420 , XML parsing , illegal xml xharacter

Question

I want to convert the binary value to XML and I have executed the below code

SELECT *, CAST (CAST (CAST (columnName AS varbinary(max)) AS varchar(max)) AS xml)  
  FROM tableName  
  WHERE primaryKey = '13877'  

I got the below error

Msg 9420, Level 16, State 1, Line 1
XML parsing: line 28, character 29, illegal xml character

Answer 1

As the error message says, the source data isn't convertable to valid XML.

For conversion you use varchar = ASCII; you better should use nvarchar for Unicode.

Answer 2

Hi @kandege CR
You can try cast your binary to varchar(max), then to nvarchar(max) and finally to xml.
Like this:

CAST(CAST(CAST(CAST(columnName AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS NVARCHAR(MAX)) AS XML)  

Refer to this similar thread: TSQL "Illegal XML Character" When Converting Varbinary to XML

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.