FOR XML EXPLICIT problem

• Doylie

  Right there with Babe

  Points: 799

  More actions

  May 5, 2009 at 4:46 am

  #103727

  Hi All,

  I am VERY new to dealing with XML thru SQL and even newer at using "FOR XML EXPLICIT" (an hour ago!)

  I am trying to return two columns. I want to return the XML that has been built up based on a parameter passed (

  script to create table and some data below) but i need to also return another column called RtnMsg that will either have success or error based on some error checking i do before. I cant work out how to return the two columns together, and cant use insert or into with the FOR xml statement...help!!!

  i hope this makes sense...

  Thanks in advance

  CREATE TABLE [#tempa]

  (

  MkAccountID VARCHAR(100),

  NAME VARCHAR(100)

  )

  INSERT INTO #tempa

  SELECT '13','Test account'

  INSERT INTO #tempa

  SELECT '14','Test account 1'

  INSERT INTO #tempa

  SELECT '15','test account €'

  SELECT 1 as Tag

  , 0 as Parent

  , MkAccountID as [SUCCESS!1!MkAccountID!element]

  , Name as [SUCCESS!1!AccountName!cdata]

  FROM #tempa

  FOR XML EXPLICIT

• LutzM

  SSC Guru

  Points: 107049

  More actions

  May 5, 2009 at 12:18 pm

  #989092

  Please provide sample data with the result set you're expecting.

  The statement "how to return the two columns together" is not clear.

  When running your test code the result shows:

  13

  14

  15

  In this set the two columns you're asking for are "together" - so you requirement is met, at least from my point of view 😉

  Also, please include the source of your third column you'd like to add in your response.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• GSquared

  SSC Guru

  Points: 260824

  More actions

  May 5, 2009 at 12:26 pm

  #989097

  If you provide a sample of the output you're looking for, we can probably help you get there.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon