FOR XML PATH variable

• mjackson-527564

  SSC Veteran

  Points: 247

  More actions

  July 2, 2008 at 5:24 pm

  #194612

  I have a stored procedure that returns xml. Using the FOR XML type of query. It is fairly complicated and has several nested selects that work fine and performance is also good. However I have a requirement to change a tag based on what the data is. Currently the last line of one of the selects is

  For xml path('order'), type

  I would like to replace the path with a case statement or at leas a variable declares and set earlier in the sp.

  in simple form, I tried

  for xml path(select case substring(cast(B.book_amt as varchar(20)),1,1)

  when '-' then 'return'

  else 'order'

  end)

  , type

  I even tried

  For xml path(@orderclass), type

  Is there a way to replace the path string with a variable?

  mj

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  July 2, 2008 at 8:42 pm

  #837575

  Dynamic SQL.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]