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

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'

Answer 2

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.

Answer 3

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' 

Answer 4

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]

Answer 5

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

should work

Answer 6

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]
    ');

Answer 7

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