XML : How to convert a text with accent to a utf-8 format?

I'm trying to create a script that insert some rows in a table that has an xml saved as IMAGE type. The xml is using utf-8 format.

When I test with some text with accents, I just got the "illegal character" error on XML reading (via XmlDocument in c#).

So I want some way to convert an string with accent to a utf-8 format.

Example: when I save via XmlWriter (in c#) a string that contains "ã", it auto-converts it to "ã" to represent that character. And it works perfectly! But I want to do it in a SQL Script. So let's say the word "São Paulo", it saves "São Paulo". But using only SQL Server (script), I just can't make the xml valid with the word "São Paulo".

Only for more tech details, I'm working with a specific varchar/nvarchar that has this problem, so I just need to convert an text to a "unicode" or "utf-8" text that is valid on xml reading. And using Oracle worked fine, as it saves with accent and reads normally (using BLOB type), but I need the same script on SQL Server too.

I already tried some collates, converts and casts that I've found on the web but nothing worked for me.

Anyone know anything I can do?

Note: a thousand of replaces is not a solution.

No comments:

Post a Comment