XQuery trivia

• Igor Birioukov

  Old Hand

  Points: 340

  More actions

  October 4, 2006 at 7:21 am

  #94787

  DECLARE

  @mainxml xml, @addxml xml, @s-2 varchar(1024)

  SET

  @mainxml ='<root><a><b>bvalue</b></a></root>'

  SET

  @addxml = '<c>cvalue</c>'

  SET

  @s-2 = cast(@addxml as varchar(1024))

   

  --SET @mainxml.modify('insert (<c>cvalue</c&gt as last into (/root/a)[1]')

  SET

  @mainxml.modify('insert sql:variable("@s") as last into (/root/a)[1]')

  SELECT

  @mainxml

  --------------------------------------------

  I get syntax error when running code above. Who know what is wrong? Is it possible at all to use sql:variable(0 function to insert node rather than text or attibute value?

  
  Igor Birioukov

• DanKennedy

  Hall of Fame

  Points: 3736

  More actions

  October 5, 2006 at 1:43 am

  #664235

  I don't think sql:variable is supported in XQuery modify statements. The only way I can get it to work is with dynamic sql as follows. Not exactly pretty I know but hey !

  DECLARE @mainxml xml, @addxml xml, @stmt nvarchar(max)

  SET @mainxml = N'<root><a><b>bvalue</b></a></root>'

  SET @addxml = N'<c>cvalue</c>'

  SET @stmt = N'SET @mainxml.modify(''insert ' + CAST(@addxml AS nvarchar(max)) + ' as last into (/root/a)[1]'')'

  EXEC sp_executesql @stmt, N'@mainxml xml OUTPUT', @mainxml = @mainxml OUTPUT

  SELECT @mainxml

  ---

  Dan
  www.firstcs.co.uk

• Igor Birioukov

  Old Hand

  Points: 340

  More actions

  October 5, 2006 at 7:04 am

  #664292

  Thank you!

  The workaround is better than one I came up with. I posted the same on MSDN, no response yet.

  
  Igor Birioukov