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

[jamesagnew]

Hi

I'm trying to write a query that moves a node (and its children) from
one part of an xml tree to another.

Here's what I've got so far:

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

This successfully copies node 7 into node 1, but has also leaves the
original copy of node 7.

The problem I have is how to delete that original node whilst leaving
the newly inserted copy untouched?

I can't filter by id number, because that exists in two places, nor can
I rewrite the id number at the time of copying.

I thought about recording the parentID and position() of the original
node and then only delete the node that matches, but I've been unable
to get position() to return an value in SQL Server 2005.

Is there a simpler way to do this?

I've been hitting my head against this all day so I'd really appreciate
some guidance.

Many thanks, James

[Han]

Why not delete the node after that?

.modify('delete //node()[@id=7 and not(parent::*[@id=1])]')

By the way // expression should be used with caution.
--
Pohwan Han. Seoul. Have a nice day.

Post by jamesagnew
Hi
I'm trying to write a query that moves a node (and its children) from
one part of an xml tree to another.
UPDATE tbl_tree SET theTree.modify('
')
This successfully copies node 7 into node 1, but has also leaves the
original copy of node 7.
The problem I have is how to delete that original node whilst leaving
the newly inserted copy untouched?
I can't filter by id number, because that exists in two places, nor can
I rewrite the id number at the time of copying.
I thought about recording the parentID and position() of the original
node and then only delete the node that matches, but I've been unable
to get position() to return an value in SQL Server 2005.
Is there a simpler way to do this?
I've been hitting my head against this all day so I'd really appreciate
some guidance.
Many thanks, James

[jamesagnew]

Thanks Pohwan

Your suggestion would work if the node is moved to a different parent,
but if it's move 'up' or 'down' amongst its siblings (i.e. it keeps the
same parent), I'd need to have an extra check, such as position() but
that doesn't seem to be available in SQL Server 2005 as a return value
that I could store as a variable to compare against later.

Regarding your comment about // - yes, I wondered that myself, but is
there a better way to search for a node whose position is unknown but
always has a unique identifier?

Thanks again, James

[Han]

Post by jamesagnew
Your suggestion would work if the node is moved to a different parent,
but if it's move 'up' or 'down' amongst its siblings (i.e. it keeps the
same parent), I'd need to have an extra check, such as position() but
that doesn't seem to be available in SQL Server 2005 as a return value
that I could store as a variable to compare against later.

The node may be inserted into the same parent when,

