CODEWITHSUNDEEP
sqlxmlsql-server-2008
Nikshep
Nikshep

Reputation: 2115

Storing Symbols like ϱπΩ÷√νƞµΔϒᵨλθ→%° in SQL Server XML

I ran these quires in my SQL server 

select cast('<Answers>
  <AnswerDescription> ϱπΩ÷√νƞµΔϒᵨλθ→%° </AnswerDescription>
</Answers>' as xml)


select ' ϱπΩ÷√νƞµΔϒᵨλθ→%°'

And got the following results 

   <Answers>
      <AnswerDescription> ?pO÷v??µ??????%° </AnswerDescription>
    </Answers>

and " ?pO÷v??µ??????%°"

How to make my SQL server store or display these values as they are being sent from Application ?

Upvotes: 0

Views: 2572

Answers (2)

Ed Harper
Ed Harper

Reputation: 21495

In SQL Server, scalar string values are cast to VARCHAR by default.

Your example can be made to work by indicating that the strings should be treated as NVARCHAR by adding N before the opening single quote:

select cast(N'<Answers>
  <AnswerDescription> ϱπΩ÷√νƞµΔϒᵨλθ→%° </AnswerDescription>
</Answers>' as xml)

select N' ϱπΩ÷√νƞµΔϒᵨλθ→%°'

If these strings are being incorrectly stored in the database, it is likely that they are being implicitly cast to VARCHAR at some point during insertion (e.g. INSERT). It's also possible that they are being stored correctly and are cast to VARCHAR on retrieval (e.g. SELECT).

If you add some code to the question showing how you're inserting data and the datatypes of the target tables, it should be possible to provide more detailed assistance.

Upvotes: 4

Milan Halada
Milan Halada

Reputation: 1934

I believe its problem with incorectly set character set, change charecter set to UTF8. I just tested it on my MySQL database, i changed character set to utf8-bin using 

ALTER TABLE  `tab1` CHANGE  `test`  `test` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

worked without any problem

Upvotes: 1

Related Questions