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.

score 11 · Accepted Answer · answered Jun 07 '11 at 06:24

11

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

answered Jun 07 '11 at 06:24

Mikael Eriksson

136,425
22
210
281

How can I use variable in the INSERT statement inside the modify method? – Si8 Aug 27 '15 at 19:05
@SiKni8 have a look at using sql:variable("") – Mikael Eriksson Aug 27 '15 at 19:53
Tried this: `Set @xml.modify('insert xs:string(sql:variable("@locAnchor")) into (/root/StartOne/Value6)[1]');` I keep getting an error: `XQuery [modify()]: Only non-document nodes can be inserted. Found "xs:string ?".` – Si8 Aug 27 '15 at 19:55
@SiKni8 Try this `set @xml.modify('insert text{sql:variable("@LocAnchor")} into (/root/StartOne/Value6)[1]');` – Mikael Eriksson Aug 28 '15 at 05:33
Extremely unfortunate. :-) Is this still true in 2018, or have there been improvements? I don't see anything in [the `replace` docs](https://learn.microsoft.com/en-us/sql/t-sql/xml/replace-value-of-xml-dml) to suggest there have been, but... – T.J. Crowder Mar 22 '18 at 08:33
@T.J.Crowder There is nothing done about this in later versions of SQL Server. It does however work if you use [typed xml](https://technet.microsoft.com/en-us/library/ms184277(v=sql.90).aspx). – Mikael Eriksson Mar 22 '18 at 08:58
@MikaelEriksson: Thanks. (I really should.) – T.J. Crowder Mar 22 '18 at 09:00

score 2 · Answer 2 · answered Sep 16 '13 at 17:08

2

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)

answered Sep 16 '13 at 17:08

AndyClaw

740
8
15

Very handy, this saves performing the exists check. – Justin Clarke Sep 19 '13 at 11:54

score 1 · Answer 3 · answered Jun 06 '11 at 18:57

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

score 0 · Answer 4 · answered Jun 06 '11 at 18:31

0

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

answered Jun 06 '11 at 18:31

woodshy

4,085
3
22
21

Update XML field with no text in T-SQL

4 Answers4

Linked

Related