Update XML field with no text in T-SQL

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/开发者_StackOverflow社区text())[1] with "notworking!"');

Thanks for any help.

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 />

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)

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

I think that this method will not work for empty nodes.
You also coud check this thead: link