Retrieving data from elements and attributes.

• paulhumphris

  Hall of Fame

  Points: 3496

  More actions

  June 26, 2003 at 7:56 am

  #64829

  I have an XML document which has data held both in attributes and elements.

  I am trying to retreive all the information into a table using OPENXML in the following structure:

  id item

  1 Spanners

  2 Widgets

  I have tried the following examples but to no avail!!

  declare @idoc int

  exec sp_xml_preparedocument @idoc output,

  '<?xml version="1.0"?>

  <root>

  <item id="1">Spanners</item>

  <item id="2">Widgets</item>

  </root>'

  -- Attempt 1 - Only returns first row

  select*

  fromopenxml (@idoc, '/root', 3)

  with (id int '@id',

  item varchar(15) 'item')

  -- Attempt 2 - Only

  select *

  from openxml (@idoc, '/root/item', 2)

  with (id int '@id',

  item varchar(15) )

  -- Attempt 3 - Only

  select *

  from openxml (@idoc, '/root/item', 3)

  with (id int '@id',

  item varchar(15) '../item')

  exec sp_xml_removedocument @idoc

  Can anyone help and provide me with the OPENXML statement needed to return the data in the structure I require?

• paulhumphris

  Hall of Fame

  Points: 3496

  More actions

  June 26, 2003 at 8:27 am

  #463264

  Don't worry, found the answer....

  select *

  from openxml (@idoc, '/root/item', 3)

  with (id int '@id',

  item varchar(15) '.')