select XML data into a variable

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  May 9, 2012 at 4:35 am

  #268627

  hi folks

  this is proboably a really simple problem, but i just can't see the wood for the trees here.

  i have a result set with a single column that is the result of a very simple query - what i need to do is package these up as XML into a variable

  here's some sample code to show what i want to do (it's not my production code - this is just prototyping so that i can show the issue)

  declare @results xml

  select @results=name from sysobjects for XML AUTO

  i get the error

  "the FOR XML clause is not allowed in an assignment statement"

  this must be soooooo simple, i can't seem to figure out a way to do this

  hang my head in shame

  MVDBA

• Ant-Green

  SSC Guru

  Points: 113544

  More actions

  May 9, 2012 at 4:42 am

  #1484965

  does this help

  declare @results xml

  set @results = (select name from sysobjects for XML AUTO)

  select @results

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  May 9, 2012 at 4:46 am

  #1484970

  genius - thank you

  MVDBA