openxml only returns one row

Posted on 2007-10-05
Last Modified: 2008-01-09
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 =
      <RowId>DATA HERE</RowId>
      <RowId>MORE DATA HERE</RowId>

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
Question by:xmstr
    LVL 75

    Accepted Solution

    Hello xmstr,

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

    Aneesh R
    LVL 3

    Author Comment

    It works, Thank you aneeshattingal!

    Can you tell me what the '.' does in the with line?
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    (@hdoc, '/root/RowId', 2)  --- means you are at this node
    and what you really need was, everything inside this tag, and '.' will take everything

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now