How to select as rows using XQuery value()

• Senthil T

  Hall of Fame

  Points: 3367

  More actions

  April 20, 2009 at 12:54 pm

  #129422

  Hi,

  I have this sample xml data.

  DECLARE @depxml XML

  SET @depxml = '12100102'

  If I do a SELECT like this,

  SELECT @depxml.value('(/Department/Employees)[1]', 'int') AS Employees

  the result set is 100102. How can I get the resultset like this,

  100

  102

  Thanks

• GSquared

  SSC Guru

  Points: 260824

  More actions

  April 20, 2009 at 1:09 pm

  #980985

  You need to use the nodes function before the value function. Try this, see if you can what you want from there:

  select x.y.query('.').value('.[1]','int')

  from @depxml.nodes('(DEPARTMENT/EMPLOYEES/EMPID)') x(y)

  Look up the nodes function in Books Online. It'll explain how this works.

  - 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

• Senthil T

  Hall of Fame

  Points: 3367

  More actions

  April 20, 2009 at 5:23 pm

  #981119

  This worked.

  SELECT x.y.value('.', 'int')

  from @depxml.nodes('(DEPARTMENT/EMPLOYEES/EMPID)') x(y)