Sql-server – Querying XML with Unicode data

sql serversql server 2014unicodexml

I'm trying to select attribute value from XML column using XPath with a parameter that can be a Unicode string. Could you please tell me how to make these two examples work?

create table #test
(
    Document xml
);

insert into #test (Document) values
('<FormDocument xmlns="http://mynamespace.org/Schemas">
  <Fields>
    <Field Id="a" Value="qwerty" />
    <Field Id="ą" Value="123" />
  </Fields>
</FormDocument>')

-- works
declare @p1 int
set @p1=-1
exec sp_prepexec @p1 output,N'@p0 nvarchar(4000)',
N'select t.Document.value(''declare default element namespace "http://mynamespace.org/Schemas"; /FormDocument[1]/Fields[1]/Field[@Id = sql:variable("@p0")][1]/@Value'', ''nvarchar(255)'') as Col1
from #test t',@p0=N'a'
go

-- doesn't work
declare @p1 int
set @p1=-1
exec sp_prepexec @p1 output,N'@p0 nvarchar(4000)',
N'select t.Document.value(''declare default element namespace "http://mynamespace.org/Schemas"; /FormDocument[1]/Fields[1]/Field[@Id = sql:variable("@p0")][1]/@Value'', ''nvarchar(255)'') as Col1
from #test t',@p0=N'ą'
go

-- works
declare @p0 nvarchar(4000) = N'a'
select t.Document.value('declare default element namespace "http://mynamespace.org/Schemas"; /FormDocument[1]/Fields[1]/Field[@Id = sql:variable("@p0")][1]/@Value', 'nvarchar(255)') as Col1
from #test t
go

-- doesn't work
declare @p0 nvarchar(4000) = N'ą'
select t.Document.value('declare default element namespace "http://mynamespace.org/Schemas"; /FormDocument[1]/Fields[1]/Field[@Id = sql:variable("@p0")][1]/@Value', 'nvarchar(255)') as Col1
from #test t
go

drop table #test;

Best Answer

Add the N prefix to the INSERT statement to preserve the XML as Unicode, eg

    insert into #test (Document) values
(N'<FormDocument xmlns="http://mynamespace.org/Schemas">
  <Fields>
    <Field Id="a" Value="qwerty" />
    <Field Id="ą" Value="123" />
  </Fields>
</FormDocument>')
Related Question