(//*[@id=7])[1]/parent::* = (//*[@id=1])[1] = true

Then, I would like to control it *before* insert.

.modify() where field.exist('the-above-query') = 0

If you don't mind document order.

BTW, sometimes the operators <<, >> can be workaround for the missing
position() function. But not always.

Post by jamesagnew
Regarding your comment about // - yes, I wondered that myself, but is
there a better way to search for a node whose position is unknown but
always has a unique identifier?

Yes, no way but // if you perfectly don't know the position. The cost is
expensive. For example, we are dealing with just two nodes(one with @id=1
and another with @id=7) in the whole document no matter how many nodes of
the same criteria are in the document.

[jamesagnew]

Post by Han
The node may be inserted into the same parent when,
Then, I would like to control it *before* insert.
.modify() where field.exist('the-above-query') = 0
If you don't mind document order.

Alas, document order is very important.

Starting with...

A
|--A1
|
|--A2
|
|--A3
|
|--A4


Within the parent node (A) I'd like to 'nudge' child node A3 above
child node A2, so we have the following:

A
|
|--A1
|
|--A3
|
|--A2
|
|--A4

This needs to be performed at the database level, not via an xslt
trasformation. The problem is that the 'nudge' will not know the id of
the destination node i.e. the immediate preceding sibling.

So I need some way of determining the destination node above which A3
is to be inserted.

Ideally I would have used position() and inserted the node above the
node whose position() = //node()[@id="A3"]/position()-1

...but that XPath implementation appears not to work in SQL Sever 2005.

Any ideas?

[Han]

You can know the position but may not need it. I think you want
insert-before instead. e.g.,

.modify(insert into <a3/> before (//*[@id="a3"])[1])

Lets kick out node() from now on. At least '*' is 10 times more efficient
than 'node()'.

Additionally, I think you mean count(preceding-sibling::*) + 1 by
position(). Then, something like,

for $a in /a/b[@id="3"]
return count($a/../*[. << $a]) + 1

should return the position() as you mean. But I am not sure you really need
it.
--
Pohwan Han. Seoul. Have a nice day.

Post by jamesagnew

Post by Han
The node may be inserted into the same parent when,
Then, I would like to control it *before* insert.
.modify() where field.exist('the-above-query') = 0
If you don't mind document order.

Alas, document order is very important.
Starting with...
A
|--A1
|
|--A2
|
|--A3
|
|--A4
Within the parent node (A) I'd like to 'nudge' child node A3 above
A
|
|--A1
|
|--A3
|
|--A2
|
|--A4
This needs to be performed at the database level, not via an xslt
trasformation. The problem is that the 'nudge' will not know the id of
the destination node i.e. the immediate preceding sibling.
So I need some way of determining the destination node above which A3
is to be inserted.
Ideally I would have used position() and inserted the node above the
...but that XPath implementation appears not to work in SQL Sever 2005.
Any ideas?

[jamesagnew]

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 :)

[Han]

Never give up :)

There are some communication problem. Michael Rys may come.

Post by jamesagnew
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 :)

[Michael Rys [MSFT]]

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

Post by Han
Never give up :)
There are some communication problem. Michael Rys may come.

Post by jamesagnew
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 :)

[jamesagnew]

Thanks Michael and Han - that's very helpful.

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

<folder id="B4790C9E-64B2-4818-B639" name="oceans">
<folder id="26A62C24-51D0-42CB-9AEF" name="north pacific" />
<folder id="77446ECF-5178-40BA-915B" name="atlantic" />
<folder id="AC78A55B-BDFF-473B-BC95" name="indian" />
<folder id="3CA5F05E-350F-49C8-9831" name="arctic" />
<folder id="26A62C24-51D0-42CB-9AEF" name="south pacific" />
</folder>

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.

[Han]

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='<folder id="B4790C9E-64B2-4818-B639" name="oceans">
<folder id="26A62C24-51D0-42CB-9AEF" name="north pacific" />
<folder id="77446ECF-5178-40BA-915B" name="atlantic" />
<folder id="AC78A55B-BDFF-473B-BC95" name="indian" />
<folder id="3CA5F05E-350F-49C8-9831" name="arctic" />
<folder id="26A62C24-51D0-42CB-9AEF" name="south pacific" />
</folder>'

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,

<folder id="B4790C9E-64B2-4818-B639" name="oceans">
<folder id="26A62C24-51D0-42CB-9AEF" name="north pacific" />
<folder id="77446ECF-5178-40BA-915B" name="atlantic" />
<folder id="3CA5F05E-350F-49C8-9831" name="arctic" />
<folder id="AC78A55B-BDFF-473B-BC95" name="indian" />
<folder id="3CA5F05E-350F-49C8-9831" name="arctic" />
<folder id="26A62C24-51D0-42CB-9AEF" name="south pacific" />
</folder>
--
Pohwan Han. Seoul. Have a nice day.

Post by jamesagnew
Thanks Michael and Han - that's very helpful.
<folder id="B4790C9E-64B2-4818-B639" name="oceans">
<folder id="26A62C24-51D0-42CB-9AEF" name="north pacific" />
<folder id="77446ECF-5178-40BA-915B" name="atlantic" />
<folder id="AC78A55B-BDFF-473B-BC95" name="indian" />
<folder id="3CA5F05E-350F-49C8-9831" name="arctic" />
<folder id="26A62C24-51D0-42CB-9AEF" name="south pacific" />
</folder>
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
a) insert it before the node whose position() is
@positionOfNodeToMove-1 to achieve a single nudge up
to achieve a single nudge down
Is this the right approach or is there a better solution?
Thanks again for your support.

[Michael Rys [MSFT]]

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/ShowPost.aspx?PostID=15466#15466

Best regards
Michael

Post by Han
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,
<folder id="26A62C24-51D0-42CB-9AEF" name="north pacific" />
<folder id="77446ECF-5178-40BA-915B" name="atlantic" />
<folder id="AC78A55B-BDFF-473B-BC95" name="indian" />
<folder id="3CA5F05E-350F-49C8-9831" name="arctic" />
<folder id="26A62C24-51D0-42CB-9AEF" name="south pacific" />
</folder>'
')
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,
<folder id="B4790C9E-64B2-4818-B639" name="oceans">
<folder id="26A62C24-51D0-42CB-9AEF" name="north pacific" />
<folder id="77446ECF-5178-40BA-915B" name="atlantic" />
<folder id="3CA5F05E-350F-49C8-9831" name="arctic" />
<folder id="AC78A55B-BDFF-473B-BC95" name="indian" />
<folder id="3CA5F05E-350F-49C8-9831" name="arctic" />
<folder id="26A62C24-51D0-42CB-9AEF" name="south pacific" />
</folder>
--
Pohwan Han. Seoul. Have a nice day.

Post by jamesagnew
Thanks Michael and Han - that's very helpful.
<folder id="B4790C9E-64B2-4818-B639" name="oceans">
<folder id="26A62C24-51D0-42CB-9AEF" name="north pacific" />
<folder id="77446ECF-5178-40BA-915B" name="atlantic" />
<folder id="AC78A55B-BDFF-473B-BC95" name="indian" />
<folder id="3CA5F05E-350F-49C8-9831" name="arctic" />
<folder id="26A62C24-51D0-42CB-9AEF" name="south pacific" />
</folder>
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
a) insert it before the node whose position() is
@positionOfNodeToMove-1 to achieve a single nudge up
to achieve a single nudge down
Is this the right approach or is there a better solution?
Thanks again for your support.

[Eugene Kogan [MSFT]]

James,

will something like the following work for you?

declare @x xml
set @x=N'<elt><ch id="1"/><ch id="2"/><ch id="3"/><ch id="4"/></elt>'
select @x
set @x.modify('insert (/elt/ch[@id="3"])[1] before (/elt/ch[@id="2"])[1]')
select @x
set @x.modify('delete (/elt/ch[@id="3"])[2]')
select @x

Regards,
Eugene
---
This posting is provided "AS IS" with no warranties, and confers no rights.

Post by jamesagnew
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 :)

[jamesagnew]

Hi Eugene

Thanks for your response.

The problem we've got is that at the time of insertion, we don't know
the id number of the node above which <id=3> should be inserted.

I've used 1,2,3 for the id numbers to simplify the example above, but
in reality the id values are GUIDs.

Something like this, actually:

<folder id="B4790C9E-64B2-4818-B639-DB813A231A3F" name="oceans">
<folder id="26A62C24-51D0-42CB-9AEF-6A716F1BFE4" name="pacific" />
<folder id="77446ECF-5178-40BA-915B-2CD52B672452" name="atlantic" />
<folder id="AC78A55B-BDFF-473B-BC95-D113F23BC2C1" name="indian" />
</folder>

I'd like to move the node for 'indian' above the node for 'atlantic'.

I've got the deletion part working ok, by assigning the value of the
node containing 'indian' to a variable of type xml called @focusNode

I can then safely delete the node from the tree since its value is
stored in memory.

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.

Maybe I'm missing something obvious as I would have thought this should
be easy to accomplish with position() but so far I've been unable to
crack the problem!

BTW - this solution is only for single nudges up and down the tree
keeping the same parent i.e. inserted above the target's preceding
sibling and inserting below the target's following sibling.

Any further help much appreciated.

Best, James

[Han]

Post by jamesagnew
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='<a><b/><c/><d/></a>'
select @x.query('
for $a in //d
return (//*[parent::*=$a/parent::* and . << $a])[last()]
')

The result should be <c/>, which is <d/>'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.