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 — Mikael Eriksson
– Mikael Eriksson, Commented Jun 20, 2011 at 20:31
Awesome, Mikael. This solution seems way cleaner than the one pointed to by Andrey. Thanks a lot! — Chris
– Chris, Commented Jun 20, 2011 at 20:44

Edwin de Koning · Accepted Answer · 2011-08-29 07:24:48Z

9

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

Try out this work for me

edited Aug 29, 2011 at 7:24

Edwin de Koning

14.4k9 gold badges60 silver badges74 bronze badges

answered Aug 28, 2011 at 11:24

jaspreet

1612 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Si8 Over a year ago

What about if instead of "foo" I want a variable? stackoverflow.com/questions/32253235/…

Marcello Over a year ago

In this case you can use: ' insert text{sql:variable("@variableName")} into (/root/node)[1]'

taiji123 Over a year ago

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 !

Community · Accepted Answer · 2017-05-23 12:31:20Z

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, 2017 at 12:31

CommunityBot

11 silver badge

answered Jun 20, 2011 at 17:55

Andrey Adamovich

20.7k16 gold badges97 silver badges133 bronze badges

3 Comments

Chris Over a year ago

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.

Andrey Adamovich Over a year ago

Check this out: stackoverflow.com/questions/2812036/…

taiji123 Over a year ago

not sure why this is the accepted answer; as @Chris mentioned above, the leading slash makes no difference in this scenario

Stefan Steiger · Accepted Answer · 2019-11-01 10:32:06Z

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.

Collectives™ on Stack Overflow

Replacing Value of Empty Node in SQL XML

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related