+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 11 to 15 of 15

How to 'move' xml nodes via xquery in SQL Server 2005

  1. Re: How to 'move' xml nodes via xquery in SQL Server 2005

    > The tricky part is to know how to re-insert the node above 'atlantic'
    > without knowing the 'atlantic' node's position or id, other than the
    > fact that it's 'just one above' the 'indian' node.


    If you know a node, finding the nearest preceding sibling is like,

    (//*[parent::*=$a/parent::* and . << $a])[last()]

    Test code is,

    declare @x xml
    set @x=''
    select @x.query('
    for $a in //d
    return (//*[parent::*=$a/parent::* and . << $a])[last()]
    ')

    The result should be , which is 's nearest preceding sibling.

    I have no idea how to apply the syntax to the modify() method. At least you
    can recompose the xml altogether by xquery.


  2. Re: How to 'move' xml nodes via xquery in SQL Server 2005

    My master, you rang :-).

    Some quick explanations:

    1. position() in SQL Server 2005 can only be used inside a predicate. We may
    allow it in other places as well in future releases.
    2. Insert into always appends at the end. You can also insert always in the
    beginning by using insert .... as first into .... For exact placement, you
    need to use insert ... before ... or insert ... after ....

    So in the simple case, the first example, you write:

    UPDATE tbl_tree SET theTree.modify('
    insert (//node()[@id="7"])[1]
    into (//node()[@id="1"])[1]
    ')

    Aside: Like Han, I don't think you should use node() since you are only
    looking at elements, and you should avoid // if you know the exact path. In
    newer CTP builds you will actually get a type error since a target for
    insertion can only be a document or element node. I will replace node() with
    * below but continue to use //...

    and then ask how you can delete the older version of the node with the
    attribute id being 7. Since the newly inserted node is the last one under
    the target node, you can search for every node with id 7 that is not the
    same node.

    So something along the line of:

    update tbl_tree SET theTree.modify('delete //*[@id="7"][not(. is
    (//*[@id="1"]/*[@id="7"])[last()])]')

    should help.

    If you insert it in a specific position, it becomes a bit more complex. For
    that, I would need some more concrete example to better understand the
    general parts. But note that you can adopt the above idea (to delete the
    node if it is not the same as the one you inserted) as well...

    Does that help?
    Michael

    "Han" wrote in message
    news:%23cPnAwTZFHA.3984@TK2MSFTNGP10.phx.gbl...
    > Never give up :)
    >
    > There are some communication problem. Michael Rys may come.
    >
    > "jamesagnew" wrote in message
    > news:1117457279.819059.242580@g49g2000cwa.googlegroups.com...
    >> I'm not sure I understand how I can insert A3 *before* A2 without
    >> knowing A2's position and at the same time only have a single A3 node
    >> (since the insert process will create a copy, requiring me to delete
    >> the 'original' A3 node).
    >>
    >> Thanks for your support and patience - much appreciated :)
    >>

    >
    >




  3. Re: How to 'move' xml nodes via xquery in SQL Server 2005

    Thanks Michael and Han - that's very helpful.

    Here's a more concrete example of what I'm trying to achieve:









    I'd like to nudge any one of the ocean nodes up or down a single
    postion without any prior knowledge of the names or IDs of the
    siblings.

    For example, I'd like to nudge 'arctic' up a position so it is above
    'indian' but the only information I will have at excution time is the
    ID of the node I want to move (namely 'arctic').

    If I were able to calculate the position of 'arctic' and assign it to
    an int variable @positionOfNodeToMove then I could

    a) insert it before the node whose position() is
    @positionOfNodeToMove-1 to achieve a single nudge up

    b) insert it after the node whose position() is @positionOfNodeToMove+1
    to achieve a single nudge down

    Is this the right approach or is there a better solution?

    Thanks again for your support.


  4. Re: How to 'move' xml nodes via xquery in SQL Server 2005

    Prophecy came true -:)

    Hi jamesagnew

    Yes, I think we have been saying that to now, just without real example. See
    if this is what you want,

    declare @x xml
    set @x='





    '

    set @x.modify('
    insert (//*[@name="arctic"])[1] before
    ((//*[@name="arctic"])[1]/../*[. << (//*[@name="arctic"])[1]])[last()]
    ')
    select @x

    All you know is /arctic/. The script finds the closest preceding sibling of
    the /arctic/ node, and insert the node before the closest preceding sibling.
    Now, the result is duplicated /arctic/ nodes. The rest will not be a problem
    if you follow Michael's trick. I think each GUIDs are unique. Result of the
    script is as follows,










    --
    Pohwan Han. Seoul. Have a nice day.
    "jamesagnew" wrote in message
    news:1117699382.361875.158870@z14g2000cwz.googlegroups.com...
    > Thanks Michael and Han - that's very helpful.
    >
    > Here's a more concrete example of what I'm trying to achieve:
    >
    >
    >
    >
    >
    >
    >
    >

    >
    > I'd like to nudge any one of the ocean nodes up or down a single
    > postion without any prior knowledge of the names or IDs of the
    > siblings.
    >
    > For example, I'd like to nudge 'arctic' up a position so it is above
    > 'indian' but the only information I will have at excution time is the
    > ID of the node I want to move (namely 'arctic').
    >
    > If I were able to calculate the position of 'arctic' and assign it to
    > an int variable @positionOfNodeToMove then I could
    >
    > a) insert it before the node whose position() is
    > @positionOfNodeToMove-1 to achieve a single nudge up
    >
    > b) insert it after the node whose position() is @positionOfNodeToMove+1
    > to achieve a single nudge down
    >
    > Is this the right approach or is there a better solution?
    >
    > Thanks again for your support.
    >



  5. Re: How to 'move' xml nodes via xquery in SQL Server 2005

    Here is the reply the same forums post (thanks Han for your modify code
    below that I slightly changed without having to use a parent since I assumed
    that I have the same parent :-):

    http://forums.microsoft.com/msdn/Sho...ID=15466#15466

    Best regards
    Michael

    "Han" wrote in message
    news:%23qahTT6ZFHA.3328@TK2MSFTNGP09.phx.gbl...
    > Prophecy came true -:)
    >
    > Hi jamesagnew
    >
    > Yes, I think we have been saying that to now, just without real example.
    > See if this is what you want,
    >
    > declare @x xml
    > set @x='
    >
    >
    >
    >
    >
    >
    '
    >
    > set @x.modify('
    > insert (//*[@name="arctic"])[1] before
    > ((//*[@name="arctic"])[1]/../*[. << (//*[@name="arctic"])[1]])[last()]
    > ')
    > select @x
    >
    > All you know is /arctic/. The script finds the closest preceding sibling
    > of the /arctic/ node, and insert the node before the closest preceding
    > sibling. Now, the result is duplicated /arctic/ nodes. The rest will not
    > be a problem if you follow Michael's trick. I think each GUIDs are unique.
    > Result of the script is as follows,
    >
    >
    >
    >
    >
    >
    >
    >
    >

    >
    > --
    > Pohwan Han. Seoul. Have a nice day.
    > "jamesagnew" wrote in message
    > news:1117699382.361875.158870@z14g2000cwz.googlegroups.com...
    >> Thanks Michael and Han - that's very helpful.
    >>
    >> Here's a more concrete example of what I'm trying to achieve:
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>

    >>
    >> I'd like to nudge any one of the ocean nodes up or down a single
    >> postion without any prior knowledge of the names or IDs of the
    >> siblings.
    >>
    >> For example, I'd like to nudge 'arctic' up a position so it is above
    >> 'indian' but the only information I will have at excution time is the
    >> ID of the node I want to move (namely 'arctic').
    >>
    >> If I were able to calculate the position of 'arctic' and assign it to
    >> an int variable @positionOfNodeToMove then I could
    >>
    >> a) insert it before the node whose position() is
    >> @positionOfNodeToMove-1 to achieve a single nudge up
    >>
    >> b) insert it after the node whose position() is @positionOfNodeToMove+1
    >> to achieve a single nudge down
    >>
    >> Is this the right approach or is there a better solution?
    >>
    >> Thanks again for your support.
    >>

    >