8

As I understand MS SQL Server Management Studio doesn't allow directly editing/replacing entries in XML columns via GUI (copy/paste doesn't work etc.).

What is the easiest option to replace a single entry in an XML column? Do I have to use something different from UPDATE/REPLACE for an XML column value?

21

If you just need to replace the whole piece of XML in once, then you can do a normal UPDATE, eg something like this:

UPDATE yourTable
SET yourXML = '<yourNewValidXML/>' 
WHERE rowId = 1

If you need to edit individual attributes or elements then you can use the .modify method of the XML data-type in SQL Server to update single values. Here's a simple example to get you started:

DECLARE @t TABLE ( rowId INT IDENTITY PRIMARY KEY, yourXML XML )

INSERT INTO @t ( yourXML )
VALUES ( '<Users>
    <User Name="Bob"></User>
    <User Name="Mikhail"></User>
    <User Name="John"></User>
    <User Name="Sue"></User>
</Users>' )

SELECT 'before' s, DATALENGTH(yourXML) dl, yourXML
FROM @t
WHERE rowId = 1

-- Update one attribute
UPDATE @t
SET yourXML.modify('replace value of (Users/User/@Name[.="Bob"])[1] with "wBob"')
WHERE rowId = 1

SELECT 'after' s, DATALENGTH(yourXML) dl, yourXML
FROM @t
WHERE rowId = 1

If you need more help, please post a small sample of your XML and your expected results.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.