Can't update an xml field in a column of a database

Question

I have an existing xml field. I want to update the field using Microsoft Sql Server Management Studio. I know of the modify function with replace but it is not working as I wish. I'm using the following link for reference and I've mimicked it but it just creates it's own xml values.

Maybe this even helps, I have a database. In the database I have columns a,b,c. In column c is an xml file. I want to update the the fields of the xml file in column c.

http://technet.microsoft.com/en-us/library/ms190675.aspx

 declare @a xml

 select @a = convert(xml,[example]).value(
 '(/root/x/y)[1]', 'varchar(5)') 
 from --
 left join -- on -- = --
 where 1=1 and -- = -- and -- = --

 --update rev.REV_SECURITY_DEFAULT
 set @a.modify('
 replace value of (/root/x/@y)[1]
 with "0"')
 select @a

 --new
 SET @a = '<Root>
 <x LocationID="10">
  <y>Manufacturing step 1 at this work center</y>
  </x>
 </Root>'
 SELECT @myDoc
 --update rev.REV_SECURITY_DEFAULT
 set @a.modify('
 replace value of (/root/x/@y)[1]
 with "0"')
 select @a

--UPDATE

select NAME, NAMESPACE, ---
from --
left join -- on -- = --
where 1=1 and --.NAMESPACE = '--' and--.name = '--' 
go

DECLARE @a xml = N'<root><x y="2"/></root>';
SELECT @a;

SET @a.modify('replace value of (/root/x/@y)[1] with "42"');
SELECT @a;

new problem when trying to cast the ntext to xml. It doesn't cast it and still gives me cannot call methods on ntext

SELECT CAST(a.column_name AS XML)
from a

update a

set a.modify('replace value of (/x/y/@z)[1] with "0"') 

now I need to insert an element into the xml file. I try the following but get an error of "Syntax error near ')', expected a step expression".

update #tmp set permissions.modify('insert <EXAMPLE></EXAMPLE> into (/SECURITY/)[1]')

Answer 1

After correctly setting the value of @a to be an xml fragment, not a value, if y is an attribute, your syntax would be similar to:

DECLARE @a xml = N'<root><x y="2"/></root>';
SELECT @a;

SET @a.modify('replace value of (/root/x/@y)[1] with "42"');
SELECT @a;

yields <root><x y="42"/></root>

If y is an element your syntax would be:

DECLARE @a xml = N'<root><x><y>hello</y></x></root>';
SELECT @a;

SET @a.modify('replace value of (/root/x/y/text())[1] with "world"');
SELECT @a;

yields <root><x><y>world</y></x></root>

Applying this to a table, here is sample code that will run as is:

DECLARE @T TABLE(NS varchar(32), NM varchar(16), DocXml xml)
INSERT INTO @T 
  VALUES ('MyNamespace1', 'MyName1', N'<root><x y="2"/></root>'),
         ('MyNamespace2', 'MyName2', N'<root><x y="2"/></root>')

UPDATE @T SET DocXml.modify('replace value of (/root/x/@y)[1] with "42"')
 WHERE NS = 'MyNamespace1' AND NM = 'MyName1';

SELECT * FROM @T     

However, if the 'xml' column is a text column with xml data and not typed as xml, you cannot both cast to xml and call modify in one update statement. You ought to consider either altering the table column to the xml type, or adding an xml column.

Here would be one workaround, assuming your original table has a unique Id:

-- represents original table...
CREATE TABLE T(Id int, NS varchar(32), NM varchar(16), DocXml ntext)
INSERT INTO T 
  VALUES (1, 'MyNamespace1', 'MyName1', N'<root><x y="2"/></root>'),
         (2, 'MyNamespace2', 'MyName2', N'<root><x y="2"/></root>');

-- select target records into tempdb
SELECT Id, CAST(DocXml AS xml) AS DocXml INTO #tmp FROM T WHERE NS = 'MyNamespace1' AND NM = 'MyName1';

-- update tempdb
UPDATE #tmp SET DocXml.modify('replace value of (/root/x/@y)[1] with "42"');

-- and copy back to original table
    UPDATE T
       SET DocXml = CAST(U.DocXml AS nvarchar(MAX))
      FROM dbo.T TT
INNER JOIN #tmp U ON TT.Id = U.Id

SELECT * FROM T