In my T-SQL procedure I'm trying to extract a string value from the XML node using the .value() method, like this:
declare @criteria xml;
set @criteria = N'<criterion id="DocName"><value>abcd</value></criterion>';
declare @val nvarchar;
set @val = @criteria.value('(criterion[@id="DocName"]/value)[1]', 'nvarchar');
select @val;
I expected to get 'abcd' as a result, but I surprisingly got just 'a'.
So, the value method returns only the 1st character of the string. Can anybody tell me, what am I doing wrong? Thanks a lot.
P.S. I'm using MS SQL Server 2012
Don't use nvarchar without size. From documentation:
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.
If you don't know exact length, you always can use nvarchar(max):
declare @criteria xml;
set @criteria = N'<criterion id="DocName"><value>abcd</value></criterion>';
declare @val nvarchar(max);
set @val = @criteria.value('(criterion[@id="DocName"]/value)[1]', 'nvarchar(max)');
select @val;