I need to use XQuery to move a node between two XML data type fields...

I know I can delete a node like this:
SET MyXMLField1.modify
('delete
(//Node[@MyKey="123"])[1]')
Where ID = @ID

And I know I can insert a node like this:
SET MyXMLField1.modify
('insert
<Node
Text="SomeText">
</Node>
into
(//Node[@MyKey="123"])[1]')

But I need to move the node (maybe delete in one field, save it in memory,
and insert into another field). And all the sub-nodes need to go along with
it.

How can this be done? The XML is always pulled from MyXMLField1, and then is
moved into MyXMLField1 or MyXMLField2.

Derek Hart wrote:
There are functions sql:variable and sql:column that you can use to
bring in data from variables or columns. However with SQL Server 2005,
it is not supported to use variables of type xml in modify expressions e.g.

DECLARE @x1 XML;
DECLARE @x2 XML;

SET @x1 = N'<root>
<foo>bar</foo>
</root>';

SET @x2 = N'<root/>';

DECLARE @x3 XML;
SET @x3 = @x1.query('root/foo');

SET @x2.modify('insert sql:variable("@x3") into /root');

SELECT @x2;

gives an error. I think you mentioned SQL Server 2008, I am not sure
they have removed that restriction but you could try the above.


--

Martin Honnen --- MVP Data Platform Development
http://msmvps.com/blogs/martin_honnen/

Thanks for the help on these Martin.... yes, it does work in 2008, but it
needs the [1] item to always make sure that only one node is dealt with.
This works perfectly:

use MyDatabase

Declare @Source XML
Declare @GUIDsource varchar(50)
Declare @GUIDdest varchar(50)
Declare @UserID int

Set @UserID = 186
Set @GUIDsource = 'e6bad46e-a0b3-45a0-a84c-d879ebb842f4'
Set @GUIDdest = 'da98951a-91f9-4fc6-868b-6c612b08d74e'

-- Get the source XML that I want to copy into a variable.
Set @Source =
(select
tuser.FileTreePrivateXML.query('//Node[@GUID=sql:variable("@GUIDsource")]')
from tuser
where UserID=186)

-- Now change the source XML to have a unique value so I can delete it after
I move into another field.
-- Copy the GUID used in the GUID attribute into the Text attribute. That
will make sure the
-- node is unique since users would never see the GUID.
Update tUser Set FileTreePrivateXML.modify
('replace value of
(//Node[@GUID=sql:variable("@GUIDsource")]/@Text)[1]
with sql:variable("@GUIDsource")')
Where UserID=@UserID

-- Now insert the Source into the Destination.
update tUser
Set FileTreeXML.modify('insert sql:variable("@Source")
into (//Node[@GUID=sql:variable("@GUIDdest")])[1]')
Where UserID=@UserID

-- Now delete the Original Source; it has the GUID as the Text attribute so
-- it will be different than the one that was just moved so it can be
found.
update tUser
SET FileTreePrivateXML.modify
('delete
(//Node[@GUID=sql:variable("@GUIDsource")][@Text=sql:variable("@GUIDsource")])[1]')
Where UserID = @UserID




"Martin Honnen" <mahotrash (AT) yahoo (DOT) de> wrote