Update Empty XML Tag in SQL Server

Question

I'm trying to update an empty XML Tag in my Xml string on sql server; it says the row is updated when i run the following script, but when I view the XML; nothing has changed:

Declare @newValue varchar(100)
select @newValue = '01'

    update dbo.UploadReport
    set XmlTest.insert('replace value of (/CodeFiveReport/Owner/AgencyID/text())[1] with sql:variable("@newValue")') 
    where id = 'myId'

The xml after still appears as this in the databse

<AgencyID />

What am I doing wrong?

I've tried @AgencyID without the text() at the end and still no avail...

Answer 1

As far as I know from my own experience, you cannot do this in one step, since the <AgencyID/> element really has no text() - so therefore, you cannot replace it.

You might have to use something like:

DECLARE @newValue VARCHAR(100)
SELECT @newValue = '01'

-- first update - add a new <AgencyID>...</AgencyID> node    
UPDATE dbo.UploadReport
SET XmlTest.modify('insert <AgencyID>{sql:variable("@newValue")}</AgencyID> as last into (/CodeFiveReport/Owner)[1]') 
WHERE id = 'myId'

-- second update - remove the empty <AgencyID /> node    
UPDATE dbo.UploadReport
SET XmlTest.modify('delete (/CodeFiveReport/Owner/AgencyID)[1]') 
WHERE id = 'myId'

One thing: you need to use the XmlTest.modify operation - not XmlTest.insert as you have in your post. There is no .insert() function on SQL Server XML columns.