Update Empty XML Tag in SQL Server

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 =开发者_JAVA百科 '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...

Using Sql Server 2005

declare @var varchar(100)
set @var = '1234'

-- insert whitespace into empty element

update dbo.UploadReport 
set Form_XML.modify('insert text{" "} into (/CodeFiveReport/Owner/AgencyID/[not(text())])[1]')
WHERE id = 'myId'

-- now that we have a text() node, you can use replace w/ your variable

update dbo.UploadReport 
set FORM_XML.modify('replace value of (/CodeFiveReport/Owner/AgencyID/text())[1] with sql:variable("@var")')
WHERE id = 'myId'

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.

It should be like this to work straight for updating values to empty elements :-

update dbo.UploadReport  
set Form_XML.modify('insert text{sql:variable("@var")} into (/CodeFiveReport/Owner/AgencyID)[1]') 
WHERE id = 'myId' 

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]

Some of the more recent answers do seem to provide a simpler solution to this problem.

However, I've been looking for a solution that would work also work in cases where the node/tag did already have a value in it. So far, the best way I've found to do this for both cases is to just delete the node and then insert a new one, like this:

    set @xml.modify('
        delete (//Content/MyNode/ETA)
    ');

    set @xml.modify('
        insert <ETA>{sql:variable("@eta")}</ETA>
        into (//Content/MyNode)[1]
    ');

SET Form_XML.modify('insert text{sql:variable("@var") into  (/CodeFiveReport/Owner/AgencyID)[1]')1]')

should work

If your "XmlTest" column were typed (using an XML SCHEMA COLLECTION), empty nodes will be stored with their open and close tags; such nodes will be appropriately updated - and your original code would work.

ref: http://msdn.microsoft.com/en-us/library/ms176009.aspx