insert value from xml to the table

• SQL006

  SSCertifiable

  Points: 5071

  More actions

  February 7, 2012 at 3:42 am

  #100439

  Hi

  follwing is the code i had use

  DECLARE @GroupID int = 1

  DECLARE @Agency xml

  SET @Agency = '<Agency>

  <AgencyID>1</AgencyID>

  <AgencyID>2</AgencyID>

  <AgencyID>3</AgencyID>

  </Agency>'

  CREATE TABLE #temp (id int identity,AgencyID int,GroupID int )

  INSERT INTO #temp

  (

  AgencyID

  ,GroupID

  )

  SELECT

  V.y.value('(//AgencyID)[1]','int') AS AgencyID

  ,@GroupID

  FROM @Agency.nodes('/Agency/AgencyID') AS V(y)

  SELECT * FROM #temp

  DROP TABLE #temp

  this resul is coming while executing the above code, the agency id is not coming right instaed of coming 1,2,3 it is repeating 1

  id AgencyID GroupID

  111

  211

  311

  i want the following result

  id AgencyID GroupID

  111

  221

  331

• srikant maurya

  Hall of Fame

  Points: 3293

  More actions

  February 7, 2012 at 5:50 am

  #1443708

  Try this

  CREATE TABLE #temp (id int identity,AgencyID int,GroupID int )

  INSERT INTO #temp

  (

  AgencyID

  ,GroupID

  )

  SELECT

  -- V.y.value('(//AgencyID)[1]','int') AS AgencyID

  V.y.value('.','int')

  ,@GroupID

  FROM @Agency.nodes('/Agency/AgencyID') AS V(y)

  SELECT * FROM #temp

  DROP TABLE #temp

• SQL006

  SSCertifiable

  Points: 5071

  More actions

  February 7, 2012 at 6:18 am

  #1443723

  thanks srikant

  can u explain why you r using '.' instead of tags