2

I am dealing with API calls and retrieving xml which is described below.

<?xml version="1.0" encoding="utf-16"?>
<backlinks>
<asd><Click Here for &#x3; tips to help you get around></asd>
<sourcetitle>《新加坡》住宿@戴斯旅店 Days Hotel Singapore-歡遊世界</sourcetitle>
</backlinks>

I am using SQL Server 2008 and I have a xml column in my database table, in which I want to store this value.

I tried with CDATA, it is working fine for &#x3; as well as working fine If I remove encoding from the xml string for whole mentioned xml. But at the time when I try to retrieve an xml then It shows like this.

<backlinks>
<asd>Click Here for &amp;#x3; tips to help you get around</asd>
<sourcetitle>«???»??@???? Days Hotel Singapore-????</sourcetitle>
</backlinks>

I want exactly output which I had entered as an input.

Thank you all in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nikunj Soni
  • 297
  • 2
  • 13

1 Answers1

1

Make sure that you cast column or value to nvarchar. SELECT without N:

SELECT 
'<?xml version="1.0" encoding="utf-16"?>
<backlinks>
<asd><Click Here for &#x3; tips to help you get around></asd>
<sourcetitle>《新加坡》住宿@戴斯旅店 Days Hotel Singapore-歡遊世界</sourcetitle>
</backlinks>'

Returns:

<?xml version="1.0" encoding="utf-16"?>  <backlinks>  <asd><Click Here for &#x3; tips to help you get around></asd>  <sourcetitle>**«???»??@????** Days Hotel Singapore-????</sourcetitle>  </backlinks>

SELECT with N:

SELECT 
N'<?xml version="1.0" encoding="utf-16"?>
<backlinks>
<asd><Click Here for &#x3; tips to help you get around></asd>
<sourcetitle>《新加坡》住宿@戴斯旅店 Days Hotel Singapore-歡遊世界</sourcetitle>
</backlinks>'

Returns:

<?xml version="1.0" encoding="utf-16"?>  <backlinks>  <asd><Click Here for &#x3; tips to help you get around></asd>  <sourcetitle>《新加坡》住宿@戴斯旅店 Days Hotel Singapore-歡遊世界</sourcetitle>  </backlinks>
Westerlund.io
  • 2,743
  • 5
  • 30
  • 37
  • If I have this xml string in a parameter and If I want to pass from .Net to SQL how this 'N' would come into the picture? Because I have to store this xml into database. – Nikunj Soni Dec 26 '15 at 11:03
  • Make sure that the column in your database is of type nvarchar, probably nvarchar(max) would be the best. Then during each stop of the import/export make sure that the data is handled correctly by C# as well (with the correct enconding etc.). UTF-16 and nvarchar column should be enough for the application and the database to handle the characters correctly. – Westerlund.io Dec 26 '15 at 18:55
  • I took the column as nvarchar(max) and also encoding as UTF-16 but the issue is that the Chinese characters (歡遊世界) are stored like this '-????' .So what is the solution for it? Thanks in advance – Nikunj Soni Dec 29 '15 at 04:34