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

Best-Practice:

1. ONLY update XML/Records that need updating.
   (i.e. Avoid Updating all Records whether the data is different or not).
2. ONLY update XML/Records once.
   (e.g. Do not Delete every Node, only to then go back and Insert every Node).
3. Increase Performance my making a minimal impact.
   (i.e. Avoid Inserting or Deleting Nodes, when a simple Replace or Insert-Text will do.
4. Do not risk losing other important data.
   (i.e. NEVER Delete a Node, and risk losing possible Child-Elements, when all you are doing is updating the Text-Value).
5. Create something reusable that will work for every scenario.
   (i.e. The question asks us how to do this when you already know the Unique-ID.
   However, design your answer to handle multiple records in the most performant way possible).

XML Column

Here is how I would have scripted it to update an XML Field in a Table:

DECLARE @newValue nVarChar(128) = '01'

--Insert a Value when the Element is Empty (i.e. <AgencyID />), so it becomes <AgencyID>01<\AgencyID>.
UPDATE dbo.UploadReport
   SET XmlTest.modify('insert text{sql:variable("@newValue")} as first into (/CodeFiveReport/Owner/AgencyID)[1]') 
 WHERE XmlTest.value('(/CodeFiveReport/Owner/AgencyID)[1]', 'nVarChar(128)') = ''--Node is: <AgencyID />
   AND id = 'myId'

--Replace the Value if Text already Exists AND is Different (e.g. <AgencyID>99<\AgencyID>).
--  Note: This will not work for Empty-Elements (i.e. <AgencyID />), which is why we perform the Update Above.
UPDATE dbo.UploadReport
   SET XmlTest.modify('replace value of (/CodeFiveReport/Owner/AgencyID)[1] with sql:variable("@newValue")') 
 WHERE XmlTest.value('(/CodeFiveReport/Owner/AgencyID)[1]', 'nVarChar(128)') != @newValue--Node is like: <AgencyID>99<\AgencyID>
   AND id = 'myId'

--Optional.  Use the Update below if it is possible for an Element to not exist at all.
UPDATE dbo.UploadReport
   SET XmlTest.modify('insert <AgencyID>{sql:variable("@newValue")}</AgencyID> as first into (/CodeFiveReport/Owner)[1]') 
 WHERE XmlTest.exist('/CodeFiveReport/Owner/AgencyID') = 0--The AgencyID Element/Node is missing entirely.
   AND id = 'myId'
   --AND XmlTest.value('(/CodeFiveReport/Owner/AgencyID)[1]', 'nVarChar(128)') IS NULL--Same thing as Exist(), only without the overhead of Casting.

XML Variable

If you only want to update an XML variable (and not an XML field in a table), then I'd use this approach.
I prefer this because you are not Deleting an existing Node or unnecessarily Adding one (which I'd imagine is slower). You are only Updating it when absolutely necessary.
FYI: An Element could have a Text value AND other Child-Elements - this is allowed by the XML spec.

DECLARE @Xml Xml = N'<Root><Note /></Root>'--Works for: "<Root></Root>", "<Root><Note /></Root>", and "<Root><Note>Something</Note></Root>".
DECLARE @Note NVarChar(128) = 'Hello'
IF(@Xml.value('(/Root/Note)[1]', 'nVarChar(128)')  = '')    SET @Xml.modify('insert text{sql:variable("@Note")} as first into (/Root/Note)[1]')    --Node is: <Note />
IF(@Xml.value('(/Root/Note)[1]', 'nVarChar(128)') != @Note) SET @Xml.modify('replace value of (/Root/Note/text())[1] with sql:variable("@Note")')  --Node is like: <Note>Something<\Note>
IF(@Xml.exist('/Root/Note') = 0)                            SET @Xml.modify('insert <Note>{sql:variable("@Note")}</Note> as first into (/Root)[1]')--Node is missing: <Root></Root>
SELECT @Xml[@Xml]