SQL Server: parsing copyright symbol from xml

0
votes

I have xml files with © symbols. But SQL can't parse such XMLs, is there a workaround for this?

This query:

declare @XmlResponse as xml; 
select @XmlResponse = '<?xml version="1.0" encoding="utf-8"?><Response>©</Response>'
select @XmlResponse as myxml

Returns an error:

Msg 9420, Level 16, State 1, Line 15
XML parsing: line 1, character 49, illegal xml character

3
sql-serverxmltsql

3 Answers

2
votes

You can replace the © symbol with the XML entity &#169;. 

declare @XmlResponse as xml; 
select @XmlResponse = REPLACE('<?xml version="1.0" encoding="utf-8"?><Response>©</Response>', '©', '&#169;')
select @XmlResponse as myxml

For more XML entities please see: http://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references

1
votes

It works if you get rid of the encoding. Here is an article I found about XML encoding. I tried using the encoding that it suggested and I got a different error. However if I remove the encoding completely it works fine.

declare @XmlResponse as xml; 
select @XmlResponse = '<?xml version="1.0" ?><Response>©</Response>'
select @XmlResponse as myxml
1
votes

Problem is with encoding attribute. It will work if you remove it or change it to utf-16. Here are some details. Following with utf-16 works here

declare @xml nvarchar(max) = 
'<?xml version="1.0" encoding="utf-16" ?><Response>©</Response>'
select convert(xml, @xml) myxml