Replacing Value of Empty Node in SQL XML

Question

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>

possible duplicate of [Update XML field with no text in T-SQL](http://stackoverflow.com/questions/6254630/update-xml-field-with-no-text-in-t-sql) — Mikael Eriksson, Jun 20 '11 at 20:31
Awesome, Mikael. This solution seems way cleaner than the one pointed to by Andrey. Thanks a lot! — Chris, Jun 20 '11 at 20:44

score 8 · Answer 1 · edited Aug 29 '11 at 07:24

8

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

Try out this work for me

edited Aug 29 '11 at 07:24

Edwin de Koning

14,209
7
56
74

answered Aug 28 '11 at 11:24

jaspreet

151
2
3

What about if instead of `"foo"` I want a variable? http://stackoverflow.com/questions/32253235/how-to-use-if-else-statement-to-update-or-create-new-xml-node-entry-in-sql – Si8 Aug 27 '15 at 19:35
1

In this case you can use: ```' insert text{sql:variable("@variableName")} into (/root/node)[1]'``` – Marcello Aug 15 '18 at 17:21
This should be marked as the answer. I had the same issue as the OP and my addressing was fine (no missing slashes, etc.) -- this is what actually worked for an empty node; 'replace value' did NOT. Thank you @jaspreet ! – taiji123 Jun 05 '19 at 13:36

score 2 · Accepted Answer · edited May 23 '17 at 12:31

2

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

edited May 23 '17 at 12:31

Community

1
1

answered Jun 20 '11 at 17:55

Andrey Adamovich

20,285
14
94
132

2

The leading slash only makes the path absolute. Without it, the XPath will match any node whose path ends with `root/node`. Since there aren't any other nodes in the tree, in this instance, the leading slash makes no difference. – Chris Jun 20 '11 at 18:55
1

Check this out: http://stackoverflow.com/questions/2812036/update-empty-xml-tag-in-sql-server – Andrey Adamovich Jun 20 '11 at 19:51
not sure why this is the accepted answer; as @Chris mentioned above, the leading slash makes no difference in this scenario – taiji123 Jul 19 '22 at 20:26

score 0 · Answer 3 · answered Nov 01 '19 at 10:32

Necromancing.
Providing an actual answer:

You cannot do this in one pass.
You first need to set the value to empty (replace if it exists), and then insert it:

SET @tXML.modify('replace value of (//path[contains("0000X1,0000X2", @data-objid) and string-length(@data-objid) != 0]/text())[1] with ""')
SET @tXML.modify('insert text{"This Works"} into (//path[contains("0000X1,0000X2", @data-objid) and string-length(@data-objid) != 0])[1]')

example XML used:

DECLARE @tXML xml = '<svg>
<g>
<path></path>
<path data-objid="0000X1">tt</path>
<path data-objid="0000X2"></path>
<path data-objid="0000X3"></path>
</g>
</svg>';

Same goes for attributes.
There you need to insert the attribute first, but you need to check with [not(@data-objid)] if the attribute already exists. Once it has been inserted where it's needed, you can modify the values.

SET @tXML.modify('insert attribute data-objid {"1"} into (//path[not(@data-objid) and contains("0000X1,0000X2", @data-objid) and string-length(@data-objid) != 0])[1]')
SET @tXML.modify('replace value of (//path[contains("0000X1,0000X2", @data-objid) and string-length(@data-objid) != 0]/@data-objid)[1] with "Test"')

Thus you created the attribute (with a dummy value) where the attribute didn't exist, and then you updated it where it already exists, which means all the values have been updated now, including the dummy values.

Since modify/insert can only operate on a single record, you cannot bulk-update/insert anyway.
Interestingly, contrary to expectations, you can bulk delete... Not very consequent... I presume somebody ran out of time to implement this when shipping that crap.

Replacing Value of Empty Node in SQL XML

3 Answers3