sp_executesql with XML not working

• imSQrLy

  Ten Centuries

  Points: 1335

  More actions

  May 20, 2009 at 1:02 pm

  #131206

  If i hard code an xml statement it work. If i use sp_executesql it works. However if I use sp_executesql with a variable and then use that variable in the xml statement the expected result is not returned. Look at the 3rd section where i replace the 1 with @var. When @var is used ( an integer equaling 1) the statement returns zero length string. If you look at the 2nd section you will notice if i actually type in 1 it works.

  For good measure i added a section 4 as well where i make @var char instead of an int.

  Confused please help! See attachment for code.

  declare @XMLstring varchar(max),

  @XML xml,

  @Find varchar(max),

  @sql nvarchar(max),

  @parms nvarchar(max),

  @xml2 xml

  Select @XMLstring =

  '2testdomain\usertestname5/12/2009 1:25:25 PM'

  select @xml = cast(@xmlstring as xml)

  --section 1

  --works

  select cast(@XML.query('declare namespace DTS="www.microsoft.com/SqlServer/Dts";

  (/DTS:Executable/DTS:Property[1]/text())') as nvarchar(100))

  --section 2

  --works

  select @sql = 'select @out = cast(@XMLin.query(''declare namespace DTS="www.microsoft.com/SqlServer/Dts";

  (/DTS:Executable/DTS:Property[1]/text())'') as nvarchar(100))'

  SET @Parms = N'@var int, @XMLin XML, @out nvarchar(100) output';

  EXECUTE sp_executesql @sql, @Parms, @var = 1, @XMLin = @xml, @out = @Find output;

  select @Find

  --section 3

  --doesn't work

  select @sql = 'select @out = cast(@XMLin.query(''declare namespace DTS="www.microsoft.com/SqlServer/Dts";

  (/DTS:Executable/DTS:Property[@var]/text())'') as nvarchar(100))'

  SET @Parms = N'@var int, @XMLin XML, @out nvarchar(100) output';

  EXECUTE sp_executesql @sql, @Parms, @var = 1, @XMLin = @xml, @out = @Find output;

  select @Find

  --section 4

  --doesn't work either

  select @sql = 'select @out = cast(@XMLin.query(''declare namespace DTS="www.microsoft.com/SqlServer/Dts";

  (/DTS:Executable/DTS:Property[@var]/text())'') as nvarchar(100))'

  SET @Parms = N'@var char(1), @XMLin XML, @out nvarchar(100) output';

  EXECUTE sp_executesql @sql, @Parms, @var = '1', @XMLin = @xml, @out = @Find output;

  select @Find

  Jimmy

  "I'm still learning the things i thought i knew!"

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  May 20, 2009 at 5:01 pm

  #998002

  Why don't you just use REPLACE() to change [@var] to [1] in the @sql string BEFORE you execute it.

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  May 21, 2009 at 2:07 am

  #998109

  For the case where @var is an int, change

  DTS:Property[@var]

  to

  DTS:Property[sql:variable("@var")]

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• imSQrLy

  Ten Centuries

  Points: 1335

  More actions

  May 21, 2009 at 7:15 am

  #998235

  This worked. Thanks for the fix. If you know of any documentation that explains when to use the sql:variable syntax please forward. I will look around as well. Thanks again.

  Mark (5/21/2009)

  ---

  For the case where @var is an int, change

  DTS:Property[@var]

  to

  DTS:Property[sql:variable("@var")]

  Jimmy

  "I'm still learning the things i thought i knew!"