SQL Server – Simplest Way to Edit Single Entry in XML Column

sql serversql-server-2012xml

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?

Best Answer

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.

Related Question