1

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;
3

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>')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.