Helprequired in XML

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  September 5, 2008 at 6:10 am

  #197281

  DECLARE @idoc int

  DECLARE @doc varchar(1000)

  SET @doc ='

  '

  --Create an internal representation of the XML document.

  EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

  -- SELECT stmt using OPENXML rowset provider

  drop table #event

  create table #event

  (

  --CustomerIDvarchar(100)

  acct_idint

  )

  insert into #event

  SELECT *

  FROM OPENXML (@idoc, '/DUMP/OPTIONS/ACCOUNT/EVENTS',2)

  WITH (

  acct_id varchar(10) '../acct_id'

  )

  select * from #event

  EXEC sp_xml_removedocument @idoc

  i want to fetch acct_id value in #event table

  how can i do it ?

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• Gary Johnson-259336

  SSCommitted

  Points: 1673

  More actions

  September 5, 2008 at 2:03 pm

  #868032

  try this...

  DECLARE @XML XML

  SET @XML = '<root><Users><Account AccountID="10"/><Account AccountID="20"/></Users></root>'

  SELECT u.a.value('@AccountID','int') AccountID

  FROM @XML.nodes('//Users/Account') as u(a)

  AccountID

  -----------

  10

  20

  SET @XML = '<root><Users><Accounts><AccountID>10</AccountID><AccountID>20</AccountID></Accounts></Users></root>'

  SELECT u.a.value('.','int') AccountID

  FROM @XML.nodes('//Users/Accounts/AccountID') as u(a)

  AccountID

  -----------

  10

  20

  Gary Johnson
  Sr Database Engineer