Convertsion between xml and a table structure

• seaport

  SSCrazy

  Points: 2491

  More actions

  July 17, 2008 at 12:21 pm

  #184896

  I am trying to get some simple code to do conversion between xml data and a table structure.

  From a table structure to xml, I can do the following:

  select rateid from rates

  FOR XML AUTO, TYPE, ELEMENTS

  The code is simple enough because I only need to add "FOR XML AUTO, TYPE, ELEMENTS" to the end of my "select" sql.

  From xml data to a table structure, I can do the following:

  DECLARE @x XML

  SET @x = '

  '

  SELECT

  x.item.value('rateid[1]', 'int') AS rateid

  FROM @x.nodes('//rates') as x(item)

  I wonder whether simpler code can achieve that. I mean the code without the need to specify the node "rates" and column "rateid".

  Certainly, the code will assume that the xml is a one table structure and has no attributes data.

  Any suggestion?

  Thanks in advance.

  Seaport

• GSquared

  SSC Guru

  Points: 260824

  More actions

  July 17, 2008 at 12:55 pm

  #844615

  It looks to me like you've already hit on the right solution. Someone else may have a better suggestion, but I'd go for it with that.

  - 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