CODEWITHSUNDEEP
sql-serverxml
Chris
Chris

Reputation: 1059

Replacing Value of Empty Node in SQL 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>

Upvotes: 8

Views: 13017

Answers (3)

Stefan Steiger
Stefan Steiger

Reputation: 82336

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.

Upvotes: 0

jaspreet
jaspreet

Reputation: 151

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

Try out this work for me

Upvotes: 8

Andrey Adamovich
Andrey Adamovich

Reputation: 20683

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

Upvotes: 2

Related Questions