XML querying - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

XML querying

Author

Topic

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2006-04-18 : 18:01:53

For some reason, when i execute the following snippet of code, i always get the same "item" value. Each order is selected properly. any ideas why?


DECLARE @hDoc int
declare @productId int
DECLARE @strXMLText nvarchar(4000)

set @productId = 1
SET @strXMLText = '<product>
	<items>
		<item order="1">text1</item>
		<item order="2">text2</item>
		<item order="3">text3</item>
	</items>
</product>'

EXEC sp_xml_preparedocument @hDoc OUTPUT, @strXMLText 

select	@productId, item, [order]
from	openxml	(@hdoc, '/product/items/item', 2)
with	(	
		item		varchar(256) '../item',
		[order]		int '@order'
)

EXEC sp_xml_removedocument @hdoc

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2006-04-18 : 18:04:16

for clarity, my results are:
1 text1 1
1 text1 2
1 text1 3

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2006-04-18 : 18:29:53

i fixed it.. the problem was in traversing the xml.. in my with clause, the item element should be:

item varchar(256) '.'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources