Msg 9420 , XML parsing , illegal xml xharacter

kandege CR 1 Reputation point
2022-10-04T12:40:58.953+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,878 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Olaf Helper 41,101 Reputation points
    2022-10-04T12:49:30.383+00:00

    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.

    0 comments No comments

  2. CosmogHong-MSFT 23,566 Reputation points Microsoft Vendor
    2022-10-05T08:38:10.543+00:00

    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.

    0 comments No comments