openxml rowset problem

hi all,

I'm trying to get some xml data into sql server but i ran into this problem: openxml seems to repeat results ...

I simplified my example to this:

----------------
Declare @.rDoc int,
@.sDoc varchar(4000)

Set @.sDoc = '
<ROOT>
<Rider>aaa</Rider>
<Rider>bbb</Rider>
<Rider>ccc</Rider>
</ROOT>'

EXEC sp_xml_preparedocument @.rDoc OUTPUT, @.sDoc

SELECT *
FROM OPENXML (@.rDoc, 'ROOT/Rider', 1)
WITH (RiderName varchar(50) '../Rider')
------------------

it returns 3 records for the 'rider' elements, _but_ all the records contain the text 'aaa' :/

does anybody know the solution to this?

cheers,

alexIf you did the WITH statement below instead it would work - note also as a general practice you should avoid walking "up" the xml data structure (the '../Rider' construct) for performance reasons

Declare @.rDoc int,
@.sDoc varchar(4000)

Set @.sDoc = '
<ROOT>
<Rider>aaa</Rider>
<Rider>bbb</Rider>
<Rider>ccc</Rider>
</ROOT>'

EXEC sp_xml_preparedocument @.rDoc OUTPUT, @.sDoc

SELECT *
FROM OPENXML (@.rDoc, 'ROOT/Rider', 1)
WITH (RiderName varchar(50) '.')|||thanks alot! :)