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 *
from openxml (@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?