Refresh empty XML tag in SQL Server

Question

I am trying to update an empty XML tag in my Xml string on sql server; he says the line is updated when I run the following script, but when I look at the XML; nothing 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' 

xml after that is still displayed in this file

<AgencyID />

What am I doing wrong?

I tried @AgencyID without text () at the end and still didn't help ...

Answer 1

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

You may need 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 operation XmlTest.modify - not XmlTest.insert , as you have in your post. There is no .insert() function in the SQL Server XML columns.

Answer 2

Using Sql Server 2005

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

- insert a space into an 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 the 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 3

This should be such as to work directly to update 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:

• Only update XML / records that require updating.
  (i.e., avoid updating all records regardless of whether the data is different or not).
• ONLY updates XML / reports once.
  (for example, do not delete each Node, and then go back and insert each Node).
• Increase productivity with minimal impact.
  (i.e., avoid inserting or deleting nodes when a simple "Replace" or "Insert-text" is executed.
• Do not risk losing other important data.
  (i.e., NEVER delete Node and risk losing potential children when everything you do updates the text value).
• Create something reusable that will work for each scenario.
  (i.e. the question asks us how to do this when you already know the unique identifier.
  However, create your answer to handle multiple records in the most efficient way).

XML Column

This is how I would script it to update the XML field in the table:

 DECLARE @newValue nVarChar(128) = '01' --Insert a Value when the Element is Empty (ie <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 (eg <AgencyID>99<\AgencyID>). -- Note: This will not work for Empty-Elements (ie <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 the XML variable (and not the XML field in the table), I would use this approach.
I prefer this because you are not deleting the existing Node or adding it unnecessarily (which I think is slower). You only update it when it is absolutely necessary.
FYI: An element can have the value Text and other child elements - this is permitted by the XML specification.

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

must work

Answer 6

Some of the later answers seem to provide a simpler solution to this problem.

However, I was looking for a solution that would work also in cases where the node / tag made already relevant in it. So far, the best way I have found for this in both cases is to simply remove the node and then insert a new one, for example:

  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 has been printed (using XML SCHEMA COLLECTION), empty nodes will be saved with their tags open and close; such nodes will be updated accordingly - and your source code will work.

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