Update XML field with no text in T-SQL

Question

I've come across a problem in updating an SQL field in that what I've written works perfectly for xml nodes with a text present, however it trips up when the node is empty.

<filemeta filetype="Video">
  <heading>TEST</heading>
  <description />
</filemeta>

This code works fine;

UPDATE filemetaDB SET filemeta.modify('replace value of (/filemeta/heading/text())[1] with "TEST"');

However this breaks;

UPDATE filemetaDB SET filemeta.modify('replace value of (/filemeta/description/text())[1] with "notworking!"');

Thanks for any help.

Answer 1

I had a scenario where I ONLY wanted to update the EMPTY nodes.

UPDATE filemetaDB SET filemeta.modify('
    insert text{"Oh, this works!!"}
    into (/filemeta/description[not(node()) and not(text())])[1]
');

not(node()) means no children and not(text()) means no text content (possibly has children however, so mix and match these cases as needed)

Answer 2

This node (/filemeta/description/text())[1] does not exist in the XML so there is nothing to replace. You have to do an insert instead. If you have a scenario where you have a mix of empty nodes and nodes with a value you have to run two update statements.

declare @filemetaDB table(filemeta xml)

insert into @filemetaDB values
('<filemeta><description>Not empty</description></filemeta>'), -- Not empty node
('<filemeta><description/></filemeta>'),                       -- Empty node
('<filemeta></filemeta>')                                      -- Missing node

-- Replace value for nodes with value
update @filemetaDB
set filemeta.modify('replace value of (/filemeta/description/text())[1] with "TEST 1"')
where filemeta.exist('/filemeta/description/text()') = 1

-- Add text node for empty nodes
update @filemetaDB
set filemeta.modify('insert text{"TEST 2"} into (/filemeta/description)[1]')
where filemeta.exist('/filemeta/description/text()') = 0

select *
from @filemetaDB

Result:

filemeta
------------------------------------------------------
<filemeta><description>TEST 1</description></filemeta>
<filemeta><description>TEST 2</description></filemeta>
<filemeta />

Answer 3

you can check if the node has Data before updating such as:

IF EXISTS(
            SELECT  null
            FROM    filemetaDB
            WHERE   cast(filemeta.query('if (/filemeta/description[1]) then "T" else ""') as varchar) = 'T')
    BEGIN
        UPDATE filemetaDB SET filemeta.modify('replace value of (/filemeta/description/text())[1] with "notworking!"');
    END