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]
-- 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#...