Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    12

    Question Unanswered: 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,

    alex

  2. #2
    Join Date
    Feb 2004
    Posts
    2
    If 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) '.')

  3. #3
    Join Date
    Jan 2004
    Posts
    12

    Smile

    thanks alot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •