Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Replacing Value of Empty Node in SQL XML

Tags:

sql-server

xml

I've come across a problem with modifying XML within SQL Server that doesn't seem to make sense to me. I want to update the value of an empty node, but it doesn't seem to be working. For instance, say I have this XML tree:

<root>
    <node />
</root>

and I want to update the value of <node /> with 'foo' so I have the XML query:

UPDATE  [Table]
SET     [XmlColumn].modify('
        replace value of (root/node/text())[1]
        with "foo"')

For some reason, this doesn't work. It treats the node like it doesn't exist. If the node already has a value (e.g., <node>bar</node>), it works just fine, but when the node is empty, it silently ignores the command, without even throwing any errors. Is there a way to make SQL Server acknowledge a replace value of on an empty node?

EDIT:

I want the end result of this query to be this:

<root>
    <node>
        foo
    </node>
</root>
like image 277
Chris Avatar asked Jun 20 '11 17:06

Chris


2 Answers

UPDATE  [Table] 
SET     [XmlColumn].modify(' insert text{"foo"} into (/root/node)[1]') 

Try out this work for me

like image 65
jaspreet Avatar answered Sep 20 '22 04:09

jaspreet


Will this be of any help: http://whyiamright.wordpress.com/2008/01/02/updating-xml-column-in-sql-server-2005/?

You seem to miss slash in the begining of xpath.

EDIT: Then it seems to be a duplicate of this question:

Update Empty XML Tag in SQL Server

like image 34
Andrey Adamovich Avatar answered Sep 23 '22 04:09

Andrey Adamovich