openxml only returns one row

On SQL Server 2000 the following only returns the first row.  Why? and how can I fix it?

declare @xml varchar(8000), @hDoc int

set @xml =
'<root>
      <RowId>DATA HERE</RowId>
      <RowId>MORE DATA HERE</RowId>
</root>'

exec master.dbo.sp_xml_preparedocument @hdoc out, @xml

select RowId
from openxml(@hdoc, '/root', 2)
with (RowId char(25))

exec master.dbo.sp_xml_removedocument @hdoc
LVL 3
xmstrAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello xmstr,

select RowId
from openxml(@hdoc, '/root/RowId')
with (RowId char(25) '.')



Aneesh R
0
 
xmstrAuthor Commented:
It works, Thank you aneeshattingal!

Can you tell me what the '.' does in the with line?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
(@hdoc, '/root/RowId', 2)  --- means you are at this node
and what you really need was, everything inside this tag, and '.' will take everything
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.