Read Unicode XML into nvarchar

Transact-SQL
1

I have XML in other languages that I am passing down from C# to be stored in the SQL Server 2008 DB.

The XML is correctly identified as such and passed into the proc as a XML variable

<?xml version="1.0" encoding="utf-16"?>

My column is declared as a nvarchar but when I use this SQL to get the values the characters for title / AgencyName are replaced with '?'

Here is my SQL

CREATE procedure [dbo].[updTransitAgencies]
@ProviderID int,
@DefaultInstance varchar(25),
@AgencyXML xml
as

-- Grab the data from the XML list
select @ProviderID as ProviderID,
	   a.t.value('@tag', 'nvarchar(100)') as AgencyID,
	   a.t.value('@title', 'nvarchar(500)') as AgencyName, 
	   a.t.value('@regionTitle', 'nvarchar(500)') as AgencyRegionTitle,		   
	   a.t.value('@shortTitle', 'nvarchar(100)') as AgencyShortTitle,
	   a.t.value('@timeZone', 'varchar(100)') as AgencyTimeZone,
	   a.t.value('@url', 'varchar(200)') as AgencyURL,
	   a.t.value('@lang', 'varchar(10)') as AgencyLang,
	   a.t.value('@phone', 'varchar(15)') as AgencyPhone,
	   a.t.value('@fareURL', 'varchar(200)') as AgencyFareURL,
	   a.t.value('@email', 'varchar(200)') as AgencyEmail,
       @DefaultInstance as DefaultInstance
into #TempTable
from @AgencyXML.nodes('/body/agency') a(t)

I have seen plenty of sample where the XML in set in the SQL but here I am passing it in through a proc variable from C#...

2

what is your server, database and table collation set to? collation exists on

server --> database --> table

level

3

Please re-read post. Info is listed already

4

ok re-read your post, still same question

what is your server, database and table collation set to? nvarchar is not collation

5

Solved. The script to update the columns from varchar to nvarchar for the following fields failed because one of the columns was a primary key value.

a.t.value('@tag', 'nvarchar(100)') as AgencyID,
a.t.value('@title', 'nvarchar(500)') as AgencyName,
a.t.value('@regionTitle', 'nvarchar(500)') as AgencyRegionTitle,
a.t.value('@shortTitle', 'nvarchar(100)') as AgencyShortTitle,

Thanks...