CODEWITHSUNDEEP
c#sql-serverxmlencoding
nanonerd
nanonerd

Reputation: 1984

Encoding xml and saving to SQL Server

Have a Web application that let's you paste text and save it to database.

Text had € (euro currency) symbol and once it got to the database, it crashed while parsing the XML in SQL. Reason is invalid character.

I solved this by using C# to replace € with € in the Web API. Now XML contains € and it gets parsed into SQL. I look at the saved row in the database and it shows €. Great!

I go back to the Web application and pull up the text that was saved. I expect to see "€52.9mm" but instead, I see "52.9mm".

I hit F12, refresh and see that it does indeed retrieve "€52.9mm" which is what's stored in the database. But the UI shows "52.9mm".

For kicks, I change the value in the database to € 52.9mm. A refresh of the UI now shows "€52.9mm".

Would a practical solution be to save the text in the database as € 52.9mm?

If this works, how do I get it to save in this format which is how it is in the XML? But after it gets parsed by the SQL, it gets saved as €52.9mm.

Is there a better solution?

Upvotes: 1

Views: 626

Answers (1)

Nico
Nico

Reputation: 12683

Best way would be to use CDATA wrapping your HTML inside your XML tag element. Then you dont need to worry about escaping or converting non-xml safe characters.

Now you haven't shown any detail on constructing this xml file but lets take a stadard XElement. To save the content as CDATA you simply write..

xDocument.Add(new XElement("MyHtml", new XCData("<p>My HTML Content</p>"));

CDATA

CDATA sections may occur anywhere character data may occur; they are used to escape blocks of text containing characters which would otherwise be recognized as markup. CDATA sections begin with the string <![CDATA[ and end with the string ]]>

http://www.w3.org/TR/REC-xml/#sec-cdata-sect

Upvotes: 3

Related Questions